Query plan hash and query hash

Query plan hash and query hash

The new function introduced in SQL Server 2008 is called query plan hash and query hash. This is a binary object that uses algorithms for queries or query plans to generate binary hash values.

You can retrieve the hash and query hash of a query plan from SYS. dm_exec_query_stats or SYS. dm_exec_requests. Although this is a mechanism to confirm the query and its plan, the hash value is not unique. Similar queries may produce the same hash, so they cannot be used as the backup primary key.

Create two queries respectively as follows:

Select * From personhunderthousand inner join provinceon personhunderthousand. PID = province. idwhere personhunderthousand. Name = 'lang ance' and province. Name = 'guangdong 'select *FromPersonhunderthousand inner join province on personhunderthousand. PID = province. IDWherePersonhunderthousand. Name ='XI Xiao'And province. Name = 'guangdong'

The two queries are only from and where case insensitive and the first parameter is different. They are designed as inner join and the two conditions are slightly more complex to prevent simple parametric generation of 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 sCROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t

The output is as follows:

　　

From the input diagram above, we can see that two different plans are created, because these queries are not parameterized, they are too complex to consider simple parameterization, and forced parameterization is disabled. The two plans have the same hash value, because different aspects of them are only passed values. The case sensitivity difference is irrelevant to hash queries or hash queries.

However, if you modify the SELECT statement to only return one column:

Select personhunderthousand. ID from personhunderthousand inner join province on personhunderthousand. PID = province. idwhere personhunderthousand. Name = 'lang hongst'
And province. Name = 'guangdong'

This query generates a new plan.

Although the basic structure of the query is the same, the modification in the returned column is enough to change the hash value of the query and query plan.

Because different data distributions and indexes may lead to two different plans for the same query, query_hash may be the same, and _ query_plan_hash may be different.

　　

In addition, even if only one parameter is input, two plans are created even if the execution plan of different parameters is different based on statistics:

Select * From persontenthousand where id = 1/100 -- if the input parameters generate different execution plans, different plans will also be created.

Query plan hash and query hash values may be useful tools for common problems between Different queries, but as you can see, they cannot obtain a set of precise information in each possibility. They provide another useful tool to identify areas where query performance may be low. You can capture the query_plan_hash query After deploying the query to the production environment, and then observe it at any time to see if it is changed due to data changes. As a result, SYS. dm_exec_query_stats can be referenced to track the query status of the set as planned, but remember that the data of these sets will be reset when the server is restarted.

 

Query plan hash and query hash