读懂语句执行的统计信息

对于语句的运行，除了执行计划本身，还有一些其他因素要考虑，例如语句的编译时间、执行时间、做了多少次磁盘读等。如果DBA能够把问题语句单独测试运行，可以在运行前打开下面这三个开关，收集语句运行的统计信息。这些信息对分析问题很有价值。

[TOC]

SET STATISTICS TIME ON

先来看看SET STATISTICS TIME ON会返回什么信息，运行语句：

DBCC DROPCLEANBUFFERS    --.清除buffer pool里的所有缓存数据
DBCC FREEPROCCACHE       --.清除buffer pool里的所有缓存的执行计划
SET STATISTICS TIME ON
SELECT TOP 1000 * FROM dbo.ip2location WITH(NOLOCK) WHERE city = 'Shanghai'

除了结果集之外，SQL Server还会返回下面这两段信息

SQL Server 分析和编译时间:
    CPU 时间 = 15 毫秒，占用时间 = 104 毫秒。
SQL Server 执行时间:
    CPU 时间 = 171 毫秒，占用时间 = 1903 毫秒。

大家知道SQL Server执行语句是分以下阶段：分析 -> 编译 -> 执行。
根据表的统计信息分析出比较合适的执行计划，然后编译语句，最后执行语句。下面说一下上面的输出是什么意思：

CPU时间：这一步SQL Server所花的纯CPU时间是多少。也就是说，语句花了多少CPU资源

占用时间：这一步一共用了多少时间。也就是说，这是语句运行的时间长短，有些动作会发生I/O操作，产生了I/O等待，或者是遇到阻塞、产生了阻塞等待。总之时间用掉了，但是没有用CPU资源。所以占用时间比CPU时间长是很正常的，但是CPU时间是语句在所有CPU上的时间总和。如果语句使用了多颗CPU，而其他等待几乎没有，那么CPU时间大于占用时间也是正常的。

分析和编译时间：这一步就是语句的编译时间。由于语句运行之前清空了所有执行计划，SQL Server必须要对他编译。这里的编译时间就不为0了。由于编译主要是CPU的运算，所以一般CPU时间和占用时间是差不多的。如果这里相差比较大，就有必要看看SQL Server在系统资源上有没有瓶颈了。

SQL Server执行时间：语句真正运行的时间。由于语句是第一次运行，SQL Server需要把数据从磁盘读到内存里，这里语句的运行发生了比较长的I/O等待。所以这里的CPU时间和占用时间差别就很大了，分别是171和1903毫秒。总的来讲，这条语句花了104+1903+15+172=2193毫秒，其中CPU时间为15+171=186毫秒，语句的主要时间应该是都花在了I/O等待上。

现在再来运行一遍，但不清空缓存：

SET STATISTICS TIME ON
SELECT TOP 1000 * FROM dbo.ip2location WITH(NOLOCK) WHERE city = 'Shanghai'

这次比上次快很多，输出时间统计信息是：

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

由于执行计划被重用，“SQL分析和编译时间” CPU时间是0，占用时间是0；由于数据已经缓存在内存里，不需要从磁盘上读取，SQL执行时间的CPU时间是156毫秒，占用时间这次和CPU时间非常接近，为169毫秒。这里省下运行时间1903-169=1734毫秒，从这里可以再次看出，缓存对语句执行性能起着至关重要的作用。

为了不影响其他测试，请运行下面的语句关闭SET STATISTICS TIME ON

SET STATISTICS TIME OFF

SET STATISTICS IO ON

这个开关能够输出语句做的物理读和逻辑读的数目。对分析语句的复杂度有很重要的作用，还是刚才那个查询：

DBCC DROPCLEANBUFFERS    --.清除buffer pool里的所有缓存数据
DBCC FREEPROCCACHE       --.清除buffer pool里的所有缓存的执行计划
SET STATISTICS IO ON
SELECT TOP 1000 * FROM dbo.ip2location WITH(NOLOCK) WHERE city = 'Shanghai'

除了结果集之外，SQL Server还会返回下面这两段信息

表 'ip2location'。扫描计数 1，逻辑读取 9246 次，物理读取 0 次，预读 13702 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

下面说一下上面的输出是什么意思：
表：表的名称。这里的表就是SalesOrderDetail_test

扫描计数：执行的扫描次数。

一般来讲大表扫描的次数越多越不好。唯一的例外是如果执行计划选择了并发运行，由多个thread线程同时做一个表的读取，每个thread读其中的一部分，但是这里会显示所有thread的数目。也就是有几个thread在并发做，就会有几个扫描。这时数目大一点没问题的。

逻辑读取：从数据缓存读取的页数。页数越多，说明查询要访问的数据量就越大，内存消耗量越大，查询也就越昂贵。可以检查是否应该调整索引，减少扫描的次数，缩小扫描范围。

• 为什么逻辑读取结果的单位不是Page，也不是K或KB。SQL Server在做读和写的时候，会运行到某一段特定的代码。每调用一次这个代码，Reads/Write就会加1。如果这个值比较大，那语句一定做了比较多的I/O，但是不能通过这个值计算出I/O的绝对数量，这个值反映的是逻辑读写量，不是物理读写量。
  
• 优化SQL语句的关键是尽可能减少语句的logical reads。如果Logical reads很大，而返回的行数很少，也即两者相差较大，那么往往意味者语句需要优化。

物理读取：从磁盘读取的页数。

预读：用估计信息，去硬盘读取到缓存的页数。

物理读取+预读：就是SQL Server为了完成这句查询而从磁盘上读取的页数。

如果不为0，说明数据没有缓存在内存里，则运行速度一定会受到影响。

LOB逻辑读取：从缓存读取text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)的数目

LOB物理读取：从磁盘读取的text、ntext、image、大值类型页的数目

LOB预读：为进行查询而放入缓存的text、ntext、image、大值类型页的数目

区分预读、逻辑读、物理读
当SQL Server执行一个查询语句时，首先会生成查询计划，同时用估计的数据去磁盘读取数据(预读,用于估计而读取硬盘数据到缓存,比较慢)，这两步是并行的。SQL Server通过这种方式来提高查询性能。查询计划生成好了以后再去缓存读取数据，当发现缓存缺少所需要的数据后，再让缓存去读硬盘(物理读,读的是硬盘,速度慢)，然后从缓存中取出所有数据(逻辑读,读的是缓存,比较快)。

现在再来运行一遍，但不清空缓存：

SET STATISTICS IO ON
SELECT TOP 1000 * FROM dbo.ip2location WITH(NOLOCK) WHERE city = 'Shanghai'

结果集返回：

表 'ip2location'。扫描计数 1，逻辑读取 9246 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

这次逻辑读取不变，还是9246页。但是物理读取和预读都是0了。说明数据已经缓存在内存里，第二次运行不需要再从磁盘上读一遍，节省了时间。

为了不影响其他测试，请运行下面语句关闭 SET STATISTICS IO ON

SET STATISTICS IO OFF

SET STATISTICS PROFILE ON

这是三个设置中返回最复杂的一个，它返回语句的执行计划，以及语句运行在每一步的实际返回行数统计。通过这个结果，不仅可以得到执行计划，理解语句执行过程，分析语句调优方向，也可以判断SQL Server是否选择了一个正确的执行计划。

DBCC DROPCLEANBUFFERS    --.清除buffer pool里的所有缓存数据
DBCC FREEPROCCACHE       --.清除buffer pool里的所有缓存的执行计划
SET STATISTICS PROFILE ON
SELECT COUNT(b.ipto) FROM ip2location a 
INNER JOIN ip2location b ON a.ipto=b.ipto WHERE a.ipto<715063408

注意：这里是从最下面开始向上看的，而且有多少行就表示SQL Server执行了多少个步骤。

返回的结果集很长，下面说一下重要字段：

• Rows: 执行计划的每一步返回的实际行数
• Executes: 执行计划的每一步被运行了多少次
• StmtText: 执行计划的具体内容。执行计划以一棵树的形式显示，每一行都是一步，会有结果集返回和cost
• EstimateRows: SQL Server根据表格上的统计信息，预估的每一步的返回行数。 经常将Rows和EstimateRows做对比，先确认SQL Server预估是否正确，以判断统计信息是否有更新
  
• EstimateIO: 根据EstimateRows和统计信息里记录的字段长度，预估的每一步会产生的I/O cost
• EstimateCPU: 根据EstimateRows和统计信息里记录的字段长度，以及复杂度，预估每一步会产生的CPU cost
• TotalSubtreeCost: 根据EstimateIO和EstimateCPU通过某种计算公式，计算出每一步执行计划子树的cost（包括这一步自己的cost和他的所有下层步骤的cost总和），下面介绍的cost说的都是这个字段值
• Warnings: 在运行每一步时遇到的警告，例如，某一步没有统计信息支持cost预估等
• Parallel: 执行计划的这一步是不是使用了并行的执行计划

从上面结果可以看出执行计划分成4步，其中第一步又分成并列的两个子步骤：

• 步骤a1（第5行）：从ip2location表里找出所有a.ipto<715063408的值； 因为ipto这个字段上有一个聚集索引，所以使用了Clustered Index Seek(LogicalOp)，预测返回1049210行(EstimateRows)，而实际返回了1032982行(Rows,这个是实际的行数)，这一步的cost是15.28482(totalsubtreecost)
• 步骤a2（第6行）：从ip2location表里找出所有b.ipto<715063408的值；
• 步骤b（第4行）：将a1和a2两步得到的结果集做一个Merge Join(LogicalOp)； SQL Server预估这个join返回1049210行，实际返回1032982行。因为字段ipto上有统计信息，所以这里的预估非常准确。这一步的cost等于totalsubtreecost减去他的子步骤，即35.08685-15.28482-15.28482=4.51721。
• 步骤c（第3行）：在join返回的结果集的基础上算count()的值； 这一步比较简单，count()的结果总是1，所以预测值是正确的。这一步的cost是根据上一步join返回的结果集大小预估出来的。由于步骤b的预估返回值非常准确，所以这一步的cost非常低，即35.71637-35.08685=0.62952
• 步骤d（第2行）：将步骤c返回的值转换为int类型，作为结果返回； 这一步是上一步的继续，更为简单。convert一个值的数据类型所要的cost几乎可以忽略不计。所以这棵子树的cost和他的子节点相等，都是35.71637。也就是说，这一步的cost是0。 通过这样的方法，用户可以了解到语句的执行计划、SQL Server预估的准确性、cost的分布。