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