jump to navigation

Worst Performing Queries August 17, 2007

Posted by furrukhbaig in DMV's, Indexes, Performance, SQL Server 2005, TSQL.
Tags: , , , , , ,
trackback

I have been looking for a easy way to find worst performing queries without runing Profiler and I come across very interesting DMV’s dm_exec_query_stats and dm_exec_sql_text.Microsoft has introduced Dynamic Management Views (sys.dm_exec_query_stats, sys.dm_exec_sql_text) in SQL Server 2005 which provide a way to fetch information related to resources used by SQL Server for executing SQL statements.

Following query will return TOP 100 worst performing SQL Statement with the name of object that contain those statements e.g. Stored Procedure, Trigger and Function. Current database context will be used so change the database before executing query. The result will be sorted by CPU time in descending order.

SELECT  TOP 100
            [Object_Name] = object_name(st.objectid),
            creation_time,
            last_execution_time,
            total_cpu_time = total_worker_time / 1000,
            avg_cpu_time = (total_worker_time / execution_count) / 1000,
            min_cpu_time = min_worker_time / 1000,
            max_cpu_time = max_worker_time / 1000,
            last_cpu_time = last_worker_time / 1000,
            total_time_elapsed = total_elapsed_time / 1000 ,
            avg_time_elapsed = (total_elapsed_time / execution_count) / 1000,
            min_time_elapsed = min_elapsed_time / 1000,
            max_time_elapsed = max_elapsed_time / 1000,
            avg_physical_reads = total_physical_reads / execution_count,
            avg_logical_reads = total_logical_reads / execution_count,
            execution_count,
            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 qs
CROSS APPLY
            sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
            Object_Name(st.objectid) IS NOT NULL
            AND st.dbid = DB_ID()
ORDER BY
            db_name(st.dbid),
            total_worker_time / execution_count  DESC
Advertisements

Comments»

1. Clive Richardson - January 15, 2008

This is useful, thanks. One observation… For me, your query would only return results related to the current database context. The ‘AND DB_NAME(st.dbid) = ‘myDB’ has the effect of returning a zero resultset if run against any other db apart from ‘myDB’. Not a big issue but thought I would mention it.

Like

2. Eric - November 14, 2008

I got 0 record returned with this statement. Why?

Like

3. mellerbeck - November 19, 2008

Probably a dumb question but I have been trying to get this to work and other queries like it but keep getting

Server: Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ‘.’.

Any thoughts?

Like

4. furrukhbaig - November 28, 2008

I fix the syntax error in query. it was due to special character for some reason. its fixed now.

Like

5. furrukhbaig - November 28, 2008

You will 0 rows after restarting sql server. Please note all DMVs refreshed after starting database service.

Like

6. Ruben - August 20, 2009

the next error

Server: Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ‘.’.

Is because you are NOT running it over master db, always remember to run any query on master when query is related to DMV’s

Query looks fine but I think need some improvements, because is returning queries that really didn’t take a lot time.

Like

7. Anonymous - November 22, 2011

can you explain the terms that we get in the result set that would be great thank you

Like

furrukhbaig - February 28, 2012

sorry for late reply. most of the terms are self explanatory like logical read , cpu time. which term you want to understand ?
Thanks

Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: