none
Query Store

    Question

  • 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 ? 

    Thanks,

    Arif

    Thursday, February 1, 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

    Cheers,

    Shashank

    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

    MVP

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

    HTH

    Thursday, February 1, 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)

    Thanks,

    Josh

    Thursday, February 1, 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 1, 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 1, 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.

    Josh

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

    Any idea ? 

    Friday, February 2, 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:

    DBCC FREEPROCCACHE
    GO
    SELECT p.FirstName, p.LastName, p.Title FROM Person.Person AS p WHERE p.FirstName = 'Joe'
    GO
    SELECT p.FirstName, p.LastName, p.Title FROM Person.Person AS p WHERE p.FirstName = 'Lisa'
    GO
    SELECT p.FirstName, p.LastName, p.Title FROM Person.Person AS p WHERE p.FirstName = 'Lisa'
    GO
    
    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 2, 2018 9:44 AM
    Moderator
  • 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 2, 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.

    Josh

    Friday, February 2, 2018 3:57 PM
  • any ideas ? 
    Tuesday, February 6, 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 6, 2018 12:46 PM
    Moderator
  • 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 6, 2018 12:54 PM
  • any ideas ? 
    Thursday, February 8, 2018 1:12 PM
  • It would be great understanding the behavior of query store in this case :)
    Monday, February 12, 2018 12:19 PM
  • 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.

    Hope that helps,

    Conor

    Architect SQL

    Thursday, July 12, 2018 3:57 PM
  • Hi Conor,

    Thanks for answer. Basicly the queries were sent from same application,so i am pretty sure "set options" is same for all queries.

    To be honest i did not understand much what you mean by "if you have differences in spaces".  If you mean database performance tier(i am using sql azure). there is no change in terms of server specs.

    Friday, July 13, 2018 3:54 PM
  • from my experience i know that changing the session's options you have this behaviour.

    i can have a repro: (the last value is set_options , as you see it's different in the two records)

    query_id plan_id query_hash query_sql_text query_text_id query_text_id set_options
    5 5 0xB0B8585A1438A482 (@0 int)select * from moc . dbo . Orders where order_amount > @0 4 4 0x000200FB
    4 4 0xB0B8585A1438A482 (@0 int)select * from moc . dbo . Orders where order_amount > @0 4 4 0x000210FB

    in your case you have a lot of different query _id...by the way as for now the only repro is this:

    let check your set_options with (add the filter you need)

    SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.query_id, Qry.query_text_id,Qry.context_settings_id, CTX.set_options, Qry.*
    FROM sys.query_store_plan AS Pl 
    INNER JOIN sys.query_store_query AS Qry 
        ON Pl.query_id = Qry.query_id 
    INNER JOIN sys.query_store_query_text AS Txt 
        ON Qry.query_text_id = Txt.query_text_id
    INNER JOIN sys.query_context_settings as CTX
     on Qry.context_settings_id = CTX.context_settings_id


    • Edited by baleng Wednesday, September 12, 2018 3:25 PM
    Wednesday, September 12, 2018 3:21 PM