PostTest2


Compilation metrics

 

Identifying High compiling time

Compilation time is one of the metrics that we can check to evaluate the complexity of our query. To understand if the timeout limit is fixed, we can query all the queries that have been stopped, and compare the value.

If the compilatin time is high we have different ways to evaluate, but the main could be to simplify the query: removing the functions or views by inlining the function or view logic and produce a much smaller search space for the query optimizer.

Other choice is to break apart large amounts of joins into smaller logical chunks and use temporary tables as a way to mitigate the complexity.

 

-- SAVELOR OK at Statements

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

cp.plan_handle,

QueryPlanHash,

c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,

c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,

c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,

c.value(N'@StatementText[1]', N'nvarchar(max)') AS StmtText,

qp.query_plan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)

 

Skip to main content