Database Reference
In-Depth Information
Figure 15-24.
Differences in the query_plan_hash
You can see the
queryhash
values are identical, but the
query_plan_hash
values are different. This is because
the execution plans created, based on the statistics for the values passed in, are radically different, as you can see
in Figure
15-25
.
Figure 15-25.
Different parameters result in radically different plans
The query plan hash and the query hash values can be useful tools for tracking down common issues between
disparate queries, but as you've seen, they're not going to retrieve an accurate set of information in every possibility.
They do add yet another useful tool in identifying other places where query performance could be poor. They can
also be used to track execution plans over time. You can capture the
query_plan_hash
for a query after deploying it
to production and then watch it over time to see whether it changes because of data changes. With this you can also
keep track of aggregated query stats by plan, referencing
sys.dm_exec_ querystats
, although remember that the
aggregated data is reset when the server is restarted or the plan cache is cleared in any way. Keep these tools in mind
while tuning your queries.
