SQLServer查询执行计划分析.docx

看懂执行计划例子1以AdventureWorks的DatabaseLog查询为例：SELECT*FROM[AdventureWorks2008R2].[dbo].[DatabaseLog]?Cached plan size – how much memory the plan generated bythis query will take up in stored procedure cache. This is auseful number when investigating cache performance issuesbecause youll be able to see which plans are taking up morememory.?Estimated Operator Cost – weve already seen this as thepercentage cost in Figure 1.?Estimated Subtree Cost – tells us the accumulated optimizercost assigned to this step and all previous steps, but rememberto read from right to left. This number is meaningless in thereal world, but is a mathematical evaluation used by the queryoptimizer to determine the cost of the operator in question; itrepresents the amount of time that the optimizer thinks thisoperator will take.?Estimated number of rows – calculated based on thestatistics available to the optimizer for the table or index inquestion.The logical operators are the resultsof the optimizers calculations for what should happen when the queryexecutes. The physical operators represent what actually occurred. Thelogical and physical operators are usually the same, but not alwaysThe Subtree is simply the section of the execution tree that wehave looked at so far, working right to left again, and top to bottom. Allestimations are based on the statistics available on the columns andindexes in any table.The I/O Cost and CPU cost are not actual operators, but rather thecost numbers assigned by the Query Optimizer during its calculations.These numbers are useful when determining whether most of the costis I/O-based (as in this case), or if were putting a load on the CPU. Abigger number means more processing in this area.Youll note that, in this case, the operator cost and the subtree cost arethe same, since the table scan is the only operator. For more complextrees, with more operators, youll see that the cost accumulates as theindividual cost for each operator is added to the total. You get the f