一直想找一些关于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引入了动态管理对象例如DMVDMF。SQL Server 2008中添加了新的对象新的属性。这些饱含非常有用的信息利用这些信息可以监视SQL Server诊断问题进行性能监视。要仔细研究这些对象会很耗时。这里只是列举一些常用的。统计IO统计IO是是一个session选项。它返回域当前执行的语句相关的I/O信息。要使用这个选项首选清除数据缓存DBCC DROPCLEANBUFFERS;然后运行下面的代码来打开这个选项SET STATISTICS IO ON;SELECT orderid, custid, empid, shipperid, orderdate, fillerFROM dbo.OrdersWHERE orderdate 20060101AND 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允许我们查看一个图形化的执行计划快捷键CtrlL。注意当查看一个执行计划的时候查询并没有运行。一些度量值只能在运行完之后才能得到实际查询得到的行的数目。使用下面的语句来查看执行计划SELECT custid, empid, shipperid, COUNT(*) AS numorders FROM dbo.Orders WHERE orderdate 20080201 AND orderdate 20080301GROUP BY CUBE(custid, empid, shipperid);这个语句查询得到所有可能的聚合值聚合属性是custidempidshipperid。如图1图1注意当这个执行计划占用很大的屏幕空间的时候可以点击右下方的按钮“”不放然后拖动鼠标可以查看想要查看的区域。执行计划是由一些操作组成的树状结构图。数据从子运算流向父运算。这个结构的顺序是从右到左从上到下。在这个例子中运算首选从聚集索引开始然后是后面的操作缠绕运算-Table Spool注意每个运算符旁边有一个百分比这个值表值这个运算在整个执行过程中所占的资源百分比这个值只是优化器估计的值。SQL语句的优化工作应该放在那些所占的百分比比较大的操作上面。当把鼠标放上去的时候会有一个换色的提示框。有一个值是Estimated Subtree Cost。最上方最作坊的运算时整个运算的资源开销。如图2图2注意这些值只是优化器估计出的值优化器会使用这个值来和其他的估计值作比较进而选择出一个最优的执行计划。另外一个比较好的地方时你可以同时生成多个语句的执行计划进而对他们进行比较。例如下面的语句--1 SELECT custid, orderid, orderdate, empid, filler FROM dbo.Orders AS O1 WHERE orderid (SELECT TOP (1) O2.orderid FROM dbo.Orders AS O2 WHERE O2.custid O1.custid ORDER BY O2.orderdate DESC, O2.orderid DESC); --2 SELECT custid, orderid, orderdate, empid, filler FROM dbo.Orders WHERE orderid IN ( SELECT (SELECT TOP (1) O.orderid FROM dbo.Orders AS O WHERE O.custid C.custid ORDER BY O.orderdate DESC, O.orderid DESC) AS oid FROM dbo.Customers AS C ); --3 SELECT A.* FROM dbo.Customers AS C CROSS APPLY (SELECT TOP (1) O.custid, O.orderid, O.orderdate, O.empid, O.filler FROM dbo.Orders AS O WHERE O.custid C.custid ORDER BY O.orderdate DESC, O.orderid DESC) AS A; --4 WITH C AS ( SELECT custid, orderid, orderdate, empid, filler, ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate DESC, orderid DESC) AS n FROM dbo.Orders ) SELECT custid, orderid, orderdate, empid, filler FROM C WHERE n 1;他们的 查询结果是一样的但是执行计划是不同的。在每个执行计划的开头有一个百分比指示这个语句在所有的语句所占的开销的百分比。在这个例子中我们可以看到第一个语句的比例是37%第二个语句的比例是19%第三个是30%第四个是14%。从这个结果我们可以粗略的认定第四个语句的效率要高一些。当把鼠标放在运算符上面的时候会有一个黄色的提示框如图4