tim laqua dot com Thoughts and Code from Tim Laqua

15May/090

Quick Analysis of Cached Query Plans in SQL Server 2005

I run this one pretty frequently when we need to figure out what procs are killing a complex ETL process and what exactly about them is making the server cry. So basically, if it's on the development server, I'll do a DBCC FREEPROCCACHE and a DBCC DROPCLEANBUFFERS, run the entire set of ETLs, then run this query and then dig deeper in to the query plans that look suspect (high *Scan counts usually, sometimes lots of Hash Matches or Merge Joins). On a production server, the clearing of the proc cache and dropcleanbuffers can be problematic so I'll often just run the query after a scheduled ETL run. If you want to see the query plans mapped out visually, click on the query_plan value and SSMS will open up the XML. Then save that XML file as a .sqlplan file. Once you have that, close the XML and then open the .sqlplan file.

SELECT
	 DatabaseName
	,ObjectName
	,CAST(query_plan.value('
		declare namespace QP="http://schemas.microsoft.com/sqlserver/2004/07/showplan";   
		sum((/QP:ShowPlanXML/QP:BatchSequence/QP:Batch/QP:Statements/QP:StmtSimple/QP:QueryPlan/@CompileTime))','NVARCHAR(MAX)') AS FLOAT)/1000.0/60.0 AS [CompileTime]
	,query_plan
	,query_plan.value('
		declare namespace QP="http://schemas.microsoft.com/sqlserver/2004/07/showplan";   
		count((//QP:ColumnsWithNoStatistics))','INT') AS [ColumnsWithNoStatistics]
	,query_plan.value('
		declare namespace QP="http://schemas.microsoft.com/sqlserver/2004/07/showplan";   
		count((//QP:TableScan))','INT') AS [TableScan]
	,query_plan.value('
		declare namespace QP="http://schemas.microsoft.com/sqlserver/2004/07/showplan";   
		count((//QP:IndexScan))','INT') AS [IndexScan]
	,query_plan.value('
		declare namespace QP="http://schemas.microsoft.com/sqlserver/2004/07/showplan";   
		count((//QP:RelOp[@PhysicalOp="Nested Loops"]))','INT') AS [NestedLoop]
	,query_plan.value('
		declare namespace QP="http://schemas.microsoft.com/sqlserver/2004/07/showplan";   
		count((//QP:RelOp[@PhysicalOp="Hash Match"]))','INT') AS [HashMatch]
	,query_plan.value('
		declare namespace QP="http://schemas.microsoft.com/sqlserver/2004/07/showplan";   
		count((//QP:RelOp[@PhysicalOp="Merge Join"]))','INT') AS [MergeJoin]
FROM 
	(	
		SELECT
			OBJECT_NAME(st.objectid,st.dbid) as ObjectName,
			DB_NAME(st.dbid) as DatabaseName,
			st.dbid,
			st.objectid,
			qs.plan_handle
		FROM 
			sys.dm_exec_query_stats as qs
			CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
		WHERE
			ObjectId IS NOT NULL
		GROUP BY
			st.dbid,
			st.objectid,
			qs.plan_handle
	) a
	CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE DatabaseName = 'NameOfDatabase'
ORDER BY DatabaseName, ObjectName
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.