Homework SourseExplain in a clear wayexamples can be used how the use of an
Solution
Answer
Query plan hash values are provided by the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views which can be used to determine the aggregate resource usage for similar queries and similar query execution plans.
The query hash is a binary hash value which is calculated on the query and is used to identify queries which have the similar logic. The optimizer of queries computes the query hash during query compilation. Queries which may differ only by literal values will be considered to have the same query hash.
The query plan hash is a binary hash value which is calculated on the query execution plan (explained above) and is used to identify those which have similar query execution plans. The optimizer of queries computes the query plan hash at the time of query compilation, using these execution plan values(for example, the logical and physical operators), and a subset of important operator attributes. Query execution plans which may have the same physical and logical operator tree structure, as well as may have identical attribute values for the subset of important operator attributes, will have the same query plan hash.
The differences in the cardinality of query results can cause the query optimizer to choose different query execution plans when queries with identical query hashes are executed against different data and this may result in different query plan hashes.
Performance degradations or improvements can happen when a query is recompiled and the optimizer of query produces a different query execution plan.You can capture, store, and compare query execution plans over time through the use of the query plan hash. Diagnosis of the performance impact of data and configuration changes can be done efficiently when you know which execution plans have changed.
