T-SQL命令在SQL Server查询中的运用

【 tulaoshi.com - 编程语言 】

　　首先需要说明的是这篇文章的内容并不是如何调节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的忙闲程度没有关系。但是，每次运行查询时这一数字也会有所不同，只是变化的范围没有总时间变化大。总时间是对查询执行所需要的时间（不计算阻塞或读数据的时间），由于服务器上的负载是在不断变化的，因此这一数据的变化范围有时会相当地大。