How to find which Program or User executed Query using Query Store in SQL Server 2016+

Question

After enabling query store, how to find who executed the query. For example, in case of trace collection, there is TRC file which will get the hostname and program details for query and in case of Extended-Events, we have XEL file which will get the hostname and program details. We tried code

SELECT des.program_name,
des.host_name,
*
FROM sys.query_store_query_text qt -- Query Text
JOIN sys.query_store_query q ON qt.query_text_id = q.query_id -- Query Data
JOIN sys.query_store_plan qp on qp.query_id = q.query_id --  Query Plan
join sys.dm_exec_requests der on der.query_hash = q.query_hash -- Get session id for query
join sys.dm_exec_sessions des on des.session_id = der.session_id -- Session Info
order by q.last_execution_time desc

Below DMV return null values for Query Hash (query_hash) hence no data for above query

select * from sys.dm_exec_requests der
select * from sys.dm_exec_sessions des

Nester

Query store contains aggregated information - the same query plan can have been executed by many users so there is no such information held there. – Boltrope

Answer 1

The sys.query_store_query_text view should contain the text of the tracked queries if the Query Store is properly enabled and configured.

SELECT name, is_query_store_on 
FROM sys.databases 

The Query Store in SQL Server keeps track of the queries, their plans, and runtime statistics, but it does not maintain a record of who executed those queries.

Query Store has its own system views which are different from sys.dm_exec_requests and sys.dm_exec_sessions DMVs. It does not directly relate to the session-level details that you are looking for.

Your query tries to join Query Store views with session-level views (sys.dm_exec_requests, sys.dm_exec_sessions) based on query_hash. The query_hash field in sys.dm_exec_requests is not persisted and is only available for currently executing requests. Hence, if there's no currently executing request that matches the query_hash, it will return null.

Also, sys.query_store_query.query_id is not the same as sys.query_store_query_text.query_text_id, therefore the join ON qt.query_text_id = q.query_id might not give you the expected results.

If you need to track who is executing what query, when, and from where, you may need to use Extended Events or SQL Server Audit which can provide you with this level of information.

Please note that tracking every query executed on your SQL Server might cause performance degradation due to the increased overhead of logging every query and their details.

For now, you might want to update your join condition to match query_text_id properly, something like:

SELECT des.program_name,
des.host_name,
*
FROM sys.query_store_query_text qt -- Query Text
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id -- Query Data
JOIN sys.query_store_plan qp on qp.query_id = q.query_id --  Query Plan
join sys.dm_exec_requests der on der.query_hash = q.query_hash -- Get session id for query
join sys.dm_exec_sessions des on des.session_id = der.session_id -- Session Info
order by q.last_execution_time desc

Lingo

Answer 2

Query store is designed to hold aggregate information about the queries in your system. As such, it doesn't capture individual information such as an individual user executing a query, or an application running the query. While you could, through the hash values (not the ID values, they are different between the system catalog views of Query Store and the performance DMVs), tie the information stored in query store to actively executing information within the DMVs, it is a somewhat inconsitent view into the data (hash values can duplicate, they get dropped from cache, etc.).

The information you're after is execution context and therefore needs to be captured by something that understands that, such as Extended Events or the DMVs. You just won't get it from Query Store because that individual execution context doesn't exist there.

Hesiod