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

Feeds:: Posts; Comments

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 | 9 Comments

9 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.