SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2

« SQLAuthority News – Milestone of 6 Million Visits – 60 Lak Visits – Search and Job

SQLAuthority News – Download Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 »

SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2

January 23, 2009 by pinaldave

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 (http://blog.SQLAuthority.com)

Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL System Table, SQL Tips and Tricks, T SQL, Technology | 13 Comments

13 Responses

on January 23, 2009 at 2:49 pm | Reply riley

Nice blog with huge information about SQL SERVER. I just found your blog, and really enjoyed reading this post.
on January 23, 2009 at 2:58 pm | Reply Matt

Hello,

Just delete the “,” before the GO command.

Thanks
on January 28, 2009 at 11:13 pm | Reply Sandle Wood

Again one easy to understand and nice blog based on SQL server….
on February 17, 2009 at 9:36 pm | Reply Darshan Shah

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 .
on February 24, 2009 at 10:21 pm | Reply Jerry Hung

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

http://www.databasejournal.com/features/mssql/article.php/3802936/Finding-the-Worst-Performing-T-SQL-Statements-on-an-Instance.htm
on August 21, 2009 at 9:35 pm | Reply Wayne Nelson

I have multiple user databases. How would I modify the query to add a column showing which database the query is executing against?
on August 23, 2009 at 9:39 am | Reply Imran Mohammed

@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.
on November 20, 2009 at 12:30 pm | Reply Bhaskar

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.
on November 20, 2009 at 12:55 pm | Reply bhaskar

Hi,

Please add the hostname with this query.
I am not able to join sysprocess for hostname with this query.
on February 23, 2010 at 1:07 pm | Reply Abhijit

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
on September 1, 2010 at 7:43 pm | Reply Gnana

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…
on March 31, 2011 at 5:48 am | Reply Amir

Hi Pinal,

I think you don’t need to devide by 1000 for FrequencyPerSec
on April 11, 2011 at 9:04 pm | Reply peterb

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;