SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查询性能优化中的作用

原文: SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查询性能优化中的作用

近段时间以来，一直在探究SQL Server查询性能的问题，当然也漫无目的的查找了很多资料，也从网上的大神们的文章中学到了很多，在这里，向各位大神致敬。正是受大神们无私奉献精神的影响，所以小弟也作为回报，分享一下关于SET STATISTICS IO和SET STATISTICS TIME这两条T_SQL命令，在查询优化性能中的作用。

      首先我想说明一下这篇文章不是关于如何优化SQL Server查询性能的，因为关于这方面的内容太多，太复杂。另外查看很多关于性能优化（该文章中，指的是查询性能）的资料的过程中，发现几乎所有都是用执行时间作为优化指标，但是用执行时间作为性能优劣的指标并不是那么合适。当然，我们优化查询语句的最终目的就是减少查询时间。

      引起查询时间不准确的原因，有以下两个方面：

       1.SQL Server会随着服务器资源的变化，而进行自我调节。

       因为我们通常测试的服务器和实际服务器的环境并不完全相同。例如，我们在一台负载很重的服务器上进行反复的测试。你会发现每次的执行的时间，并不相同，当然差距并不大，但是这个差距足以让我们的性能调节变得困难许多。当然你也可以反复执行求平均值，但是在负载很大的服务器上，你需要一种多么科学的标准来确定执行时间的平均值呢？

      2.SQL Server所要读取的数据，有没有在缓冲区中。

      因为SQL Server 每次读取数据都必须从数据缓冲区中读取，这个也叫逻辑读。如果要读的数据没有在数据缓冲区中，就要从物理磁盘上读取（物理读）。

      以上两个原因都会影响执行查询语句所用的时间。

      说了那么多，那我们该用什么作为性能优化的标准呢？

      1.CPU的占用时间。

       当数据库执行查询语句时，会用到很多服务器的资源。其中一种资源就是CPU的占用时间，如果数据库没有发生任何的改变，反复的运行同一个查询，CPU的占用时间都是十分接近的。

       2.IO操作的次数。

        IO操作的指标有很多，下面会比较详细的描述。

       通过上面的描述，我们知道，一个查询所需要的CPU、IO资源越少，性能就会越好。如果我们按照这个标准来优化查询，那么就会很容易的判断出你的优化措施是降低了性能，还是提高了性能。想到了这，那么我们怎样才能看到我的服务器资源使用情况呢？这个时候我们就想到了SET STATISTICS IO和SET STATISTICS TIME (之前的内容就算是我卖关子了啊，嘿嘿)

        SET STATISTIC IO和SET STATISTIC TIME像很多T_SQL语句那样属于开关命令（自己起得名字，就是用ON和OFF打开和关闭）。缺省状态下是关闭的。接下来我们就开始使用这两个命令了，好期待。

        在这个例子中，我们使用之前建好的Test数据库,使用Person表

        （一）首先我们使用SET STATISTICS TIME

         1.首先，为了使每次的执行都在同一个起点上，我们使用下面的两条命令，来清除SQL Server的数据和过程缓冲区，否则执行的查询结果就没有可比性了。   

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

          2.执行SET STATISTIC TIME ON 打开CUP统计报表

         这些准备工作完成后，我们可以执行下面查询：

          select  * from Person where ID=50000

          执行完上述命令之后，你可以在消息选项卡中得到下面信息

SQL Server 分析和编译时间:    CPU 时间 = 0 毫秒，占用时间 = 20 毫秒。

SQL Server 分析和编译时间:    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

 SQL Server 执行时间:    CPU 时间 = 235 毫秒，占用时间 = 1508 毫秒。

下面我们来详细的分析这些时间信息的含义。

 第一个“SQL Server 分析和编译时间”指的是解析“select  * from Person where ID=50000”这条查询语句，并将解析的结果放到过程缓冲区中，SQL Server使用的CPU运行时间和总的时间。

