Sql-server – SQL Server : Tracking a slow running query on completion

sql serversql-server-2008sql-server-2012

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?

Best Answer

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.

Related Solutions

Sql-server – high ‘total_worker_time’ for stored proc using OPENQUERY in SQL Server 2005

In sys.dm_exec_query_stats total_worker_time is:

Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.

So it is the total CPU time for all executions of the cached plan. The execution_count column gives the number of times the complete plan has executed, and there is also information about the lowest and highest values recorded. The figures from the DMV are in microseconds, whereas STATISTICS TIME output shows milliseconds. Finally, the MAXDOP hint only applies to the portion of the query that runs locally.

I noticed you are running SQL Server 2005 SP2. There were all sorts of timing issues with that (now unsupported) release, for some detail, see:

SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities.

Consider moving to 2005 SP4.

Sql-server – How to find I.O. statistics (by index) for queries in progress

You can see some information about the indexes that are in use right now using the DMV sys.dm_db_index_usage_stats. An example:

declare @dbid int 
select @dbid = db_id() 
select db_name(db_id()) as [database]
, objectname=object_name(s.object_id)
, indexname=i.name
, i.index_id 
, reads=user_seeks + user_scans + user_lookups 
, writes =  user_updates 
from sys.dm_db_index_usage_stats s join sys.indexes i  on i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1 
and s.object_id = i.object_id  and s.database_id = @dbid  and i.index_id <> 1
order by reads

(sample code from MSDN).

Reads and writes columns will grow as soon as the specific index is used for data gathering.

Best Answer

Related Solutions

Sql-server – high ‘total_worker_time’ for stored proc using OPENQUERY in SQL Server 2005

Sql-server – How to find I.O. statistics (by index) for queries in progress

Related Question