SQL点滴27—性能分析之执行计划

一直想找一些关于SQL语句性能调试的权威参考，但是有参考未必就能够做好调试的工作。我深信实践中得到的经验是最珍贵的，书本知识只是一个引导。本篇来源于《Inside Microsoft SQL Server 2008》，有经验的高手尽管拍砖把。

这个部分将讲解一些性能分析工具，这些性能分析主要关注在执行计划。

缓存执行计划 

SQL Server 2008提供了一些服务器视图来分析执行计划
Sys.dm_exec_cached_plans：    包含缓存的执行计划，每个执行计划对应一行。
Sys.dm_exec_plan_attributes： 这是一个系统函数，每一个执行计划都对应着一些属性，在这个系统函数中包含着这些属性。
Sys.dm_exec_sql_text：             这是一个系统函数，返回文字格式的执行计划。
Sys.dm_exec_query_plan：        这是一个系统函数，返回xml格式的执行计划。

SQL Server 2008还提供了一个兼容性的视图sys.syscacheobject，这个视图中保存了所有的执行计划的信息。

清除缓存 

在进行性能分析的时候有时候需要清除缓存以便进行下一次分析。SQL Server提供了一些工具来清除缓存的性能数据。使用下面的语句来完成这些任务。 

清除全局缓存使用下面的语句： 

DBCC DROPCLEANBUFFERS;

从全局缓存中清除执行计划缓存，使用下面的语句： 

DBCC FREEPROCCACHE;

清除某一个数据库中的所有执行计划缓存，使用下面的语句： 

DBCC FLUSHPROCINDB(<db_id>);

清除某一个特定的执行计划缓存使用下面的语句： 

DBCC FREESYSTEMCACHE(<cachestore>);

可以使用’ALL’，pool_name，’Object Plan’，’SQL Plans’，’Bound Trees’作为输入参数。

'ALL'参数标明要清除所有的缓存

'pool_name'参数表明要清除的一个缓存池的名字。

'Object Plans'清除对象计划（例如存储过程，触发器，用户定义函数等等）。

'SQL Plans'用来清除要立即执行的语句的执行计划缓存。

'Bound Trees'定义清除视图，约束等的缓存。

注意：在使用这些语句清除缓存之前要想清楚，特别是在生产环境。这些对性能有很大的影响。清除这些缓存之后SQL Server需要从数据页中重新读取数据。并且SQL Server需要重新生成新的执行计划。因此在清除之前要想清楚这些对生产或者测试环境的影响。

动态的管理对象

SQL Server 2005引入了动态管理对象/视图，例如DMV，DMF。SQL Server 2008中添加了新的对象，新的属性。这些视图包含非常有用的信息，利用这些信息可以监视SQL Server，诊断问题，进行性能监视。要仔细研究这些对象会很耗时。这里只是列举一些常用的对象/视图。

统计IO

统计IO是是一个session选项。它返回域当前执行的语句相关的I/O信息。要使用这个选项首先清除数据缓存，这样会更加准确：

DBCC DROPCLEANBUFFERS;

然后运行下面的代码来打开这个选项：

SET STATISTICS IO ON;
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo.Orders
WHERE orderdate >= '20060101'
AND orderdate < '20060201';

 
最后可以得到类似下面的信息：

(21226 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 537, physical reads 3, read-ahead reads 549, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

从输出信息中我们可以看到在执行计划中有多少次获取表（Scan count）；多少次读取缓存（logical reads）；多少次读取硬盘（physical reads ，read-ahead reads）；多少次读取大的对象（lob physical reads ， log read-ahead reads）。
使用下面的语句来关闭这个选项：

SET STATISTICS IO OFF;

统计运行时间 

STATISTICS TIME是一个用来返回CPU时钟时间的session选项。它返回语法分析，编译，执行的时间。要使用这个选项首选要清除执行计划缓存，这样会更加准确。 

DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;

运行下面的语句来打开相应的选项： 

SET STATISTICS TIME ON;

运行下面的语句：

SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderdate >= '20060101' AND orderdate < '20060201';

得到下面的信息： 

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 4 ms. 
SQL Server Execution Times: 
   CPU time = 46 ms,  elapsed time = 544 ms.

从这些信息中可以获得执行这个语句时候的CPU时钟时间，编译时间，运行时间。运行下面的语句可以关闭这个选项： 

SET STATISTICS TIME OFF;

当需要分析一个单独的语句的性能的时候这个选项非常有用。当需要使用批处理的模式来运行语句的时候需要度量会有所不同。在查询之前保存SYSDATETIME函数的值，并写入到一个表中。注意这个函数返回的时间格式是DATETIME2，可以精确到100纳秒。这个函数的准确性取决于计算机硬件和操作系统版本。因为这个函数会调用GetSystemTimeAsFileTime()这个WindowsAPI。需要统计时间的时候可以重复地运行请求语句，然后记录下需要的时间。  　

分析执行计划 

执行计划是SQL优化器生成的如何处理给定的请求的一个工作计划。它包含这个请求中药用到的操作符。有一些操作可能会执行多次。一些计划分支可能会并行执行。在这个工作计划中，优化器决定获取语句中涉及到的表的顺序，要使用到那些索引，要使用那些查询方法，要使用那些算法等等。事实上，优化器会在多个执行计划中选择出一个最优的，资源耗费最少的。频繁地生成执行计划也会耗费时间，所以SQL Server也会根据数据量的大小估算生成执行计划所需要的阀值时间。生成执行计划的时间不会超过这个估算的阀值时间。还有一个阀值是根据耗费的资源计算得到的。如果一个工作计划的资源耗费低于这个阀值，就认为它是足够好的，优化器就会停止优化使用这个计划。

图形执行计划

SSMS允许我们查看一个图形化的执行计划（快捷键Ctrl+L）。注意当查看一个执行计划的时候，查询并没有运行。一些度量值只能在运行完之后才能得到（实际查询得到的行的数目）。

使用下面的语句来查看执行计划：

SELECT custid, empid, shipperid, COUNT(*) AS numorders FROM dbo.Orders WHERE orderdate >= '20080201' AND orderdate < '20080301'
GROUP BY CUBE(custid, empid, shipperid);

这个语句查询得到所有可能的聚合值，聚合属性是custid，empid，shipperid。如图1

图1

注意当这个执行计划占用很大的屏幕空间的时候可以点击右下方的按钮“+”不放，然后拖动鼠标可以查看想要查看的区域。

执行计划是由一些操作组成的树状结构图。数据从子运算流向父运算。这个结构的顺序是从右到左，从上到下。在这个例子中，运算首选从聚集索引开始，然后是后面的操作缠绕运算-Table Spool

注意每个运算符旁边有一个百分比，这个值表示这个运算在整个执行过程中所占的资源百分比，这个值只是优化器估计的值。SQL语句的优化工作应该放在那些所占的百分比比较大的操作上面。当把鼠标放上去的时候，会有一个灰色的提示框。有一个值是Estimated Subtree Cost。最上方，整个运算的资源开销。如图2

图2

注意这些值只是优化器估计出的值，优化器会使用这个值来和其他的估计值作比较进而选择出一个最优的执行计划。

文章转载自：

https://www.cnblogs.com/tylerdonet/archive/2011/11/17/2253090.html

文章经作者授权转载，版权归原文作者所有

图片来源于网络，侵权必删！