Execution plan for SQL Server

One: Execution plan generation process

When it comes to execution planning, the first thing to know is the process of executing the plan, so that you can do it, and I'll draw a diagram below:

1. Analysis Process

These three are easier to understand, first of all we have to ensure that SQL syntax can not be wrong, select and join tables must exist, and you have to execute this SQL permissions, the wrong ...

So we're done. The first process that executes the plan life cycle.

2. Compilation process

To ensure that the above SQL three points, the engine will have to bite the bullet to see you such a big lump of rotten SQL, the deletion of the delete, the change, the conversion, such as your "sub-query" will be translated into

"Table Connection" and so on ... In fact, it is very difficult for the engine, for instance.

<1> SQL generated by subquery:

SQL generated by <2>join:

From the above two results, you can see that everyone is playing join, if you look closely, you will find a "hash match", one is "nested loop", why not the same, which

Of course, the engine is based on a number of circumstances, such as: Disk IO, logical reading, resource consumption, hardware environment and so on ... This is also known as the "plan optimization" operation.

3. Execution process

Now that the execution plan has been chosen, it is a matter of course to execute, after execution, the SQL and execution plan will be put into the cache, so that the next time you have the same SQL comes directly from

Cache, you do not need to build the plan again, and you see that generating the execution plan is more CPU-intensive.

Two: Look at the SQL and the planned cache for execution

I also said that SQL and plan have been put into the cache, then my curiosity is strong, I would like to see where the SQL and plan in the end, and what a long ugly look, just

SQL Server is still relatively capable of satisfying our G-spot.

1. To make it easier to view the cache, I need to clear all the caches first, such as the following statement.

DBCC freeproccacheselect c.* from dbo. Category as Cjoin dbo. Product as PON c.categoryid=p.categoryidwhere c.categoryid=23794

2. Get the SQL and plan pointers (plan_handle) via Sys.dm_exec_cached_plans, as

SELECT * from Sys.dm_exec_cached_plans

You see two adhoc (instant query), the join query I executed in the first step and the select I executed in step two.

3. Now that we've got 2 adhoc plan_handle, what's the difference between using Dm_exec_sql_text to see their SQL?

4. After reading the text cache, let's continue to look at the SQL plan cache. Can be viewed through dm_exec_query_plan.

The Query_plan field above is called the execution plan, which is stored in the field as XML ... So parsing this XML is still time-consuming ...

  1 <?xml version= "1.0"?> 2 <showplanxml xmlns= "Http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version= "1.1" build= "10.0.1600.22" > 3 <BatchSequence> 4 <Batch> 5 <statements > 6 <stmtsimple statementtext= "select c.* from dbo. Category as C 7 joins dbo. Product as P 8 on C.categoryid=p.categoryid 9 WHERE c.categoryid=23794 "statementid=" 1 "statementcompid=" 1 "Statementt Ype= "Select" statementsubtreecost= "1.33278" statementestrows= "1.03803" statementoptmlevel= "full" QueryHash= " 0xb10b821b9b5e6396 "queryplanhash=" 0x8c7b3b1660e28d16 "> <statementsetoptions quoted_identifie R= "true" arithabort= "true" concat_null_yields_null= "true" ansi_nulls= "true" ansi_padding= "true" ansi_warnings= "true  "numeric_roundabort=" false "/> One <queryplan cachedplansize=" "Compiletime=" 2 "compilecpu=" 2 " compilememory= "168" > <missingindexes> <missingindexgroup impact= "99.4633" > <missingin Dex database= "[Mypetshop]" schema= "[dbo]" table= "[Product]" > <columngroup Usa                                     ge= "Equality" > <column name= "[CategoryId]" columnid= "2"/> 17                             </ColumnGroup> </MissingIndex> 19                                 </MissingIndexGroup> <missingindexgroup impact= "99.4636" > 21                                     <missingindex database= "[Mypetshop]" schema= "[dbo]" table= "[Product]" > 22 <columngroup usage= "Equality" > <column name= "[C                                 Ategoryid] "columnid=" 2 "/> </ColumnGroup> 25 </missingindex&Gt                         </MissingIndexGroup> </MissingIndexes> 28 <relop nodeid= "0" physicalop= "Nested Loops" logicalop= "Inner Join" estimaterows= "1.03803" estimateio= "0" estimatecpu= "4.33898e-006" avgrowsize= "estimatedtotalsubtreecost=" 1.33278 "parallel=" 0 "estimaterebinds=" 0 " estimaterewinds= "0" > <OutputList> <columnref                                 Erence database= "[Mypetshop]" schema= "[dbo]" table= "[Category]" alias= "[C]" column= "CategoryId"/> 31 <columnreference database= "[Mypetshop]" schema= "[dbo]" table= "[Category]" alias= "[C]" column= "Name"/&gt ; <columnreference database= "[Mypetshop]" schema= "[dbo]" table= "[Category]" alias= "[C] "column=" Image "/> </OutputList> <nestedloops O         ptimized= "0" > 35                        <relop nodeid= "1" physicalop= "Clustered Index Seek" logicalop= "Clustered Index Seek" Estimatero ws= "1" estimateio= "0.003125" estimatecpu= "0.0001581" avgrowsize= "$" estimatedtotalsubtreecost= "0.0032831"                                     Tablecardinality= "1.00001e+006" parallel= "0" estimaterebinds= "0" estimaterewinds= "0" > 36 <OutputList> PNS <columnreference database= "[Mypetshop]" schema= "[db O] "table=" [Category] "alias=" [C] "column=" CategoryId "/>. <columnreferenc                                         E database= "[Mypetshop]" schema= "[dbo]" table= "[Category]" alias= "[C]" column= "Name"/> 39 <columnreference database= "[Mypetshop]" schema= "[dbo]" table= "[Category]" alias= "[C]" column= "Image"/> </OutputList> <indexscan ordered= "1 "Scandirection=" FORWARD "forcedindex="0 "forceseek=" 0 "noexpandhint=" 0 "> <DefinedValues> 43 <DefinedValue> <columnreference Da                                             Tabase= "[Mypetshop]" schema= "[dbo]" table= "[Category]" alias= "[C]" column= "CategoryId"/> 45                                                 </DefinedValue> <DefinedValue> 47 <columnreference database= "[Mypetshop]" schema= "[dbo]" table= "[Category]" alias= "[                                             C] "column=" Name "/> </DefinedValue> 49 <DefinedValue> <columnreference database= "[M                                             Ypetshop] "schema=" [dbo] "table=" [Category] "alias=" [C] "column=" Image "/> 51 </DefinedValue> </DefinedValues> <object D Atabase= "[Mypetshop]" schema= "[dbo]" table= "[Category]" index= "[Pk_category]" alias= "[C]" indexkind= "Clustered"/ > <SeekPredicates> <                                                     Seekpredicatenew> <SeekKeys> 57 <prefix scantype= "EQ" > <rangecol umns> <columnreference database= "[Mypetshop]" schema=                                                         [dbo] "table=" [Category] "alias=" [C] "column=" CategoryId "/> 60                                             </RangeColumns> <RangeExpressions> 62                <scalaroperator scalarstring= "(23794)" > 63 <const constvalue= "(23794)"/> </scalaroper                                                     Ator> </RangeExpressions> 66                                             </Prefix> </SeekKeys> 68 </SeekPredicateNew> </seekpredica                                 Tes> </IndexScan> </RelOp> 72 <relop nodeid= "2" physicalop= "Clustered Index Scan" logicalop= "Clustered Index Scan" Estim Aterows= "1.03803" estimateio= "1.18831" estimatecpu= "0.0983419" avgrowsize= "one" estimatedtotalsubtreecost= "1.28665" Tablecardinality= "89259" ParallEl= "0" estimaterebinds= "0" estimaterewinds= "0" > <outputlist/> 74                                         <indexscan ordered= "0" forcedindex= "0" noexpandhint= "0" > 75 <definedvalues/> <object database= "[Mypetshop]" schema= "[                                         DBO] "table=" [Product] "index=" [pk_product] "alias=" [P] "indexkind=" Clustered "/> 77 <Predicate> <scalaroperator scalarstring= "[Mypetshop]. [dbo]. [Product]. [CategoryId] as [P].                                                     [Categoryid]= (23794) "> <compare compareop=" EQ "> 80                                                         <ScalarOperator> 81 <Identifier> <columnreference database= "[MYPetShop] "schema=" [dbo] "table=" [Product] "alias=" [P] "column=" CategoryId "/> 83                                                     </Identifier> </ScalarOperator> 85                                                         <ScalarOperator> 86 <const constvalue= "(23794)"/> </scalaroperator& Gt </Compare> &LT;/SC Alaroperator> </Predicate> <                         /indexscan> </RelOp> </NestedLoops> 94             </RelOp> </QueryPlan> 97 </StmtSimple> </statEments> 98 </Batch> </batchsequence>100 </ShowPlanXML> 

Well, by now you should realize that rebuilding the execution plan is not easy ... Next we discuss the related situations of reuse, recompilation, and regeneration.

Execution plan for SQL Server