Query performance metrics via Query Store - how can I get a SESSION ID?

  • VoldemarG

    Hall of Fame

    Points: 3640

    In the below resultset the info is beautiful and useful. However, if I want to know SPID /Session ID , for example, to link a particular query in the resultset to , say, a persisted resultset of Sp_WhoiSActive, or other .... -- how can I add a session ID to the below query resultset?

    There is no field in any of the below QUERY STORE DMVs to retrieve session id. Please, advice.

    SELECT

    .[query_id], [qt].[query_sql_text],

    SUM([rs].[count_executions]) [ExecutionCount], AVG([rs].[avg_duration])/1000 [AvgDuration_ms],

    AVG([rs].[avg_cpu_time]) [AvgCPU], AVG([rs].[avg_logical_io_reads]) [AvgLogicalReads],

    AVG([rs].[avg_physical_io_reads]) [AvgPhysicalReads]

    FROM [sys].[query_store_query]

    JOIN [sys].[query_store_query_text] [qt]

    ON

    .[query_text_id] = [qt].[query_text_id]

    LEFT OUTER JOIN [sys].[objects] [o]

    ON

    .[object_id] = [o].[object_id]

    JOIN [sys].[query_store_plan] [p]

    ON

    .[query_id] = [p].[query_id]

    JOIN [sys].[query_store_runtime_stats] [rs]

    ON [p].[plan_id] = [rs].[plan_id]

    WHERE [qt].[query_sql_text] LIKE '%INSERT%'

    OR [qt].[query_sql_text] LIKE '%ALTER%'

    GROUP BY

    .[query_id],

    .[object_id], [o].[name], [qt].[query_sql_text], [rs].[plan_id]

    ORDER BY

    .[query_id];

    Voldemar likes to play CHESS (and IS good at it!)

  • frederico_fonseca

    SSChampion

    Points: 14651

    the tables you have contain a query hash - with that you can get to the other DMV's that may or not contain a session ID - there may not be any active session with that query.

    this should be enough for you to dig through the other DMV's and try and match it yourself (or google as there are a few examples for it)

  • VoldemarG

    Hall of Fame

    Points: 3640

    Tried... cannot do it.

    Can't find what to link query hash to,

    Voldemar likes to play CHESS (and IS good at it!)

  • VoldemarG

    Hall of Fame

    Points: 3640

    If you could give me a further hint, I would appreciate.

    Voldemar likes to play CHESS (and IS good at it!)

  • frederico_fonseca

    SSChampion

    Points: 14651

    so you are telling us that you went through the documentation on over 160 dmv views and you didn't find any other view that has a query hash column?

    even a google finds them

     

  • VoldemarG

    Hall of Fame

    Points: 3640

    Well. I found query hash only in 2 DMVs:

    * dm_exec_query_stats

    * dm_exec_requests.

    From the below definition, I am concluding that a query (every query trackable by Query Store) may or may not have Process ID associated with it, and can have MULTIPLE SPIDs that run query with same hash value, and also one process ID can run many of same  or different queries. Many-to-Many kind of thing.  From that sense, it makes no sense to link each QS query to SPID.

    Is my understanding correct?

    query_hash

    Binary(8)

    Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.

    Voldemar likes to play CHESS (and IS good at it!)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply