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:
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT
CONVERT (bigint, stmt_simple.stmt_node.value('(./@StatementEstRows)[1]', 'decimal(28,10)')) AS stmt_est_rows,
stmt_simple.stmt_node.value('(./@StatementOptmLevel)[1]', 'varchar(30)') AS stmt_optimization_level,
stmt_simple.stmt_node.value('(./@StatementOptmEarlyAbortReason)[1]', 'varchar(30)') AS stmt_optimization_early_abort_reason,
stmt_simple.stmt_node.value('(./@StatementSubTreeCost)[1]', 'float') AS stmt_est_subtree_cost,
stmt_simple.stmt_node.value('(./@StatementText)[1]', 'nvarchar(max)') AS stmt_text,
stmt_simple.stmt_node.value('(./@ParameterizedText)[1]', 'nvarchar(max)') AS stmt_parameterized_text,
stmt_simple.stmt_node.value('(./@StatementType)[1]', 'varchar(30)') AS stmt_type,
stmt_simple.stmt_node.value('(./@PlanGuideName)[1]', 'varchar(30)') AS plan_guide_name,
stmt_simple.stmt_node.value('(./sp:QueryPlan/@CachedPlanSize)[1]', 'int') AS plan_size,
stmt_simple.stmt_node.value('(./sp:QueryPlan/@CompileTime)[1]', 'bigint') AS plan_compile_time_ms,
stmt_simple.stmt_node.value('(./sp:QueryPlan/@CompileCPU)[1]', 'bigint') AS plan_compile_cpu,
stmt_simple.stmt_node.value('(./sp:QueryPlan/@CompileMemory)[1]', 'int') AS plan_compile_memory
FROM
sys.dm_exec_cached_plans
CROSS APPLY
sys.dm_exec_query_plan([plan_handle]) AS p
CROSS APPLY
p.query_plan.nodes ('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple') as stmt_simple (stmt_node)
ORDER BY
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!