缓存执⾏计划
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( 清除⼀个特定的执⾏计划使⽤下⾯的语句： DBCC FREESYSTEMCACHE( 可以使⽤’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, fillerFROM 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）；多少次读取硬盘（physicalreads 俺的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优化器⽣成的如何处理给定的请求的⼀个⼯作计划。它包含这个请求中药⽤到的操作符。有⼀些操作可能会执⾏多次。⼀些计划分⽀可能会并⾏执⾏。在这个⼯作计划中，优化器决定获取语句中涉及到的表的顺序，要使⽤到那些索引，要使⽤那些查询⽅法，要使⽤那些算法等等。事实上，优化器会在多个执⾏计划中选择出⼀个最优的，资源耗费最少的。频繁地⽣成执⾏计划也会耗费时间，所以SQLServer也会根据数据量的⼤⼩估算⽣成执⾏计划所需要的阀值时间。⽣成执⾏计划的时间不会超过这个估算的阀值时间。还有⼀个阀值是根据耗费的资源计算得到的。如果⼀个⼯作计划的资源耗费低于这个阀值，就认为它是⾜够好的，优化器就会停⽌优化使⽤这个计划。 图形执⾏计划 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 SubtreeCost。最上⽅，最作坊的运算时整个运算的资源开销。如图2 图2 注意这些值只是优化器估计出的值，优化器会使⽤这个值来和其他的估计值作⽐较进⽽选择出⼀个最优的执⾏计划。另外⼀个⽐较好的地⽅时你可以同时⽣成多个语句的执⾏计划，进⽽对他们进⾏⽐较。例如下⾯的语句： --1 SELECT custid, orderid, orderdate, empid, fillerFROM dbo.Orders AS O1WHERE orderid = (SELECT TOP (1) O2.orderidFROM dbo.Orders AS O2 WHERE O2.custid = O1.custid ORDER BY O2.orderdate DESC, O2.orderid DESC);--2 SELECT custid, orderid, orderdate, empid, fillerFROM dbo.OrdersWHERE orderid IN( SELECT (SELECT TOP (1) O.orderidFROM dbo.Orders AS OWHERE O.custid = C.custid ORDER BY O.orderdate DESC, O.orderid DESC) AS oidFROM dbo.Customers AS C);--3 SELECT A.* FROM dbo.Customers AS CCROSS APPLY(SELECT TOP (1) O.custid, O.orderid, O.orderdate, O.empid, O.fillerFROM dbo.Orders AS OWHERE 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 custidORDER BY orderdate DESC, orderid DESC) AS nFROM dbo.Orders) SELECT custid, orderid, orderdate, empid, fillerFROM C WHERE n = 1; 他们的 查询结果是⼀样的，但是执⾏计划是不同的。在每个执⾏计划的开头有⼀个百分⽐指⽰这个语句在所有的语句所占的开销的百分⽐。在这个例⼦中我们可以看到第⼀个语句的⽐例是37%，第⼆个语句的⽐例是19%，第三个是30%，第四个是14%。从这个结果我们可以粗略的认定第四个语句的效率要⾼⼀些。 当把⿏标放在运算符上⾯的时候会有⼀个黄⾊的提⽰框如图4 图4 在这个提⽰框中有下⾯的⼀些度量信息： 操作符的名字和简单的介绍 物理运算：计算机内部的物理运算 逻辑运算：与物理运算符匹配的逻辑运算符，如 Inner Join 运算符。逻辑运算符列在物理运算符之后，两者均位于⼯具提⽰的顶部。返回的⾏数： 运算返回的数据⾏数 估计I/O开销，估计CPU开销： 这个数据可以⽤来估算这个操作是不是造成很⼤的CPU或者I/O开销，⼀般Sort操作都会造成很⼤的I/O开销 估计执⾏⾏数和执⾏⾏数：估计该操作执⾏的次数和实际执⾏的次数。这个数据可以帮助你找到更好的执⾏语句估计执⾏开销：⽤于执⾏此操作的查询优化器的开销 估计⼦树开销：查询优化器执⾏此操作及同⼀⼦树内位于此操作之前的所有操作的总开销 运算⽣成的⾏数：估计运算符⽣成的⾏数。有些情况下可以通过实际⾏数和估计⾏数之间的差异来判断⼀个SQL语句的优劣 估计数据⼤⼩：操作符⽣成的⾏的估计⼤⼩（字节）。可能你会疑惑为什么这个实际⾏数没有显⽰在执⾏计划⾥⾯，那是因为数据⾏⾥⾯有可变长度的数据类型 实际的重绑和重绕： 这个数据之和⼀些特定的操作有关（⾮聚集的缠绕，远程请求，⾏数缠绕，排序，表缠绕，表值函数，断⾔，过滤等）。只有在内层嵌套查询的时候这才会统计个度量信息，否则Rebinds是1，Rewinds是0。这些数据表⽰内层的Init⽅法被调⽤。重绑和重绕的综合应该是外连接得到的⾏数之和。重绑意味着⼀个或者多个相关的连接参数改变了，需要重新估算。重绕意思是相关的参数没有改变，可以重⽤先前得到的内部结果集底部的信息：显⽰相关的对象名，输出，参数等等 选中⼀个操作符，按下F4键，可以查看更加详细的信息。 ⽂本格式的执⾏计划 可以通过设置以⽂本格式查看执⾏计划。设置SHOWPLAN_TEXT选项可以达到这个⽬的，如下：SET SHOWPLAN_TEXT ON; SELECT orderid, custid, empid, shipperid, orderdate, fillerFROM dbo.Orders WHERE orderid = 280885; 查看执⾏计划（CTRL+L）得到下⾯的结果：(1 row(s) affected)StmtText -----------------------------------------------------------------------------------------------------SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885;(1 row(s) affected) StmtText ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [Performance].[dbo].[Orders].[orderdate])) |--Index Seek(OBJECT:([Performance].[dbo].[Orders].[PK_Orders]), SEEK:([Performance].[dbo].[Orders].[orderid]=[@1]) ORDEREDFORWARD) |--Clustered Index Seek(OBJECT:([Performance].[dbo].[Orders].[idx_cl_od]), SEEK:([Performance].[dbo].[Orders].[orderdate]=[Performance].[dbo].[Orders].[orderdate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)(3 row(s) affected)(1 row(s) affected) 分析这个执⾏计划，从内层的分⽀到外层分⽀，从上到下。但是在这⾥我们只能看到运算符的名字和参数。运⾏下⾯的语句关闭这个选项：SET SHOWPLAN_TEXT OFF; 如果想得到更加详细的执⾏计划信息，使⽤SHOWPLAN_ALL选项查看执⾏计划，STATISTICS PROFILE选项查看具体的某⼀个执⾏计划。SHOWPLAN_ALL将执⾏计划的信息写⼊到⼀个表中，其中包含的⼀些估计的值有：StmtText, StmtId, NodeId, Parent, PhysicalOp,LogicalOp, Argument, Defi nedValues,EstimateRows, EstimateIO, EstimateCPU, AvgRowSize, TotalSubtreeCost, OutputList,Warnings,Type, Parallel, and EstimateExecutions。通过下⾯的语句打开这个选项：SET SHOWPLAN_ALL ON;运⾏下⾯的语句： SELECT orderid, custid, empid, shipperid, orderdate, fillerFROM dbo.Orders WHERE orderid = 280885;得到的结果如下图5： 图5 运⾏下⾯的语句关闭选项：SET SHOWPLAN_ALL OFF; STATISTICS PROFILE选项会产⽣⼀个实际的计划。设置这个选项为ON的时候显⽰的结果和设置SHOWPLAN_ALL为ON差不多，不过多了两个属性Rosw和Executes，表⽰实际的⾏数和运⾏⾏数。 语句如下： SET STATISTICS PROFILE ON; SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885;取消设置： SET STATISTICS PROFILE OFF; XML格式的执⾏计划 如果想⽤⾃⼰的代码来描述执⾏计划或者把执⾏计划发送给客户或者同事，你会发现使⽤⽂本格式的信息很不⽅便。SQL Server 2008允许允许返回XML格式的执⾏计划内容，这⾮常利于使⽤应⽤程序代码处理。打开使⽤SQL Server 2008产⽣的xml格式的执⾏计划会显⽰成图形结果，后缀是.sqlplan。 打开这个选项的代码如下：SET SHOWPLAN_XML ON; 运⾏语句 SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885; 运⾏结果如下图6： 图6 点击这个xml⽂件，图形格式的执⾏计划如下图7： 图7 使⽤下⾯的语句关闭选项：SET SHOWPLAN_XML OFF; 为了不影响其他语句的输出效果建议使⽤类似下⾯的代码来查看效果：SET STATISTICS XML ON;GO SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885;GO SET STATISTICS XML OFF; 可以看出XML格式的执⾏计划提供了最友好的查看形式。 因篇幅问题不能全部显示，请点此查看更多更全内容