Query Store


  • Hi guys,

    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 ? 



    Thursday, February 01, 2018 3:02 PM

All replies

  • How is your question related to Query store. Which DMV exactly you are talking about. Your question is too localized



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles


    Thursday, February 01, 2018 3:23 PM
  • 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.


    Thursday, February 01, 2018 5:24 PM
  • query_hash is on the source, query_id is on the plan. 

    I think you might get the same query_plan_hash for different query_ids, that are simply generated at a different time.

    (don't have it here to look at)



    Thursday, February 01, 2018 6:44 PM
  • Sorry but i did not understand well. 

    In what situation same query_text and query hash generates new query_id in sys.query_store_query dmv ? 

    Thursday, February 01, 2018 8:46 PM
  • Hi Josh,

    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.

    Thursday, February 01, 2018 8:50 PM
  • Let's say you run the same query all the time.

    But because of the pattern of updates (or something), it runs plan A every morning and plan B every evening.

    The query_hash is always the same.

    The query_plan_hash is either A or B.

    The query_id is incremented every time the plan changes.


    Friday, February 02, 2018 1:02 AM
  • No this is not correct. Plan change does not create new query_id.
    Friday, February 02, 2018 7:46 AM
  • Guys, 

    Any idea ? 

    Friday, February 02, 2018 9:36 AM
  • 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:

    SELECT p.FirstName, p.LastName, p.Title FROM Person.Person AS p WHERE p.FirstName = 'Joe'
    SELECT p.FirstName, p.LastName, p.Title FROM Person.Person AS p WHERE p.FirstName = 'Lisa'
    SELECT p.FirstName, p.LastName, p.Title FROM Person.Person AS p WHERE p.FirstName = 'Lisa'
    SELECT sql_handle, plan_handle, execution_count, query_hash, query_plan_hash   
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) AS sql
    WHERE sql.text LIKE '%FROM Person.Person%'

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, February 02, 2018 9:44 AM
  • Hi Tibor,

    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.

    Friday, February 02, 2018 1:03 PM
  • No this is not correct. Plan change does not create new query_id.

    I mean "change" from A to B to A to B, any new generation for any reason, even rebooting the system.  It's just an identity value.


    Friday, February 02, 2018 3:57 PM
  • any ideas ? 
    Tuesday, February 06, 2018 12:41 PM
  • Is is possible to create a repro on this? It would be easier for us if we could see this with our own eyes and play with it....

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, February 06, 2018 12:46 PM
  • Hello Tibor,

    Since it is a customer production enviroment,i can not share all details,but please check screenshot,you will see different query id for same query text id and query hash.

    Tuesday, February 06, 2018 12:54 PM
  • any ideas ? 
    Thursday, February 08, 2018 1:12 PM
  • It would be great understanding the behavior of query store in this case :)
    Monday, February 12, 2018 12:19 PM