Query plan hash and query hash

query plan hash and query hash

The new functionality introduced in SQL Server 2008 around execution planning and buffering is known as query plan hash and query hash. This is a binary object that generates binary hash values using an algorithm for a query or query plan.

Query plan hash and query hash can be retrieved from sys.dm_exec_query_stats or sys.dm_exec_requests. Although this is a mechanism for confirming queries and their plans, the hash value is not unique. A non-identical query may derive the same hash, so it cannot be used as a backup primary key.

Create two queries as follows:

SELECT *  fromPersonhunderthousandINNER JOINProvince onPersonhunderthousand.pid=province.idWHEREPersonhunderthousand.name= 'Lang irresolute Thorn' andProvince.name= 'Guangdong'SELECT*  fromPersonhunderthousandINNER JOINProvince onPersonhunderthousand.pid=province.idwherePersonhunderthousand.name= 'Xi laughs' andProvince.name= 'Guangdong'

Two queries only the from and where are not case-insensitive, and the first parameter is different, the design of the inner join and the two conditions are slightly more complex to prevent the simple parameterization of generating the parameter plan, and then execute the following query:

SELECT T.text, S.execution_count,s.query_hash,s.query_plan_hashfrom   Sys.dm_exec_query_stats scrosssys.dm_exec_sql_text(s.plan_handle) t

See the output as follows:

　　

As you can see from the input graph above, two different plans have been created because these queries are not parameterized, they are too complex to consider simple parameterization, and force parameterization to close. These two plans have the same hash value, because their different aspects are just the values passed. The difference in capitalization is irrelevant to query hash or query plan hash.

However, if you change the Select to return only one column:

SELECT  from INNER JOIN  on=  province.idWHERE=' lang irresolute thorn ' 
and =' Guangdong ' 

The query generates a new plan.

Although the basic structure of the query is the same, the changes in the returned column are sufficient to change the query hash and query plan hash value.

Because the data distribution and the differences in the index may cause the same query to draw two different plans, Query_hash may be the same, _query_plan_hash may be different

　　

In addition, sometimes even one parameter is passed in, but because the execution plan for different parameters is different according to the statistics, two plans are created:

SELECT *  from  WHERE=1/--If an incoming parameter generates a different execution plan, a different plan will also be created

Query plan hash and query hash values may be useful tools for tracking common problems between completely different queries, but as you can see, they cannot be given a precise set of information for each possibility. They add another useful tool to identify places where query performance may be low. You can capture a query's query_plan_hash after you deploy the query to the production environment, and then watch to see if it changes due to data changes. From this, you can reference sys.dm_exec_query_stats to track the query state of the collection according to the schedule, but keep in mind that the data for these collections is reset when the server restarts.

Query plan hash and query hash