July 25, 2010

Find the Most Time Consuming Code in your SQL Server Database

This post will demonstrate how to find T-SQL code (SQL Server 2005/2008) that takes the most time to execute. Note that a time consuming code may not necessarily be inefficient; it also depends on the volume of data being processed.

--Top 10 codes that takes maximum time
select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
cross apply
(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))
AS query_text
order by total_elapsed_time desc

query1

--Top 10 codes that takes maximum physical_reads
select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
cross apply
(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))
AS query_text
order by total_physical_reads desc

query2

The sys.dm_exec_query_stats is a Dynamic Management view that gives the statistical information's about cached data. The sys.dm_exec_sql_text is the another view that gives actual text of the sql_handle which is in binary format.

The first query sorts data based on descending order of total_elapsed_time and second query by total_physical_reads.

Similarly also read:

and


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

10 comments:

Pedrero said...

Very good =D

Anonymous said...

I see the message: "sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90

Madhivanan said...

Anonymous,

The method would work from version 2005 onwards. If you use it make sure the compatibility mode is set to 90

Struct said...

Would total_elapsed_time be the right one a frequently called but quickly executing proc could still appear.

I'd have thought max_elapsed_time would be a better candidate ?

Raja Imran Shafique said...
This comment has been removed by the author.
Raja Imran Shafique said...

very nice and excellent post,

www.imran-shafiq.blogspot.com/
www.hyperlinksolutions.net/

doomspace said...

it`s like standart report of sql server "Performance - Top queries by total CPU time"

Renju said...

Please refer the below link also

http://blog.renjucool.com/2009/11/longest-running-procedures-in-sql.html

Shekhar said...

this is quite helpful

iislas said...

thank you very much, this code is very interesting, greetings from Mexico