SQL Server - Get queries with same plan

Analyze plan cache for multiple use of plan by same query. Script is part of execution plan analysis series. You can use it to find multiple plans generated by similar queries.

To be able to execute queries, the SQL Server Database Engine must analyze the statement to determine the most efficient way to access the required data. This analysis is handled by a component called the Query Optimizer. The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan or just execution plan.

Results ordered descending by count of unique combination of query hash and T-SQL text.

SELECT 
	COUNT(*) AS [Count], 
	query_stats.query_hash AS [QueryHash], 
    query_stats.statement_text AS [Text]
FROM (
	SELECT 
		QS.*, 
		SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
		(
			(
				CASE statement_end_offset 
					WHEN -1 THEN DATALENGTH(ST.text)
					ELSE QS.statement_end_offset 
				END 
            - QS.statement_start_offset)/2
			) + 1
		) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
) as query_stats
GROUP BY query_stats.query_hash, query_stats.statement_text
ORDER BY 1 DESC

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
CountintCount of cached plans with same query hash and T-SQL text.
QueryHashBinary(8)Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.
Textnvarchar(max)Text of the SQL query. Is NULL for encrypted objects.

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *