如果您定期运行跟蹤并且保留所有结果以便进行历史趋势分析那么通过跟蹤捕获的数据的价值将大大增加但是存储空间很快会成为约束我们的主要生产服务器每小时执行 百万个事务而持续时间为 分钟的跟蹤会创建 GB 大小的跟蹤文件我们的系统致力于整理所有数据并且只保存其精华您可以安排任何服务器的特定跟蹤（或者设置循环跟蹤）并且自动加载和处理跟蹤文件正如您上个月所看到的那样我们的系统从 TSQL 中剔除了不重要的详细信息从而将事务类型减少到可管理的数量并且生成和保存了开销最大的事务的报表在经过几个周的积累之后这样的报表可以提供对整个服务器或任何特定事务类型进行性能趋势分析的数据
安装
您可以将我们的系统安装在任何已经将服务器连接链接到您希望运行跟蹤的所有 SQL Server 的网络 SQL Server 上因此为了保存跟蹤文件必须可以从被跟蹤的服务器通过网络对该中心服务器的硬盘驱动器进行访问中心跟蹤服务器充当所有跟蹤的计划程序数据处理器循环报表的发布者历史数据的储存库以及 DBA 可以生成即席报表和进行性能调查的分析服务器该设计将对被跟蹤的服务器的影响降低至最低程度并且最大限度降低了由于造成磁盘空间不足或引起处理开销而破坏这些服务器的工作的可能性您还可以直接在每个被跟蹤的服务器上安装和使用该系统 — 只要该服务器具有足够的磁盘空间和处理能力
出于本文的目的让我们将我们的中心跟蹤服务器称为 TRACESQL并且将我们的被跟蹤的服务器称为 PRODSQL如果您计划使用同一服务器来跟蹤其本身则请用同一名称来替换 TRACESQL 和 PRODSQL下面介绍如何安装 OpenSQLTrace
如果您打算只跟蹤已经安装 OpenSQLTrace 的同一服务器（换句话说TRACESQL 和 PRODSQL 是同一服务器）则请跳过步骤 和
配置从 TRACESQL 到 PRODSQL 的链接服务器连接它必须允许具有启动用来管理服务器端跟蹤的系统存储过程的权限最容易的方法是使用在 PRODSQL 上具有 System Administrator 角色的帐户但是您显然需要考虑您的特定环境中的安全要求
查明哪个帐户被用来在 PRODSQL 上运行 MSSQLServer 服务它必须是网络帐户
在 TRACESQL 上选择一个硬盘驱动器分区以用来存储跟蹤文件它必须具有足够的空间以容纳来自 PRODSQL 的跟蹤文件 — 大小很可能为几个 GB但是正如别人所说的那样每个人需要的空间可能有所不同跟蹤文件大小取决于服务器活动事务混合和跟蹤的持续时间
如果 TRACESQL 和 PRODSQL 是不同的服务器则请在 TRACESQL 上在您所选的驱动器上创建一个名为 TRACE 的共享文件夹并且将该共享上的所有权限授予在 PRODSQL 上运行 MSSQLServer 服务的网络帐户
在 TRACESQL 上创建一个名为 Trace 的数据库分配足够空间以存储多个完整的跟蹤文件另外还要分配一点儿额外的空间以存放所保存的报表将存储在数据库中的跟蹤文件的过期时间是可配置的在我们的环境中我们只将它们保留一个周但是我们无限期地保留已编译的摘要报表以便进行历史趋势分析
下载本文随附的 OpenSQLTracesql 脚本并且从 TRACESQL 服务器上的查询分析器中执行它这会在 Trace 数据库中创建所有需要的对象并且创建一个每天安排一次的作业以清除过期的跟蹤表（如果您先前已经在同一数据库中安装了该系统则请注意该脚本删除并重新创建了所有对象包括已保存的数据但未过期的跟蹤表除外）
如果 TRACESQL 和 PRODSQL 是同一服务器则改变在上一步中创建的用户定义函数 ufn_Trace_File_Name更改以下行
return( \\ + rtrim( @@servername ) + \TRACE\ +
以使用您在步骤 中创建的 TRACE 文件夹的硬编码路径确切的路径取决于您的环境例如如果您在驱动器 D: 上创建了 TRACE 文件夹则请按如下方式更改代码
return( D:\TRACE\ +
用法示例
上个月的文章提供了有关提炼跟蹤文件和生成摘要报表的存储过程的用法示例请注意可下载的新脚本具有 Calculate_Most_Expensive_Transactions 过程的重命名版本新的名称为 Calculate_Hit_Parade
本月的脚本公开了由以下示例说明的新功能
设置带有摘要处理的一次性无人参与跟蹤 为了测试该系统让我们设置一次性跟蹤从 TRACESQL 上的查询分析器中执行以下过程
Schedule_Trace PRODSQL default
这会在 TRACESQL 上安排一个在两分钟内运行的作业在 PRODSQL 上启动一个运行一分钟的跟蹤并且将文件保存到 TRACESQL 上的 TRACE 共享中它还将在 TRACESQL 上安排另一个作业以便在跟蹤的估计结束时间之后运行 分钟将文件加载到 Trace 数据库中的表中提炼已记录的 TSQL 语句（有关详细信息请参阅上个月的文章）生成开销最大的事务的摘要并且将其保存到 Trace 数据库中的表中（提示您可以使用 fn_trace_getinfo() 来监视跟蹤进度）
这两个作业在成功完成后都将自动删除它们自身如果您迫不及待地希望更快地运行该测试则可以手动启动安排的第一个作业等待一分钟（跟蹤持续时间）然后手动启动第二个作业
在第二个作业完成后您便能够在 Trace 数据库的 Hit_Parade_Archive 表中找到已保存的开销最大的事务的报表并且使用存储过程 Retrieve_Report 来检索它
默认情况下系统会记录 TSQL 批处理和远程过程调用的完成如果您希望记录其他跟蹤事件或者更进一步并分别记录在存储过程内部执行的每个查询则需要通过 @Event_Class_Filter 参数向 Schedule_Trace 提供事件列表
安排每日跟蹤 如果您需要每天运行跟蹤则可以如前所述安排一个跟蹤（只须指定预期跟蹤启动时间而不是默认时间并且指定预期持续时间而不是一分钟）然后手动更改所安排的两个作业（运行跟蹤和处理跟蹤）的属性以设置每天执行而不是一次性执行的计划同时在 EM 的已安排作业对话框中取消选中Notifications选项卡上的Automatically delete job选项以防止作业在完成后删除它们自身（通过 Schedule_Trace 设置的默认行为）
检索和分析摘要报表 要检索任何跟蹤的摘要报表需要知道用来加载数据的跟蹤表名称跟蹤表在过期（该参数可配置）时被自动从 TRACE 数据库中删除但是从它们中提取的报表总是 与原来的表名称相关联（Trace_Directory 表包含所有已处理的跟蹤表的目录）可以按照服务器名称和跟蹤时间查找跟蹤表名称
执行以下存储过程以检索一个摘要报表
Retrieve_Report <Trace_Table_Name>
您可以在上个月的文章中查看示例摘要报表我们通常将这些报表复制并粘贴到 Excel 中（在本月的下载中包含其中一个报表）在那里可以容易地对数据进行排序和分析
在我们的环境中我们还创建了一个 DTS 软件包以便将开销最大的事务的日常报表以电子表格格式自动发布到网络共享开发人员可以访问该报表以查看他们的存储过程是如何执行的并且识别瓶颈[我为作者为开发人员反馈所做的准备以及负责任的态度而喝采 — 编者]
按聚合类型获得事务的实际源代码 在您识别开销最大的事务类型之后您就可能希望查看在一个类型下聚合的所有事务的未经提炼的实际 TSQL 代码为了完成该下钻工作请执行以下存储过程
Report_TSQL_by_ID <Trace_Table_Name> <SQL_Type_ID>
其中 <SQL_Type_ID>是从指定为<Trace_Table_Name> 的跟蹤表派生的摘要报表中的事务类型的数字 ID
比较两个报表 最有效的分析方法之一是并排比较两个不同的摘要报表您可能希望比较同一服务器的两个不同跟蹤的性能或者比较具有相同事务混合的两个不同服务器的性能存储过程 Compare_Reports 采用两个跟蹤表（来自 Trace_Directory 表）的名称作为参数并且比较它们的已保存的报表对于每个事务类型它都会显示来自第一个跟蹤和第二个跟蹤的统计信息以及绝对和相对差异
只有当您在两个报表中跟蹤相同的事件类型时对这两个报表进行比较才会有意义在我们的环境中我们在同一时间同一服务器上使跟蹤运行相同的分钟数从而使逐日比较显得合理但是我们可以想到很多分析任务会要求比较两个不同服务器中的跟蹤或者比较在每天的不同时间执行的跟蹤
我们将跟蹤比较报表复制并保存到 Excel 中以便进行进一步的分析它们可以帮助我们回答如下问题
事务混合中发生了哪些可能导致性能下降的更改？
哪些事务的处理开销变得更大？
同一服务器上的特定存储过程的执行频率或平均持续时间在两个日期之间是如何更改的？
摘要报表中出现了哪些新的事务类型？
在两个跟蹤之间如何比较特定事务类型的 I/O 和 CPU 开销？
从所有已保存的报表中检索特定事务类型的历史记录 有时您可能希望查看特定事务的性能是如何随着时间的推移而变化的（例如当您调查瓶颈事务需要分析并且可能需要以图形方式表示响应速度随着时间的推移而发生的下降或提高时）我们还使用它来验证应用于存储过程的修改是否的确已经改善了它们的性能
我们每天为我们的主要生产服务器运行跟蹤并且保存所有报表经过几个月的收集该信息使我们可以为我们希望调查的任何