第二个“SQL Server 分析和编译时间”指的是从过程缓冲区中取出解析结果，并且执行的时间。这个时间会很快。

如果接下来不清空缓冲区而直接运行“select  * from Person where ID=50000”，你会发现SQL Server 分析和编译时间都为0，因为SQL Server这时，会直接使用缓冲区中的解析结果，因此就不需要编译时间。

第三个“SQL Server执行时间”将会是我们最感兴趣的时间，这个时间是执行这次查询使用了多少CPU运行时间和运行查询使用了多少时间。CPU运行时间是对运行查询所需要的CPU资源的一种相对稳定的测量方法，与CPU的忙闲程度没有关系。但是，每次运行查询时这一数字也会有所不同，只是变化的范围很小。总时间是对查询执行所需要的时间（不计算阻塞或读数据的时间），由于服务器上的负载是在不断变化的，因此这一数据的变化范围有时会相当地大。

 由于CPU占用时间是相对稳定的，所以你可以使用这一数据作为衡量你的优化措施是提高了查询性能，还是降低了查询性能。

（二）接下来我们使用SET STATISTICS IO

和上面的准备工作一样，当我们执行完“select  * from Person where ID=50000”时，我们会在消息选项卡中看到以下信息：

(1 行受影响)
表 'Person'。扫描计数 5，逻辑读取 10418 次，物理读取 105 次，预读 10418 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

这里面的一些信息是非常重要的，另一部分则可以不去考虑。

扫描次数：在查询中涉及到的表被访问的次数。在我们的例子中，Person表只被访问了5次，由于查询中不包括连接命令，这一信息并不是十分有用，但如果查询中包含有一个或多个连接，则这一信息是十分有用的。

逻辑读取：这是最有用的数据。我们知道，SQL Server在对任何数据进行操作前，必须先把数据读取到数据缓冲区中。此外，我们也知道SQL Server何时会从数据缓冲区中读取数据，并把数据读取到大小为8k字节的页中。逻辑读取得意思就是指SQL Server为得到查询结果，而必须从数据缓冲区中读取的页数。

SQL Server在执行逻辑读的时候，不会读取比实际结果多或者少的数据，因此在相同的数据集中，执行同一个查询，得到的逻辑读的结果总是相同的。因此，在进行查询优化时逻辑读的值就是来衡量你的优化措施是否可行的一个很好的标准。（在查询时逻辑读越少，其效率就越高，查询速度就越快，反之，就慢）

物理读取：在执行真正的查询操作前，SQL Server必须从磁盘上向数据缓冲区中读取他所需要的数据。在SQL Server开始执行查询前，当它发现要读的数据不再数据缓冲区中时，它会首先把它需要的数据读到数据缓冲区中。物理读取的意思就是指SQL Server把所需数据读到数据缓冲区中时，从物理磁盘上读取的数据页数。

遗憾的是，在我们进行查询优化时，是不需要考虑物理读的。尽管物理读要比逻辑读可能需要更多的服务器资源。因为SQL Server在执行查询时，是不可能通过性能调节而减少物理读的次数的。减少物理读是一项很复杂并且重要的工作，它涉及到的是整个服务器的性能调节，而不仅仅是查询性能的调节。在进行查询性能调节时，我们是不能控制数据缓冲区大小或服务器的忙碌程度，以及完成查询所需要的数据是在数据缓冲区还是在磁盘上，唯一我们可以控制的就是得到查询结果多需要执行的逻辑读的次数。因此在进行查询优化时，我们大可不必在意物理读的数据。

预读：指的是SQL Server在进行查询优化前，预测要读取的数据页，根据预读的准确程度，预读可能有用也可能没用。和物理读一样，在我们进行查询优化时是不需要考虑的。

剩下的几个“lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次”意思和前面说的差不多，只是在进行增、删、改的时候IO资源的操作情况。

所以呢，我们在做查询优化的时候，使用SET STATISTICS TIME 和SET STATISTICS IO 是个不错的选择。