Just another day I was playing with my query which I posted earlier SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL and I found that I got error devide by zero. I have fixed this error in following query as well I have updated query to return time in millisecond instead of microsecond. Jerry Hung has also posted similar solution in comments of original article.
I strongly suggest to read original article to now more about introduction and learn about DBCC command which clears cache.
SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC GO
How to Read the Longest Running Query Results
This DMV only knows about plans that are still in the plan cache. When SQL Server restarts, when memory pressure pushes plans out, or when someone clears the cache, the numbers start again from zero. So a query that ran for an hour last week may not show up at all today. The creation_time column tells you since when a row has been collecting data.
Also watch the units. The DMV stores time in microseconds. The average column in this script is divided by 1000 so it shows milliseconds, but MaxElapsedTime is still in microseconds. Divide it by 1000 as well if you want to compare the two side by side. Mixed units are an easy way to fool yourself.
Elapsed time is not the same as CPU time. A query can run for a long time because it waits on locks, disk or the network while using very little CPU. Add total_worker_time to the list to see how much CPU the query really used. If elapsed time is high and worker time is low, look for blocking or waits before you rewrite the query.
One more tip: the text column shows the whole batch or procedure, not just the slow statement. For long procedures, use statement_start_offset and statement_end_offset to cut out the exact statement.
Run this during normal business hours and again after a busy night. The two lists together tell you much more than one snapshot. If a query shows up in both, it is the first one I would tune.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
19 Comments. Leave new
Nice blog with huge information about SQL SERVER. I just found your blog, and really enjoyed reading this post.
Hello,
Just delete the “,” before the GO command.
Thanks
Again one easy to understand and nice blog based on SQL server….
This is for 2005 but what to do for sql server 2000?
I think we can use SQL profiler for that ,we can also check the using activity monitor,sysprocess,sp_locks , sp_who2 .
Oops, don’t even know my name was mentioned, but thanks Pinal
Just want to share, came over this article 02/20 “usp_Worst_TSQL”
It’s a powerful script to find worst performing queries, with parameters
I have multiple user databases. How would I modify the query to add a column showing which database the query is executing against?
@Wayne,
Check This.
SELECT DISTINCT TOP 10
db_name(dbid) DatabaseName, — Added
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
I dont know why database name is returned NULL some times… Check this throughly before you use it…
~ IM.
Hi ,
Is there any method to get the host name with this query?
I am not able to connect this with sysprocesses to get the hostname.
Please help.
Hi,
Please add the hostname with this query.
I am not able to join sysprocess for hostname with this query.
I guess this query will also return the long running query. You can also add the filters to see the long running transactions.
SELECT DATEDIFF(MINUTE, a.last_batch, GETDATE()) RunningTime,
a.spid, a.blocked, a.waittime, db_name(a.dbid) As dbname,
a.last_batch, a.hostname, a.program_name,
a.nt_username, a.loginame, b.text as sqlstatement
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) B
WHERE A.dbid > 4 —-to filter system databases
ORDER BY RunningTime DESC
GO
Hi Pinal,
we are getting “insufficient system memory to run this query” error. It’s a SQL 2k5 sp2 on Intel E5520 series machine. Please advise on this error…
Hi Pinal,
I think you don’t need to devide by 1000 for FrequencyPerSec
My understanding is that sys.dm_exec_query_stats is for archived query statistics. I’ve been using the following to monitor active long running queries and it works fine for single statements – however it seems to return hugely inflated total_elapsed_time values with some procs or functions where multiple statements are run. I’m also interested if anyone know how to find the query completion times from DMVs?
THANKS
select …..
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
INNER JOIN sys.dm_exec_sessions AS es
ON es.session_id = er.session_id
INNER JOIN sys.dm_exec_connections AS ec
ON ec.session_id = es.session_id
where er.total_elapsed_time / 1000.0 > @ElapsedThreshold
ORDER BY er.total_elapsed_time DESC;
@Imran,
in SQL Reference said: “dbid – smallint – ID of database. Is NULL for ad hoc and prepared SQL statements.”
Meanwhile, there is an example in REF: “SELECT TOP 5 creation_time, last_execution_time, …” – nearly the same as above.
I get
Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ‘.’.
on this line: CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
When running on SQL2005 servers that are hosting SQL2000 compatible DB’s.
Is there a way to resolve that?
Change (s.sql_handle) to (sql_handle)
Hi,
Tried the above query but i got the same error message as David Clary. I tried Sahun’s suggestion but got this error message instead.
“SQL_ handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure your database compatibility mode is set to 90.
Any help is appreciated.
thanks
Since I was interested in only actual data and not what happened a month ago i inserted a date filter. Also my stored procedures names did not appear (column QueryName was null). So here my script version.
For the initial script I give three virtual footkisses to Dave and one for Mohammed.
DECLARE @Now DATETIME
SET @Now = GETDATE()
SET @Now = DATEADD(DAY, -1, @Now)
SELECT DISTINCT TOP 10
db_name(t.dbid) DatabaseName,
OBJECT_NAME(t.objectid) AS ObjectName,
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
WHERE s.creation_time > @Now
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
Regards
BenS
The following code has been optimized to spool to a text output (I use it in an email).
SELECT
‘SESSION_ID: ‘ + CAST(es.[session_id] AS VARCHAR(4)) + ‘ ‘ +
‘ HOST_NAME: ‘ + es.[host_name] + ‘ ‘ +
‘ PROGRAM_NAME: ‘ + es.[program_name], ‘ ‘ + CHAR(13) + CHAR(10),
‘ LOGIN_NAME: ‘ + es.[login_name] + ‘ ‘ +
‘ PROCESS_OWNER: ‘ + CASE es.[is_user_process]
WHEN 1 THEN ‘User’
ELSE ‘System’ END, ‘ ‘ + CHAR(13) + CHAR(10),
‘ TRANSACTION_START_TIME: ‘ + CAST(tat.[transaction_begin_time] AS VARCHAR) + ‘ ‘ +
‘ LAST_READ_TIME: ‘ + CAST(ec.[last_read] AS VARCHAR) + ‘ ‘ +
‘ LAST_WRITE_TIME: ‘ + CAST(ec.[last_write] AS VARCHAR) + ‘ ‘ +
‘ SESSION_STATUS: ‘ + es.[status], ‘ ‘ + CHAR(13) + CHAR(10),
‘ TRANSACTION_STATE: ‘ + CASE tat.[transaction_state]
WHEN 0 THEN ‘The transaction has not been completely initialized yet.’
WHEN 1 THEN ‘The transaction has been initialized but has not started.’
WHEN 2 THEN ‘The transaction is active.’
WHEN 3 THEN ‘The transaction has ended. This is used for read-only transactions.’
WHEN 4 THEN ‘The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.’
WHEN 5 THEN ‘The transaction is in a prepared state and waiting resolution.’
WHEN 6 THEN ‘The transaction has been committed.’
WHEN 7 THEN ‘The transaction is being rolled back.’
WHEN 8 THEN ‘The transaction has been rolled back.’ END + ‘ ‘ +
‘ TRANSACTION_TYPE: ‘ + CASE CAST(tat.[transaction_type] AS VARCHAR)
WHEN ‘1’ THEN ‘Read/Write’
WHEN ‘2’ THEN ‘Read-only’
WHEN ‘3’ THEN ‘System’ END , ‘ ‘ + CHAR(13) + CHAR(10),
‘ SQL_TEXT: ‘ + est.text, ‘ ‘ + CHAR(13) + CHAR(10) + ‘ ‘ + CHAR(13) + CHAR(10)
FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_tran_session_transactions tst
ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON es.session_id = tst.session_id
INNER JOIN sys.dm_exec_connections ec
ON ec.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) est
ORDER BY tat.[transaction_begin_time] ASC;