Asked by:

Query Store

Question

I have a question regarding the new feature of sql server is called "Query Store". The question is,i figured out there are many different query_id for same query_hash. I can understand one query_hash can have more than one query_plan_hash but i
would see all of them should bind to same query_id since query_hash is same. Can you help me to understand the logic ?

A query hash is used to identify queries that have similar logic. Queries that differ only by literal values have the same query hash. The query store is likely identifying different queries that have the same query hash. This could
explain the different query_id, which the query store uses as a primary key.

But query_id is in sys.query_store_query dmv not in sys.query_store_plan. So one query_hash can have many query_plan_hash,which i can understand. But i did not understand that i see different record in sys..query_store_query (means different query_id's)
for same query_hash and query_text.

I think that this is what is happening: considering the two queries below:

SELECT col1 FROM tbl WHERE col2 = 42

SELECT col1 FROM tbl WHERE col2 = 666

Two two above if sent un-parameterized to SQL Server will have different query ids. But SQL server (as of some version, perhaps 2014) will assist in finding what are essentially the same query nowadays also generate a query hash, where the search value is
essentially paraeterized before the hash is calculated. I.e.,, two "different queries", but same query hash. Below is a script I show at my perf classes showinf this. Note that three the queries (the two rows) has the same query hash, although there
ate two different sql_handles. I don't have the time to dig into the Query Store aspect of this, but I have a feeling that this holds your answer to the mystery:

I know for the adhoc statement,if the statement is not safe sql does not parametrize it,and any change in the text can cause different plan and it is evaluated in the query store with different query_id. But in my case,i run the query with sp_executesql
and it is completly parametrized and there is no change except paramaters. Text is completly same. So it is not the reason text change since it is parametrized query.

Query Store creates a query_text_id for each new query text it sees (exact match).

A "query" to query store is a semantic interpretation of that query text. It is influenced by the various set options (ansi nulls on/off). So, you get different query ids based on having the same query text used in different modes.
The default connection mode for different tools differs (unfortunately), so this is actually not uncommon. So, this is one possible reason you can have different query ids for the same hash - if the set options (context settings) are not included in
the structural hash, it will not be in the query hash.

Path two to get a different query id for the same query hash is if you have differences in spaces for the query text. The texts are semantically equivalent but are not exactly the same as far as the compilation/caching behavior in SQL.