Execution Plan Cache Behavior - SQL Server Query Performance Tuning - page 298

Two different plans were created because these are not parameterized queries; they are too complex to be
considered for simple parameterization, and forced parameterization is off. These two plans have identical hash
values because they varied only in terms of the values passed. The differences in case did not matter to the query hash
or the query plan hash value. If, however, you changed the SELECT criteria in queryhash , then the values would be
retrieved from sys.dm_exec_query_stats , as shown in Figure 15-23 , and the query would have changes.
SELECT p.ProductID
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';
Figure 15-23. sys.dm_exec_query_stats showing a different hash
Although the basic structure of the query is the same, the change in the columns returned was enough to change
the query hash value and the query plan hash value.
Because differences in data distribution and indexes can cause the same query to come up with two different plans,
the query_hash can be the same, and the query_plan_hash can be different. To illustrate this, execute two new queries.
SELECT p.[Name],
tha.TransactionDate,
tha.TransactionType,
tha.Quantity,
tha.ActualCost
FROM Production.TransactionHistoryArchive tha
JOIN Production.Product p
ON tha.ProductID = p.ProductID
WHERE p.ProductID = 461;
SELECT p.[Name],
tha.TransactionDate,
tha.TransactionType,
tha.Quantity,
tha.ActualCost
FROM Production.TransactionHistoryArchive tha
JOIN Production.Product p
ON tha.ProductID = p.ProductID
WHERE p.ProductID = 712;
Like the original queries used earlier, these queries vary only by the values passed to the ProductID column. When
both queries are run, you can select data from sys.dm_exec_query_ stats to see the hash values (Figure 15-24 ).