T-SQL命令在SQL Server查询中的运用
2024-08-31 00:48:14
供稿：网友
首先需要说明的是这篇文章的内容并不是如何调节sql server查询性能的（有关这方面的内容能写一本书），而是如何在sql server查询性能的调节中利用set statistics io和set statistics time这二条被经常忽略的transact-sql命令的。
从表面上看，查询性能的调节是一件十分简单的事。从本质上讲，我们希望查询的运行速度能够尽可能地快，无论是将查询运行的时间从10分钟缩减为1分钟，还是将运行的时间从2秒钟缩短为1秒种，我们最终的目标都是减少运行的时间。
尽管查询性能调节困难的原因有许多，但这篇文章将只涉及其中的一个方面，其中最重要的原因是，每当使用环境发生变化时，就需要对性能进行调节，因此很难搞清楚到底需要如何调节查询的性能。
如果象大多数用户那样在一台测试用的服务器上进行性能调查，其效果往往并不是十分地令人满意，因为测试服务器的环境与实际应用的服务器环境并不完全相同。随着对资源要求的不断变化，sql server会自动地进行自我调节。
如果对这一点有疑问，可以在一台负载很大的服务器上反复地运行同一个查询，在大多数情况下，执行查询所使用的时间并不相同。当然，差距并不大，但其变化足以使性能的调节比它应有的程度要困难一些。
这到底是怎么回事儿？是你的想法错了还是在运行查询时，服务器的负载过重？这是引起运行时间增加的原因吗？尽管可以多次反复地运行查询得到一个平均时间，但这样作的工作量很大。我们需要用一种很科学的标准对每次测试时的性能进行比较。
测量服务器资源是解决查询性能调节问题的关健
在服务器上执行查询时，会用到许多种服务器资源。其中的一种资源是cpu的占用时间，假设数据库没有发生任何改变，反复地运行同一个查询其cpu的占用时间将是十分接近的。在这里，我指的不是一个查询从运行开始到结束的时间，而是指运行这一查询所需要的cpu资源数量，运行一个查询所需要的时间与服务器的忙碌程度有关。
sql server需要的另一种资源是io。无论何时运行查询，sql server都必须从数据缓冲区中读取数据（逻辑读），如果所需要的数据没有在缓冲区中，则需要到磁盘上读取（物理读）。
从讨论中可以知道，一个查询需要的cpu、io资源越多，查询运行的速度就越慢，因此，描述查询性能调节任务的另一种方式是，应该以一种使用更少的cpu、io资源的方式重写查询命令，如果能够以这样一种方式完成查询，查询的性能就会有所提高。
如果调节查询性能的目的是让它使用尽可能少的服务器资源，而不是查询运行的时间最短，那么就更容易测试你采取的措施是提高了查询的性能还是降低了查询的性能。尤其是在资源利用不断变化的服务器上更是如此。首先，需要搞清楚在对查询进行调节时，如何测试我们的服务器的资源使用情况。
又想起了set statistics io和set statistics time
sql server很早以前就支持set statistics io和set statistics time这二条transact-sql命令了，但由于其他一些原因，在调节查询的性能时，许多dba（数据为系统管理员）都忽略了它们，也许是它们不大吸引人吧。但不管是什么原因，我们下面就会发现，它们在调节查询性能方面还是很有用的。
有三种方式可以使用这二条命令：使用transact-sql命令行方式、使用query analyzer、在query analyzer中设置当前连接适当的连接属性。在这篇文章中，我们将使用transact-sql命令行的方式演示它们的用法。
set statistics io和set statistics time的作用象开关那样，可以打开或关闭我们的查询使用资源的各种报告信息。缺省状态下，这些设置是关闭的。我们首先来看一个这些命令如何打开的例子，并看看它们会报告一些什么样的信息。
在开始我们的例子前，启动query analyzer，并连接到一个sql server上。在本例中，我们将使用northwind数据库，并将它作为这个连接的缺省数据库。
然后，运行下面的查询：
select * from [order details]
如果你没有改动过order details这个表，这个查询会返回2155个记录。这是一个典型的结果，相信你已经在query analyzer中看到过好多次了。
现在我们来运行同一个查询，不过这次在运行查询之前，我们将首先运行set statistics io和set statistics time命令。需要记住的是，这二个命令的打开只对当前的连接有效，当打开其中的一个或二个命令后，再关闭当前连接并打开一个新的连接后，就需要再次执行相应的命令。如果想关闭当前连接中的这二个命令，只要将原来命令中的on换成off，再执行一次就可以了。
在开始我们的例子前，先运行下面的这二条命令（不要在正在使用的服务器上执行），这二条命令将清除sql server的数据和过程缓冲区，这样能够使我们在每次执行查询时在同一个起点上，否则，每次执行查询得到的结果就不具有可比性了：
dbcc dropcleanbuffers
dbcc freeproccache
输入并运行下面的transact-sql命令：
set statistics io on
set statistics time on
一旦上面的准备工作完成后，运行下面的查询：
select * from [order details]
如果同时运行上面所有的命令，你得到的输出就会与我的不同，也就很难搞清楚到底发生了什么事情。
在运行上述的命令后，就会在结果窗口中看到以前没有看到过的新资料，在窗口的最顶端，会有下面的信息：
sql server parse and compile time: （sql server解析和编译时间：）
cpu time = 10 ms, elapsed time = 61 ms.
sql server parse and compile time: （sql server解析和编译时间：）
cpu time = 0 ms, elapsed time = 0 ms.
在显示上面的数据后，查询得到的记录就会显示出来。在显示完2155条记录后，会显示出下面的信息：
table 'order details'. scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
（表：order details，扫描次数 1，逻辑读 10，物理读 1，提前读取 9）
sql server execution times:
（sql server执行时间：）
cpu time = 30 ms, elapsed time = 387 ms.
（每次得到的结果可能各不相同，在下面我们讨论显示的信息时会提到这一点。）
那么，这些信息的具体含意是什么呢？下面我们就来详细地进行分析。
set statistics time的结果
set statistics time命令用于测试各种操作的运行时间，其中一些可能对于查询性能的调节没有什么用处。运行这一命令可以在屏幕上得到如下的显示信息：
输出的最开始处：
sql server parse and compile time:
cpu time = 10 ms, elapsed time = 61 ms.
sql server parse and compile time:
cpu time = 0 ms, elapsed time = 0 ms.
输出的结束处：
sql server execution times:
cpu time = 30 ms, elapsed time = 387 ms.
在输出的最开始处我们可以看到二次测试时间，但第一行执行某一操作所需的cpu的时间和总共时间，但第二行似乎就不是了。
“sql server parse and compile time”表示sql server解析“elect * from [order details]”命令并将解析的结果放到sql server的过程缓冲区中供sql server使用所需要的cpu运行时间和总的时间。
在本例中，cpu的运行时间为10毫秒，总时间为61毫秒。由于服务器的配置和负载不同，你得到的cpu运行时间、总时间这二个值可能会与本例中的测试结果有所不同。
第二行的“sql server parse and compile time”表示sql server从过程缓冲区中取出解析结果供执行的时间，大多数情况下这二个值都会是0，因为这个过程执行得相当地快。
如果不清除缓冲区而再次运行select * from [order details]命令，cpu运行时间和编译时间会都是0，因为sql server会重复使用缓冲区中的解析结果，因此就不需要再次编译的时间了。
这些信息在查询性能的调节中对你的帮助真的很大吗？也许并非如此，但我将解释一下这些信息的真正含意，你将会很惊奇，大多数的dba居然都不真正明白这些信息的含意：
我们最感兴趣的是显示在输出最后的时间信息：
sql server execution times:
cpu time = 30 ms, elapsed time = 387 ms.
上面显示的信息表明，执行这次查询使用了多少cpu运行时间和运行查询使用了多少时间。cpu运行时间是对运行查询所需要的cpu资源的一种相对稳定的测量方法，与cpu的忙闲程度没有关系。但是，每次运行查询时这一数字也会有所不同，只是变化的范围没有总时间变化大。总时间是对查询执行所需要的时间（不计算阻塞或读数据的时间），由于服务器上的负载是在不断变化的，因此这一数据的变化范围有时会相当地大。
由于cpu占用时间是相对稳定的，因此可以使用这一数据作为衡量你的调节措施是提高了查询性能还是降低了查询的性能的一种方法。
set statistics io的效果
set statistics io的输出信息显示在输出的结束处，下面是它显示的一个例子：
table 'order details'. scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
这些信息中的一部分是十分有用的，另一部分则不然，我们来看看每个部分并了解其含意：
scan count：在查询中涉及到的表被访问的次数。在我们的例子中，其中的表只被访问了1次，由于查询中不包括连接命令，这一信息并不是十分有用，但如果查询中包含有一个或多个连接，则这一信息是十分有用的。
一个循环外部的表的scan count值为1，但对于一个循环内的表而言，其值为循环的次数。可以想象得到，对于一个循环内的表而言，其scan count值越小，它所使用的资源越少，查询的性能也就越高。因此在调节一个带连接的查询的性能时，需要关注scan count的值，在进行调节时，注意观察它是增加还是减少了。
logical reads: 这是set statistics io或set statistics time命令提供的最有用的数据。我们知道，sql server在可以对任何数据进行操作前，必须首先把数据读取到其数据缓冲区中。此外，我们也知道sql server何时会从数据缓冲区中读取数据，并把数据读取到大小为8k字节的页中。
那么logical reads的意义是什么呢？logical reads是指sql server为得到查询中的结果而必须从数据缓冲区读取的页数。在执行查询时，sql server不会读取比实际需求多或少的数据，因此，当在相同的数据集上执行同一个查询，得到的logical reads的数字总是相同的。
为什么说在调节查询性能中知道sql server执行查询时的logical reads值是很重要的呢？因为在每次执行同一查询时，这个数值是不会变化的。因此，在进行查询性能的调节时，这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。
在对查询的性能进行调节时，如果logical reads值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果logical reads值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。
physical reads：在这里我要说的的东西可能初听起来有点自相矛盾，但只要反复思考，就会明白其中的真正含意。
物理读指的是，在执行真正的查询操作前，sql server必须从磁盘上向数据缓冲区中读取它所需要的数据。在sql server开始执行查询前，它要作的第一件事就是检查它所需要的数据是否在数据缓冲区中，如果在，就从中读取，如果不在，sql server必须首先将它需要的数据从磁盘上读到数据缓冲区中。
我们可以想象得到，sql server在执行物理读时比执行逻辑读需要更多的服务器资源。因此，在理想情况下，我们应当尽量避免物理读操作。
下面的这一部分听起来让人容易感到糊涂了。在对查询的性能进行调节时，可以忽略物理读而只专注于逻辑读。你一定会纳闷儿，刚才不是还说物理读比逻辑读需要更多的服务器资源吗？
情况确实是这样，sql server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是dba的一项重要工作，但它涉及到整个服务器性能的调节，而不仅仅是查询性能的调节。在进行查询性能调节时，我们不能控制数据缓冲区的大小或服务器的忙碌程度以及完成查询所需要的数据是在数据缓冲区中还是在磁盘上，唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。
因此，在查询性能的调节中，我们可以心安理得地不理会set statistics io命令提供的physical read的值。（减少物理读次数、加快sql server运行速度的一种方式是确保服务器的物理内存足够多。）
read-ahead reads：与physical reads一样，这个值在查询性能调节中也没有什么用户。read-ahead reads表示sql server在执行预读机制时读取的物理页。为了优化其性能，sql server在认为它需要数据之前预先读取一部分数据，根据sql server对数据需求预测的准确程度，预读的数据页可能有用，也可能没用。
在本例中，read-ahead reads的值为9，physical read的值为1，而logical reads的值为10，它们之间存在着简单的相加关系。那么我在服务器上执行查询时的过程是怎么样的呢？首先，sql server会开始检查完成查询所需要的数据是否在数据缓冲区中，它会很快地发现这些数据不在数据缓冲区中，并启动预读机制将它所需要的10个数据页中的前9个读取到数据缓冲区。当sql server检查是否所需要的全部数据都已经在数据缓冲区时，会发现已经有9个数据页在数据缓冲区中，还有一个不在，它就会立即再次读取磁盘，将所需要的页读到数据缓冲区。一旦所有的数据都在数据缓冲区后，sql server就可以处理查询了。
我们应该怎么办？
我在本篇文章的开始曾提到，在对查询的性能进行调节时用一些科学的标准来测量你的调节措施是否有效是十分重要的。问题是，sql servers的负载是动态变化的，使用查询总的运行时间来衡量你正在调节性能的查询的性能是提高了还是没有，并不是一个合理的方法。
更好的方法是比较多个数据，例如逻辑读的次数或者查询所使用的cpu时间。因此在对查询的性能进行调节时，需要首先使用set statistics io和set statistics time命令向你提供一些必要的数据，以便确定你对查询性能进行调节的措施是否真正地得到了目的。
