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
 
Search WWH ::




Custom Search