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:

;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!

Comments (3) -

Kovács Zsolt 12/14/2012 5:56:42 PM

Csak érdekesség képpen. Nálunk volt egy query, aminek a végrehajtási terve 20 perc alatt készült el Smile

Kovács Zsolt 2/5/2018 3:18:25 PM

Kipróbáltam a query-t, eredmény:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

Ezzel a sorral volt a gond:
CONVERT (bigint, stmt_simple.stmt_node.value('(./@StatementEstRows)[1]', 'decimal(28,10)')) AS stmt_est_rows,

Ez kellett helyette:
CONVERT (bigint, stmt_simple.stmt_node.value('(./@StatementEstRows)[1]', 'float')) AS stmt_est_rows,

Ha esetleg valaki belefutna még Smile

János Berke 2/6/2018 4:54:22 PM

Köszi Smile

Add comment