1

I'm wondering if anyone can shed light on a worry of mine that a query_plan_hash collision could cause a query to be executed as an entirely different query.

The hash is a 16 digit Hex that sp_help sys.dm_exec_query_stats says is a binary. Therefore it's only a 64 bit hash, and a collision seems very probable (considering SHA1 [160 bit] was just verified to have had collisions).

Would the plan_hash and the query_plan_hash both have to collide for this (query to be executed as an entirely different query) to happen?

I'm also curious if there is a setting somewhere in SQL Server that will allow us to change this hash to SHA2-512 (to reduce the possibility of a collision). Our data is very important.

I searched high and low via Google and Stack Exchange forums.

7

Neither query_hash nor query_plan_hash are used to look up query plans for execution, so the exact concern in the question does not arise.

The documentation for sys.dm_exec_query_stats explains that:

  • query_hash is provided to allow users to aggregate resource usage for queries that differ only by literal values (ones that would parameterize to the same form).
  • query_plan_hash is provided to allow users to calculate the cumulative cost of queries with similar execution plans.

Unsurprisingly, the SQL Server developers have gone to great lengths to ensure that cached plans are only reused when it is safe to do so. This goes well beyond the obvious requirement that the cached plan must match the 'query' exactly.

Briefly:

SQL Server has multiple plan cache stores, each implemented as a hash table. A hash value and cache key pair is used to connect a compiled plan with a particular 'query'.

The hash value is (database_id * object_id) mod (hash table size). This determines the hash bucket in a particular cache store. The object_id is known metadata for objects like procedures, functions, and triggers. For ad-hoc SQL, the object_id is a hash of the entire batch text.

The collection of attributes that must match for a cached plan to match a 'query' is known as the cache key. This is used to find and confirm an exact match within the hash bucket. Some (not all) cache key attributes can be found in the documentation for sys.dm_exec_plan_attributes.

Importantly, for ad-hoc SQL, the entire batch text is one of the cache key attributes.


More details can be found in 3.0 How Cache Lookups Work (and other articles in the series) by the Microsoft SQL Programmability & API Development Team.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.