4

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?

3
  • 1
    for the exact statement you'd relate the two based on the sql_handle (assuming you've captured that). for similar queries, you'd related the two based on the query_hash.
    – swasheck
    Jul 23, 2015 at 6:07
  • @swasheck thanks! I think this works! +1
    – Jean
    Jul 24, 2015 at 5:05
  • If you can add this as an answer, I can mark it right @swasheck !
    – Jean
    Jul 24, 2015 at 5:24

2 Answers 2

4

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.

2

What I've been using is Adam Machanic's Who_IS_ACTIVE to track the stats for slow running queries. The prod uses the DMV's and will track the stats as mentioned.

One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval.

Please read How to Log Activity Using sp_whoisactive in a Loop

Also, read here on how to use it to find stats on slow running queries.

Apart from DMV's, you can use SQL profiler or Extended events to find the stats on slow running queries and troubleshoot the same as explained in Identifying slow running queries

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.