使用SQL语法提示工具SQL Prompt，测试批处理中单个SQL语句的性能

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

点击下载SQL Prompt正式版

本文全部涉及使用SQL Prompt代码片段在一批SQL代码上创建和运行测试工具，以提供性能数据。在幕后，此代码段创建并启动了扩展Events事件会话，插入并运行您在SSMS中选择的SQL批处理的文本，并对XML事件数据使用XQuery，以可消化的表格形式返回它。只需单击几下即可调用和执行代码段，就可以获取任何批处理的执行计划，以及批处理中每个SQL语句的SQL文本和查询执行统计信息。

为什么要这样设置呢？在处理XML和扩展事件时，有时会觉得自己就像一条金鱼盯着电视机看。它很催眠，我知道它很可爱，但是我不会假装理解它。作为开发人员，我更倾向于从应用程序的角度简单地将性能视为耗时。这是与用户体验相同的可靠指标。但是，当经过的时间超出用户的承受能力或不稳定时，并且我在逻辑上找不到明显的错误时，我希望查看基于服务器的I / O指标并检查执行情况计划。

为了获得这些，我需要使用Extended Events，并且如果我可以通过代码片段或模板来控制复杂性，那是一个更容易做出的决定。

使用代码片段的示例

假设您有一个聚合查询的执行效果不理想。您已经在Grant Fritchey的《执行计划》一书中读到过他对散列|订单查询提示的描述，这迫使SQL Server为查询中由GROUP BY或DISTINCT引起的所有聚合选择特定的聚合机制。该HASH GROUP提示迫使它使用哈希匹配聚合（基于哈希），和ORDER GROUP迫使使用流聚合（基于订单）。

您想知道这些提示是否值得追求，或者创建索引是否更有意义。唯一确定的方法是测试所有的选项。清单1显示了我使用的代码。

清单1

哪种策略效果最好？在SSMS查询窗格中，我按Ctrl-A选择整个批处理。选择代码后，我发现SQL Prompt的红色小操作列表图标出现在所选内容的左下方。它的下拉菜单列出了所有已安装的代码段，然后单击我的chk代码段。

chk片段（稍后将向您介绍如何创建）将所选代码嵌入特殊的扩展事件的事件会话中，该会话将跟踪以特定字符串开头的批处理中的各个语句。 该标识符由代码段的注释添加。 在本文稍后的部分，我将向您展示如何检查多个批次。

相当多的代码被添加到查询窗格中，但是我有稳定的神经。我只需要点击“Execute”，一秒钟后我就会看到结果，其中包括所有已执行语句的文本，它们的执行时间以及到执行计划的链接。

滚动查看每个语句的更多信息，包括CPU时间、IO统计信息和返回的行数。

从这些执行统计数据中我们可以直接看到，提供合适的索引比弄乱提示要好得多。 通过提供按我们要分组的列在逻辑上排序的索引，我们可以使查询的运行速度快四倍。

如果我们想知道为什么，可以看一下展示计划。就像我们在第一个示例中一样，仅检查一个批处理，所有showplan链接都指向同一计划，这是该批处理的计划，并且包含该批处理中每个语句的各个计划：

在没有有用索引的情况下，优化器可以使用哈希匹配聚合（这会在内存中构建和填充哈希表），也可以首先对从聚合索引中出现的数据进行排序，然后使用流聚合。

在这种情况下，它认为前一种策略是最便宜的策略，但是ORDER GROUP提示迫使优化器采用后者。我们的查询执行统计信息表明，对于像这样的小表来说，它几乎没有什么区别，但是排序操作非常昂贵，因此，随着源表中行数的增加，此提示查询的性能可能会下降。相反，通过提供按GROUP BY列排序的索引，我们使优化器可以选择开销较低的流聚合，而无需其他排序操作。

我提这一切原因并不是要阻止您使用提示，而是要说服您在决定采取行动之前尽可能准确地测量此类事情。有了这个代码段，我们可以将其用于任何一组查询，我们可以很快查看是否有任何一种策略可以提供“巨大的成功”。

批处理中检查SQL语句的代码段

清单2显示了用于创建Prompt代码段的代码。

清单2

通过$ SELECTEDTEXT $占位符，您可以将代码从所选代码段的任何一侧放入。 如您所见，我们创建会话，执行代码并立即停止会话。 我们获取表示环形缓冲区内容的XML文档，并将其切碎成出现在结果中的关系表。 事件会话会过滤掉除以字符串“ --Test these query”开头的批处理之外的所有内容。

只需将清单2中的代码粘贴到SSMS查询窗格中，使用Ctrl-A突出显示代码，然后右键单击并选择Create Snippet。确定要输入以调用代码段的字符（我选择了chk），为代码段提供描述，然后单击保存。

检查一批以上

此代码段仅适用于一个批处理。它在批处理的开始处插入以下行；

--Test these queries

但是，事件会话设置为报告以该字符串开头的任意数量的批处理。可以为一系列单独批处理中的每个语句提供单独的执行计划。为了证明这一点，我们将重复测试，但将每个语句放入自己的批处理中。

清单3

请注意，在每个批处理的开始处都带有--Test these queries字符串。 还要注意，我们已经用临时表替换了表变量，因为它在批处理之间可见。 现在，使用“提示”将其放入测试工具，您将得到只包含您感兴趣的查询的更简洁的结果，并且每个查询都有其自己的执行计划。

用于检查存储过程或函数中的语句的代码段

扩展基本代码片段以显示例如在存储过程和函数中执行的语句非常容易。 只需执行清单2的CREATE EVENT部分，然后在SSMS对象资源管理器中导航事件会话，并从其右键单击上下文菜单中选择Properties。

我们可以添加所需的任何事件、字段和过滤器。在这里，我将sp_statement_completed事件添加到事件会话中。

进行更改后，您可以编写DDL脚本以创建事件会话，并在“提示”代码段中使用它。切记添加初步代码以删除该名称的任何现有会话。

通过更改事件会话来熟悉扩展事件是值得的。通过属性窗口，查看可以从中获取哪些详细信息。但是，请注意，您必须能够凝视原始XML而不会退缩，直到能够将XML分解为关系形式为止。幸运的是，TSQL事件之间有相似之处，因此记录所有此类事件的摘要相当容易。

清单4显示了一段代码，该代码将返回存储过程或批处理中所有语句的执行统计信息和计划。它适用于功能和程序。作为辅助调查非常好，但是详细程度很快就会变得势不可挡。

清单4

例如，我在这里使用它来检查我的旧存储过程，该过程列出了当前月份或您指定的任何月份的日历。

结论

如果要使用扩展事件来开发代码，那么值得暂时退一步来创建代码片段或模板，以便在开发时可以重用这些测试工具。

他们节省了大量时间来尝试弄清楚究竟是什么导致代码缓慢运行。解决性能问题时，您不一定总是需要非常详细的信息，并且在关注SQL语句级别之前，我通常会花整段时间。但是，当您想了解细节时，没有什么可以与基于扩展事件的测试工具进行比较了。

看完了文章，不知道内容是否对您有所帮助？如果您对该产品感兴趣，可以继续关注我们，了解更多产品资讯，或者下载产品，自己动手体验一番~