April 5, 2020 at 9:38 pm
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];
Likes to play Chess
April 5, 2020 at 9:55 pm
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)
April 8, 2020 at 5:28 am
Tried... cannot do it.
Can't find what to link query hash to,
Likes to play Chess
April 8, 2020 at 2:49 pm
If you could give me a further hint, I would appreciate.
Likes to play Chess
April 8, 2020 at 3:06 pm
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
April 9, 2020 at 12:36 pm
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.
Likes to play Chess
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply