59

Using Sql Server 2005 Profiler, what events, columns, and filters do you trace to find your slowest queries and stored procedures?

Slow = greater than N seconds, 10 for sake of argument.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • Define "slow". While a query may be slow, this is only relevant to the number of calls made and whether those are critical or not. – Lucero May 04 '09 at 13:59

3 Answers3

111

In SQL 2005 you can use management views to find slow running queries. A good script i found a while ago on SQL server performance will help get you started; it lists data with the slowest performing first.

SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
u07ch
  • 13,324
  • 5
  • 42
  • 48
  • 1
    do you know the unit of measure on the times: total_worker_time, total_elapsed_time, and avg_elapsed_time? – KM. May 04 '09 at 14:18
  • 6
    The times are in microseconds; MSDN has a good breakdown on the management view http://msdn.microsoft.com/en-us/library/ms189741.aspx – u07ch May 04 '09 at 14:37
  • 1
    Yes, those DMV are great - but they are DYNAMIC, too - as their name implies, e.g. they get flushed each time the server starts up. If your server gets rebooted every night, these might not represent a very reliable sample on any given day. So treat those measurements with care - they're dynamic, and might be based on a fairly small sample... – marc_s May 04 '09 at 16:42
  • How would I add a column to this query to give me the stored procedure name? – Hades Nov 29 '12 at 00:00
  • 1
    @Hades for procedures you are better starting from sys.dm_exec_procedure_stats – u07ch Nov 30 '12 at 18:16
  • this gives me `user does not have permissions to execute this action`. which roles should I have to run this query? – chester89 Aug 26 '16 at 10:49
  • To get time in seconds, divide by 1000000.0, e.g. `(total_worker_time / 1000000.0) AS total_worker_time_s` – humbads Dec 28 '16 at 20:01
  • @u07ch is it possible to add database name information, i mean is it possbile to see which query runs slow on which database? – Sheshman May 26 '21 at 14:10
15

Before I use the profiler, I check the built-in usage reports. Right click a database, Reports, Standard Reports, then Object Execution Statistics.

It lists the currently cached execution plans, along with the amount of resources and the number of times they've been run. This generally gives a very good idea about what's keeping the server busy.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    @Andomar, Is ok to run that report on a production server? I tried running that report on one of my production servers, and it still said "retreiving data" after one minute. I stopped it to be safe. – Bill Paetzke May 06 '10 at 01:16
  • 1
    @Bill Paetzke: The report should be safe for production even if it runs quite long. If you don't trust it, you can check the process list for blocking issues! – Andomar May 06 '10 at 12:40
4

The duration column does it for me, but sometimes I look at the reads and writes columns too.

I use the TSQL:StmtCompleted filter to get the raw queries. You may want to add others like stored procedures to that, but the tsql is the 'base' you need to view. As the MSDN article says

"The execution of a stored procedure can be monitored by the SP:Starting, SP:StmtStarting, SP:StmtCompleted, and SP:Completed event classes and all the TSQL event classes."

gbjbaanb
  • 51,617
  • 12
  • 104
  • 148