[优化] SQL 执行统计信息

前言：我们知道的SQL语句分以下阶段：分析→编译→执行，我们在调优SQL语句的时候，除了要看执行计划外，还要关心一些统计信息，例如CPU占用时间啦，逻辑读的次数啦等等，这些相关的统计信息也能给我们SQL语句调优带来帮助，那么接下来我们大概的捋一捋。

 

实验：

    * 为避免缓存的影响我们首先要清掉数据库的缓存，清数据库缓存有两个操作，DBCC DROPCLEANBUFFERS 和 DBCC FREEPROCCACHE
，那么他们有什么区别呢？

    解答：

--DBCC DROPCLEANBUFFERS清除buffer pool里的数据页面

--DBCC FREEPROCCACHE
清除memtoleave和buffer pool里的执行计划内存

--绝大部分内存使用都会在buffer pool里，对于一些特别长的语句使用一部分multi-page  

 

统计信息：

 

SQL 语句：

SELECT TOP 200 * FROM TB_Order o 
LEFT JOIN TB_OrderLog ol ON o.ID=ol.OrderID WHERE o.ID<300000 AND Tx IS NOT NULL  

 

1. SQL 在数据库中分析、编译、运行时间统计：SET STATISTICS TIME ON/OFF

    先看第一个图

        

    表中分析和编译的时间统计在一起，最后是执行时间，接下来逐一解释，

    CPU时间：SQL所花纯CPU时间，如果多颗CPU运行，那么是所有CPU所用时间之和，也就是CPU占用资源

    占用时间：是这一步所用时间，此步骤可能发生I/O操作，产生I/O等待，或者是遇到阻塞，产生阻塞等待等，但是没用CPU资源

    分析编译时间：由于之前，我们将数据库的  执行计划缓存清掉了。需要对SQL语句编译，由于编译主要是CPU上的运算，所以CPU时间大于占用时间是正常的

    执行时间：之前数据缓存清掉了，所以需要将数据从磁盘读到内存中

 

    再一次运行发现，分析和编译时间降低为0，执行时间中CPU时间一直没变，但是占用时间由17秒多降到2秒多，这是因为数据已由磁盘缓存到内存中，所有的读取都是从内存中读取，IO消耗就降下来了。

 

2. SQL 语句所执行的物理读取、逻辑读取次数：SET STATISTICS IO ON/OFF

    再来看看这个统计信息，它是统计SQL进行的物理读取、逻辑读取、表扫描次数等。在SQL优化中也会起到很大作用。SQL语句还是上面那个，为了消除前一次执行的SQL对本次的影响，同样需要清掉数据缓存、以及执行计划缓存。

后得出下面信息：

(200 行受影响)

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

表'TB_Order'。扫描计数1，逻辑读取6 次，物理读取1 次，预读2016 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

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

 

咦，奇怪，我明明是查的TB_Order表，怎么会多出一个Worktable表呢？MSDN上是这么解释的：

关系引擎可能需要生成一个工作表以执行SQL语句中的特定逻辑，工作表是用于保存中间结果的内存表。如某些ORDER BY , GROUP BY , UNION等执行的过程中都会产生工作表，工作表在tempdb中，在不需要的时候自动删除

由上，我们又想到一个问题，就是在优化的过程中，如果看到Worktable的逻辑读取等特别高，就可以重点检查一下那些ORDER BY , GROUP BY 和 UNION的操作是否合理。好吧言归正传，解释下输出的这些含义：

    扫描次数：这里包括对数据表的扫描，以及索引的扫名

    逻辑读取：从数据缓存中读取数据的度量，为什么这里用到“度量”二字呢，你也看到了，逻辑读取的单位是次，这是因为这里统计的原理是，在执行读写操作的时候，会运行一段代码，代码每运行一次，这个度量就会+1，最后得到的是这个数，而不是数据库单位页，也不是存储大小KB等。

    物理读取：从磁盘上读取的度量    

    预读：为进行查询而预读入缓存的度量

    lob逻辑读取、lob物理读取、 lob预读：这三个一起说，就是读取text，ntext，image等大值类型页的度量

不清缓存，我们在运行一下，上面的SQL，可以看到如下：

(200 行受影响)

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

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

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

 

没错，逻辑读依然存在，物理读取和预读都降为0了，就是说数据这个时候已经在缓存里了。

再说说逻辑读，这个家伙很关键，如果一个SQL语句有多种写法，那么逻辑读（越少越好）是衡量哪个是最优SQL的最大依据。

 

3. SQL 语句的执行计划一级每一步返回的行数：

SET SHOWPLAN_ALL  ON/OFF  

SET STATISTICS PROFILE ON/OFF

    上述俩个复杂一点，二者均是返回每一步执行的过程中相关信息的统计行集，但第二个更为详细，他会将操作符所处理的信息也会统计到，还包括该步骤返回的行数，该步骤在整个SQL执行中执行的次数等，利用这些信息分析语句的调优方向，也可以判断SQL Server是否选择了一个正确的执行计划。

Rows：执行计划返回的实际行数

Executes：执行计划每一步被执行了多少次

StmtText：执行计划每一步的内容

EstimateRows：SQL SERVER根据表格中的统计信息预估这一步返回的行数

EstimateIO：SQL SERVER根据表格统计信息以及上面的预估返回行数，预估这一步的IO消耗

EstimateCPU：SQL SERVER根据表格统计信息以及上面的预估返回行数，预估这一步的CPU消耗

TotalSubtreeCost： SQL SERVER根据EstimateIO和EstimateCPU通过某种公式计算出每一步执行计划树的消耗

Warnings：每一步中遇到的警告

Parallel：执行计划的这一步使用的并行的执行计划

 

 

 

最后说比较重要一点，也是跟统计有关的：

在执行查询的时候，查询优化器所选择的查询策略是跟根据该表的统计信息去选择的，表的统计信息一般什么时候自动更新呢？这是微软的一个算法，当表中数据修改或添加到某一个值的时候，就会触发自动更新统计信息，或者呢你可以通过SQL手动更新，如下：

UPDATE STATISTICS TB_Order  

 

 

 

 

 

 

 

 

 

，