SQL Server – Query Plan – High Compile Times Queries

Background

Nothing original here. Looking for the the more complex SQL Queries in our system.

And, found this nice gem from Jonathan Keyhayias.

Code



/*
	You are here: Home >> Plan Cache >> Identifying High Compile Time Statements from the Plan Cache
	Identifying High Compile Time Statements from the Plan Cache
	-- Find high compile resource plans in the plan cache
*/


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Declare @NumberofRecords int;

set @NumberofRecords = 1E3;

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

	SELECT 
			 QueryHash
				= c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') 

			, QueryPlanHash
				= c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)')

			, [statementID]
				= c.value('(@StatementId)[1]', 'int')

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

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

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

			, queryPlan
				= qp.query_plan

			, planHandle
				= cp.plan_handle

			, [Operation]
				=
				(
					cast(n.c.query('.') as xml)
				)

			, qp.[dbid]

			, qp.[objectid]

			, cp.cacheobjtype

			, cp.objtype

			, [referencedCounts]
				= cp.refcounts

			, [usedCounts]
				= cp.usecounts

			, [existSchemaSys]
				= qp.query_plan.exist('//ColumnReference[@Schema!="[sys]"]')

			, [existSchemaEmpty]
				= qp.query_plan.exist('//ColumnReference[@Schema!=""]')

			, [IsMissingIndex]
		        = 
                 CAST
                    (
					     qp.[query_plan].exist('//MissingIndexes') 
                            AS TINYINT
                    )

	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)


)


, cteQPAggr
as
(

	SELECT 
			   QueryHash
				= max(QueryHash)

			, QueryPlanHash
				= max(QueryPlanHash)

			, [statementID]

			, CompileTime_ms
				= sum(CompileTime_ms)

			, CompileCPU_ms
				= sum(CompileCPU_ms)

			, CompileMemory_KB
				= sum(CompileMemory_KB)

			, queryPlan
				= max(cast(queryPlan as nvarchar(max)))

			, planHandle
				= planHandle

			, [Operation]
				= max(cast([Operation] as nvarchar(max)))

			, [dbid]
				= isNull([dbid], -1)

			, [objectid]
				= isNull([objectid], -1)

			, [cacheobjtype]

			, [objtype]

			, [referencedCounts]
				= sum([referencedCounts])

			, [usedCounts]
				= sum([usedCounts])

			, [existSchemaSys]
				= max(cast([existSchemaSys] as tinyint))

			, [existSchemaEmpty]
				= max(cast([existSchemaEmpty] as tinyint))

			, [IsMissingIndex]
		        = avg(cast(IsMissingIndex as tinyint))

	FROM cteQP

	group by

			  planHandle
			, [statementID]
			, isNull([dbid], -1)
			, isNull([objectid], -1)
			, [cacheobjtype]
			, objtype

)

, cteQueryStat
as
(
	SELECT

			  tblQS.plan_handle

			, tblQS.[sql_handle]

			, [StatementID]
				= ROW_NUMBER() OVER (
										PARTITION BY tblQS.plan_handle
										 ORDER BY tblQS.statement_start_offset
									)

			, tblQS.[execution_count]

			, tblQS.[total_worker_time]

			, tblQS.max_elapsed_time

			, tblQS.max_worker_time

			, tblQS.[total_elapsed_time]

			, tblQS.[statement_start_offset]

			, tblQS.[statement_end_offset]
	
	FROM sys.dm_exec_query_stats tblQS

)
SELECT 
		TOP ( @NumberofRecords)

		  [database] = db_name(qp.[dbid])

		, ObjectName
			 = 
				object_schema_name(qp.[objectid], qp.[dbid])
				+ '.'
				+ object_name(qp.[objectid], qp.[dbid])

		, [cacheobjtype]

		, ObjectType
			= qp.objtype

		, [statementID]
			= qp.[statementID]

		, [planHandle]	
			= qp.planhandle

		, [queryPlan]	
			= cast(qp.queryPlan as xml)

		, [Operation]
			= cast(qp.Operation as xml)

		, qp.[referencedCounts]

		, qp.[usedCounts]

		, executionCounts
			= qs.execution_count

		, qp.CompileTime_ms

		, qp.CompileCPU_ms

		, qp.CompileMemory_KB

		, duration_ms
			= qs.total_elapsed_time/1000

		, cputime_ms
			= qs.total_worker_time/1000

		, avg_duration_ms
			= (qs.total_elapsed_time/qs.execution_count)/1000

		, avg_cputime_ms
			= (qs.total_worker_time/qs.execution_count)/1000 

		, max_duration_ms
			= qs.max_elapsed_time/1000

		, max_cputime_ms
			= qs.max_worker_time/1000 


		, StmtText
			= 
				case objtype

					when 'Adhoc' then sqlTextPH.[text]

					else
							SUBSTRING
							(
									st.[text]
								, (qs.statement_start_offset / 2) + 1
										, (CASE qs.statement_end_offset
												WHEN -1 THEN DATALENGTH(st.text)
												ELSE qs.statement_end_offset
											END - qs.statement_start_offset) / 2 + 1
							) 

				end

		, [IsMissingIndex]

FROM cteQPAggr AS qp

JOIN cteQueryStat AS qs
	--ON qp.QueryHash = qs.query_hash
	ON qp.planhandle = qs.plan_handle
	and qp.statementID = qs.StatementID	

OUTER APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS st

OUTER APPLY sys.dm_exec_sql_text(qp.planHandle) sqlTextPH

WHERE qp.[existSchemaSys] = 1

ORDER BY 

	qp.CompileTime_ms DESC

OPTION(RECOMPILE, MAXDOP 1)


;

Revision

The changes were very, very miniature.

Our target Version is v2005 and unfortunately Query & Plan Fingerprints was not introduced till v2008.

And, so took Jonathan Kehayias nice lead on how to properly align sys.dm_exec_cached_plans & sys.dm_exec_query_plan and sys.dm_exec_query_stats.

Sample Queries

Here are some of the queries identified

SliceBuildingRuleParms


SELECT
  parm,
  value,
  ID
FROM SliceBuildingRuleParms
WHERE sliceType = 'IC'
AND ruleID = 3
AND inout = 'O'
AND ID IN (SELECT
  ID
FROM SliceBuildingRuleParms
WHERE sliceType = 'IC'
AND ruleID = 3
AND parm = 'copy_part_type'
AND value = 'PARAGRAPH'
AND ID IN (SELECT
  ID
FROM SliceBuildingRuleParms
WHERE sliceType = 'IC'
AND ruleID = 3
AND parm = 'copy_part_width'
AND value = 100
AND ID IN (SELECT
  ID
FROM SliceBuildingRuleParms
WHERE sliceType = 'IC'
AND ruleID = 3
AND parm = 'section_name_lines'
AND value = 1
AND ID IN (SELECT
  ID
FROM SliceBuildingRuleParms
WHERE sliceType = 'IC'
AND ruleID = 3
AND parm = 'copy_part_height'
AND value = 'FULL'
AND ID IN (SELECT
  ID
FROM SliceBuildingRuleParms
WHERE sliceType = 'IC'
AND ruleID = 3
AND parm = 'copy_part_length_MIN'
AND CONVERT(int, value) <= 719 AND ID IN (SELECT ID FROM SliceBuildingRuleParms WHERE sliceType = 'IC' AND ruleID = 3 AND parm = 'copy_part_length_MAX' AND CONVERT(int, value) >= 719))))))

 

Dedicated

Again, nothing original here.

Resting fully and solely on Jonathan Kehayias.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s