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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

10 Responses to "Find the Most Time Consuming Code in your SQL Server Database"
  1. Pedrero said...
    July 26, 2010 at 6:02 AM

    Very good =D

  2. Anonymous said...
    July 27, 2010 at 6:43 AM

    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

  3. Madhivanan said...
    July 27, 2010 at 6:47 AM

    Anonymous,

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

  4. Struct said...
    July 29, 2010 at 4:49 AM

    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 ?

  5. Raja Imran Shafique said...
    August 2, 2010 at 11:14 PM
    This comment has been removed by the author.
  6. Raja Imran Shafique said...
    August 2, 2010 at 11:16 PM

    very nice and excellent post,

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

  7. doomspace said...
    August 3, 2010 at 5:58 AM

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

  8. Renju said...
    September 9, 2010 at 1:29 AM

    Please refer the below link also

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

  9. Shekhar said...
    September 10, 2010 at 1:38 AM

    this is quite helpful

  10. iislas said...
    June 14, 2012 at 2:47 PM

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions