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
Reference : Pinal Dave (https://blog.sqlauthority.com)
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;