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:
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. |