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

hfsqlsce 于 2023-05-21 发布在 SQL Server

关注(0)|答案(1)|浏览(142)

The bounty expires in 3 days. Answers to this question are eligible for a +100 reputation bounty. Masoud is looking for a canonical answer.

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

sql-server

来源：https://stackoverflow.com/questions/49669544/how-to-find-which-program-or-user-executed-query-using-query-store-in-sql-server

1条答案

按热度按时间

y1aodyip1#

The query_hash column in the sys . query_store_query view may return NULL values if the query text has not been hashed yet. This can happen for queries executed before Query Store was enabled.

To find the details of who executed a particular query in Query Store, you can use the sys . query_store_runtime_stats view. This view provides information about the execution statistics of individual query plans:

SELECT qst.program_name,
       qst.host_name,
       qst.*
FROM sys.query_store_runtime_stats qst
JOIN sys.query_store_plan qsp ON qst.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%your_query_text%'
ORDER BY qst.last_execution_time DESC;

赞(0）回复(0）举报 2023-05-21

我来回答

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

1条答案

相关问题

热门标签

最新问答