Mennyi idő alatt állt elő a Query Plan?

Az utóbbi Meetup eseményeken egy kolléga arra volt kíváncsi, hogy mennyi idő alatt áll elő egy query plan. Akkor és ott helyben a választ nem tudtam. Miközben a monitoring riportjaimat készítettem, felmerült ugyan ez a kérdés és kicsit utánanéztem. A plan xml-jében benne van az információ, hurrá :-), ettől egyszerübb nem is lehetne. Ezt és egyéb hasznos infókat az alábbi lekérdezés segítségével ki lehet szedni:

 1;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
 2SELECT
 3    CONVERT (bigint, stmt_simple.stmt_node.value('(./@StatementEstRows)[1]', 'decimal(28,10)')) AS stmt_est_rows,
 4    stmt_simple.stmt_node.value('(./@StatementOptmLevel)[1]', 'varchar(30)') AS stmt_optimization_level,
 5    stmt_simple.stmt_node.value('(./@StatementOptmEarlyAbortReason)[1]', 'varchar(30)') AS stmt_optimization_early_abort_reason,
 6    stmt_simple.stmt_node.value('(./@StatementSubTreeCost)[1]', 'float') AS stmt_est_subtree_cost,
 7    stmt_simple.stmt_node.value('(./@StatementText)[1]', 'nvarchar(max)') AS stmt_text,
 8    stmt_simple.stmt_node.value('(./@ParameterizedText)[1]', 'nvarchar(max)') AS stmt_parameterized_text,
 9    stmt_simple.stmt_node.value('(./@StatementType)[1]', 'varchar(30)') AS stmt_type,
10    stmt_simple.stmt_node.value('(./@PlanGuideName)[1]', 'varchar(30)') AS plan_guide_name,
11    stmt_simple.stmt_node.value('(./sp:QueryPlan/@CachedPlanSize)[1]', 'int') AS plan_size,
12    stmt_simple.stmt_node.value('(./sp:QueryPlan/@CompileTime)[1]', 'bigint') AS plan_compile_time_ms,
13    stmt_simple.stmt_node.value('(./sp:QueryPlan/@CompileCPU)[1]', 'bigint') AS plan_compile_cpu,
14    stmt_simple.stmt_node.value('(./sp:QueryPlan/@CompileMemory)[1]', 'int') AS plan_compile_memory
15FROM
16    sys.dm_exec_cached_plans
17CROSS APPLY
18    sys.dm_exec_query_plan([plan_handle]) AS p
19CROSS APPLY
20        p.query_plan.nodes ('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple') as stmt_simple (stmt_node)
21ORDER BY
22    10 DESC

Az alábbi oszlopokat adja vissza:

• stmt_est_rows: a becsüt sorok száma, amit a lekrédezés vissza fog adni
• stmt_optimization_level: ez a FULL és a TRIVIAL értékeket veheti fel. Az utóbbi azt jelenti, hogy az optimizer szerint egy terv lehet optimális.
• stmt_optimization_early_abort_reason: megmutatja, hogy a terv létrehozása mirét szakadt meg. Ez 3 értéket vehet fel:
• GoodEnoughPlanFound: ez azt jelenti, hogy a terv létrehozási idő maximumán belül sikerült egy elfogadható végrehajtási tervet létrehozni,
• TimeOut: nem sikerült időn belül optimális tervet létrehozni,
• MemoryLimitExceeded: ennél az esetnél kicsit izgulnék ;-). Itt nagy valószínűséggel nincs elegendő memória a szerverbe vagy túls ok memóriát - foglal a plan cache, stb. Ezt ki kell nyomozni.
• stmt_est_subtree_cost: a parancs végrehajtásának várható költsége.
• stmt_text: a végrehajtandó parancs.
• stmt_parameterized_text:
• stmt_type: a végrehajtandó parancs típusa. pl.: SELECT, SELECT INTO, DELETE, INSERT EXEC.
• plan_guide_name: ha plan guide van használatban, akkor annak a neve.
• plan_size: a terv mérete.
• plan_compile_time_ms: a végrehajtási terv előállításához szükséges idő
• plan_compile_cpu: a terv létrehozásakor felhasznált cpu
• plan_compile_memory: a terv létrehozásakor felhasznált memória

A fenti lekérdezés éles rendszeren sokáig tarthat és befolyásolhatja a memória-használatot, illetve a teljesítményt. A lekérdezés lefuttatása csak saját felelősségre történhet!