SQL Server : Tracking a slow running query on completion Am trying to work on script for slow-running queries. I understand that we can get the list of slow running queries using sys.dm_exec_requests. Say, I have a query Q1 which is running very slow, say, 1 hour. Till Q1 is running i.e. executing, I can track it using sys.dm_exec_requests DMV. If I understood it right, once Q1 is completed, its statistics will be no more available in this DMV. Instead, we need to look at sys.dm_exec_query_stats. But how do I get the statistics of that specific query Q1 which had been slow-running ? Meaning how do I relate the data I earlier got in sys.dm_exec_requests and sys.dm_exec_query_stats?
A quick-and-dirty way to get this information is to run something like this query while the query you wish to observe is still running.

select 
    st.text,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    qs.creation_time,
    qs.*
from sys.dm_exec_query_stats qs 
join sys.dm_exec_requests r
    on r.sql_handle = qs.sql_handle
cross apply sys.dm_exec_sql_text(qs.sql_handle) st


Note: this will only capture queries that are running at that moment. Additionally, the query stats DMV does not persist across reboots and may also be flushed when under pressure. Joining on sql_handle gets you query stats for that exact statement. You can find query stats for "similar" queries (similar logic with difference occurring only in literal values) by joining on query_hash.