Database Reference
In-Depth Information
Query Plan Hash and Query Hash
With SQL Server 2008, new functionality around execution plans and the cache was introduced called the
query plan
hash
and the
query hash.
These are binary objects using an algorithm against the query or the query plan to generate
the binary hash value. These are useful for a common practice in developing known as
copy and paste.
You will find
that common patterns and practices will be repeated throughout your code. Under the best circumstances, this is
a good thing because you will see the best types of queries, joins, set-based operations, and so on, copied from one
procedure to another as needed. But sometimes, you will see the worst possible practices repeated over and over
again in your code. This is where the query hash and the query plan hash come into play to help you out.
You can retrieve the query plan hash and the query hash from
sys.dm_exec_query_stats
or
sys.dm_exec_requests
.
Although this is a mechanism for identifying queries and their plans, the hash values are not unique. Dissimilar
plans can arrive at the same hash, so you can't rely on this as an alternate primary key.
To see the hash values in action, create two queries.
SELECT *
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory AS pc
ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE pc.[Name] = 'Bikes'
AND ps.[Name] = 'Touring Bikes';
SELECT *
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory AS pc
ON ps.ProductCategoryID = pc.ProductCategoryID
where pc.[Name] = 'Bikes'
and ps.[Name] = 'Road Bikes';
Note that the only substantial difference between the two queries is that
ProductSubcategory.Name
is different,
with
Touring Bikes
in one and
Road Bikes
in the other. However, also note that the
WHERE
and
AND
keywords in the
second query are lowercase. After you execute each of these queries, you can see the results of these format changes
from
sys.dm_exec_query_stats
in Figure
15-22
from the following query:
SELECT deqs.execution_count,
deqs.query_hash,
deqs.query_plan_hash,
dest.text
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
WHERE dest.text LIKE 'SELECT *
FROM Production.Product AS p%';
Figure 15-22.
sys.dm_exec_query_stats showing the plan hash values