Finding and Tuning Similar Queries by Using Query and Query Plan Hashes

When searching for resource-intensive queries, you should consider how to find and tune similar queries that collectively consume significant system resources. The sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views provide query hash and query plan hash values that you can use to help determine the aggregate resource usage for similar queries and similar query execution plans.

This topic defines the query hash and query plan hash, gives examples of using the hash values to find the cumulative cost of similar queries and execution plans, and offers ways to enhance performance for similar queries and execution plans.

The query hash is a binary hash value calculated on the query and used to identify queries with similar logic. The query optimizer computes the query hash during query compilation. Queries that differ only by literal values have the same query hash. For example, the following two queries have the same query hash because they differ only by the literal values assigned to FirstName and LastName.

The query plan hash is a binary hash value calculated on the query execution plan and used to identify similar query execution plans. The query optimizer computes the query plan hash during query compilation, using execution plan values such as the logical and physical operators, and a subset of important operator attributes. Query execution plans that have the same physical and logical operator tree structure, as well as identical attribute values for the subset of important operator attributes, will have the same query plan hash.

When queries with identical query hashes are executed against different data, the differences in the cardinality of query results can cause the query optimizer to choose different query execution plans, resulting in different query plan hashes.

The following example shows how two similar queries can have the same query hash, but might not have the same query execution plan. There are two ways to view the hash values: the final SELECT statement and the Showplan XML, where they are listed in the StmtSimple element as attribute values for QueryHash and QueryPlanHash.

If the cardinality estimates for ProductID = 3 are high, the query optimizer might use the index scan operator in the query plan. If the cardinality estimates for ProductID = 1 are low, the query optimizer might use the index seek operator.

Non-Uniqueness for Hash Values

It is possible to have hash collisions in which dissimilar queries and query plans have the same hash value. Although the probability of hash collisions is very small and unlikely to occur, applications that depend on the uniqueness of the query hash and query plan hash can have errors because of duplicate hash values. For example, the query hash and query plan hash should not be used as a primary key or in a unique column.

The following example returns information about the top five queries according to average CPU time. This example aggregates the queries according to their query hash so that logically equivalent queries are grouped by their cumulative resource consumption.

The following example returns information about the top five query plans according to average CPU time. This example aggregates the queries according to their query plan hash so that queries with the same query plan hash are grouped by their cumulative resource consumption.

USE AdventureWorks2008R2;
GO
SELECT TOP 5 query_plan_hash AS "Query Plan Hash",
SUM(total_worker_time)/SUM(execution_count) AS "Avg CPU Time",
MIN(CAST(query_plan as varchar(max))) AS "ShowPlan XML"
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_query_plan(QS.plan_handle)
GROUP BY query_plan_hash
ORDER BY 2 DESC;
GO

Track and Investigate Execution Plan Changes

Performance degradations or improvements can occur when a query is recompiled and the query optimizer produces a different query execution plan. Using the query plan hash, you can capture, store, and compare query execution plans over time. Knowing which execution plans have changed can help you diagnose the performance impact of data and configuration changes.

For example, after changing the system configuration, you can compare query plan hash values for mission-critical queries to their original query plan hash values. Differences in query plan hash values can tell you if the system configuration change resulted in updated query execution plans for important queries. You might also decide to stop execution for a current long-running query if its query plan hash in sys.dm_exec_requests differs from its baseline query plan hash, which is known to have good performance.

Parameterize Similar Queries to Improve Cached Plan Reuse

If a set of queries has the same query hash and query plan hash, you might improve performance by creating one parameterized query. Calling one query with parameters instead of multiple queries with literal values allows reuse of the cached query execution plan. For more information on the benefits of reusing cached query plans, see Execution Plan Caching and Reuse.