OpenSQLTrace：自动跟踪处理和分析系统

领测软件测试网

　　 
　　如果您定期运行跟踪并且保留所有结果以便进行历史趋势分析，那么通过跟踪捕获的数据的价值将大大增加。但是，存储空间很快会成为约束。我们的主要生产服务器每小时执行 4 百万个事务，而持续时间为 20 分钟的跟踪会创建 0.5GB 大小的跟踪文件。

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

文章来源于领测软件测试网 https://www.ltesting.net/