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
Script results screen:
Script results detailed description:
|Column name||Data type||Description|
|Count||int||Count of cached plans with same query hash and T-SQL text.|
|QueryHash||Binary(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.|
|Text||nvarchar(max)||Text of the SQL query. Is NULL for encrypted objects.|