不使用分析器来衡量SQL性能

Question

除了使用Profiler，还有更简单的方法来衡量MS-SQL中的SQL性能吗？

我一直在使用我在网上找到的这个脚本来进行测量，但我不知道它可能有多准确。

DECLARE @StartTime datetime;

DBCC DROPCLEANBUFFERS  -- Force data/index pages out of buffer cache for valid test
SET @StartTime = GETDATE() -- Measurement Starts  

-->Insert SQL CALL/Script here

-- Measurement Ends  
SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
GO

有没有类似的替代品可以快速地、大致准确地了解我所做选择的情况?

它返回了一些意外的结果，但我不知道该将惊讶的目光投向测试查询还是测试工具/脚本?

- discosammy

---

这个 interwebs 在哪里？ - Rick S

分析器、执行计划、统计时间和io，这些是要使用的工具。反复丢弃缓存将会给你带来糟糕的指标。 - Allan S. Hansen

是的，这对于基准测试单个查询或过程的基本响应时间和资源消耗效果很好。当调整单个查询时，最好使用它来迭代比较其与先前版本的差异。但是，它并不适用于查看整个服务器/应用程序性能（几乎无用）。 - RBarryYoung

Answer 1

以下是我的测试设置：

DBCC DROPCLEANBUFFERS
DBCC freeproccache

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT * FROM sys.all_columns

这将为您提供漂亮的输出（在消息选项卡上）：

(4307 row(s) affected)
Table 'syscolrdb'. Scan count 1, logical reads 167, physical reads 0, read-ahead reads 167, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 12, physical reads 1, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 738 ms.

- Tomasito

---

这是从客户端窗口执行此操作的好方法。正如其他人指出的那样，了解您的环境始终很重要 - 确保您在尽可能类似于最终系统的系统上进行测试。但是，所有这些都已经理解，SET STATISTICS TIME ON是倾倒时间的好方法。它为每个主要操作和整个批处理时间提供一个时间...如果可能，一个相关的技巧是在每个步骤中在批处理中放置“print”语句，然后这些语句将显示在时间旁边。 - Mike M

另外，SET STATISTICS IO ON 是一种快速而简单的方法，用于确定查询存在问题。例如，如果已知数据量，我们将看到第一个解决方案从表中读取了太多内容。 - Tomasito

Answer 2

在清除缓存之前，您应该设置一个检查点以强制将所有脏页面写入磁盘。 无论如何，在测试系统中进行缓存丢弃可能是一种选择。 SQL查询的性能在很大程度上取决于数据库中的数据以及对该数据库运行的查询的类型，频率，参数和结果集。

在“干净”的单用户系统上运行查询会为“干净”的单用户系统产生结果。 缓冲区管理是DBMS的核心功能，因此我认为任意刷新缓冲区并测量执行时间值没有意义。 此外，在生产系统上执行此操作可能会显着降低整体性能，从而也会影响要测试的查询。

要优化单个查询，请使用执行计划，并识别占最高份额的查询部分，并查看是否可以采取任何措施。 例如，如果您拥有不适合您数据的设计不佳的索引，则执行时间将毫无意义，但执行计划将给您提示。

要识别值得优化的查询，请使用分析器查找a）资源消耗大的查询（例如长时间运行的查询）和b）经常使用的查询。

要监视整个系统，请使用性能计数器，并关注内存和I / O子系统，例如分页计数器。

- Martin K.

---

我正在寻找一个快速而简单的测试来展示查询速度。如果我在教授一堂19名学生的课程，那么19个人运行分析器会让整个系统崩溃吗？你如何快速地证明 where id = (select id from other_table) 是一个不好的想法？ - discosammy

我看到了，我也理解了，我以前也遇到过类似的问题。我试图解释的是，没有简单的度量标准可以使用。你可以测量执行时间，但你用那个值做什么呢？你不能去告诉另一个开发者，“嘿，5毫秒太慢了，应该是2毫秒”。缓冲区刷新可能有用于比较不同版本的查询，但即使如此，在实际环境中也不能保证得到相同的结果。 - Martin K.

仅用于演示目的，我认为这是可以接受的。但正如我所说，我只会在测试环境中进行演示。 - Martin K.

如果我使用检查点，那么缓冲区中的任何数据都会被保存，对吗？还是说我完成后必须恢复它？ - discosammy

不用担心。数据库管理系统将会把未提交的事务所更改的页面写入磁盘（=DB）以释放缓冲区空间。将脏页写入数据库需要数据库管理系统存储UNDO信息，但这完全是内部操作。您无需自己还原任何内容。 - Martin K.