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 how many plans generated by similar queries.
 
SQL Server must analyze the statement before its execution. This leads to determine the most efficient way to access the required data. Component called Query Optimizer handle this analysis . Analysis input consists of the query, schema (table and index definitions) and statistics. Output generated is a query execution plan, referred to as a query plan or execution plan.
 
Result 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 *