SQL SERVER – Find Most Expensive Queries Using DMV

The title of this post is what I can express here for this quick blog post. I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online. This basic script does do the job which I expect to do – find out the most expensive queries on SQL Server Box.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

You can change the ORDER BY clause to order this table with different parameters. I invite my reader to share their scripts.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

48 thoughts on “SQL SERVER – Find Most Expensive Queries Using DMV

  1. One of the things to keep in mind with this query is that this gives statistics for cached query plans only. If you had a query which uses a lot of resource but is not cached, then this query won’t be able to show you this query.

    Like

      • Morning Pinal,

        i hava a doubt .. how the memory allcation will be in creation of table in sql server…

        create table test
        (
        name varchar(10)–
        )

        means in sql server the memory allocation will be like bit or bytes…

        if i declared name varchar(10) the memory allocation will be based on the seze or byte wise

        Plz give me earlier responce..

        Regards
        Suresh..

        Like

  2. The other thing to keep in mind for this is that on today’s larger servers, the plan cache can be many GB’s in size, so while you are only doing a TOP 10, this can still be a fairly expensive query to execute.

    Like

  3. Here is what the scripts I have…this gives the original source of the query which helps in making any changes to query

    –Queries taking longest elapsed time:
    SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
    (CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
    where qt.dbid = DB_ID()
    ORDER BY average_seconds DESC;

    –Queries doing most I/O:

    SELECT TOP 10
    (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
    (total_logical_reads + total_logical_writes) AS total_IO,
    qs.execution_count AS execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
    (CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS indivudual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
    where qt.dbid = DB_ID()
    ORDER BY average_IO DESC;

    Like

  4. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

    • Dear Sir / Madam

      I have problem in sql server 2008

      (1) I am using service brokar for messaging purpose , my application every three second read message ( binary to xml ) and send to web client this method lot of resource use , then any alternative method available in sql server ?

      (2) in statics update lot of _wa_sys file this is effect performance or not ? if performance effect then delete or not ?
      Please tell soluation as soon as possible …

      Regards

      Like

  5. I ran the script on a database but the result was empty, i ran again in another database and works why is that? the statistics are enabled

    Like

    • update bcz it has to overwrite the data.If any index is on table then it has to modifi that and if that index is clusterd index then that index arrange itself in assinding order.

      Like

  6. Rohit: It works on 2005. If you cant make it work, check the database compatibility. There are a lot of good query DMV Scripts (search for DMV Allstars)

    Vick: Since 2000 doesnt have DMVs, go for the ol’ fashioned SQL Profiler or automated scripts that select and record the sysprocesses / DBCC NPUTBUFFER output.

    Like

  7. Does running these DMVs in itself cause any performance overhead. My DBA is of the opinion that running these queries in itself cause an overhead. Can anyone please provide me concrete data/proof that these DMVs are not resource intensive

    Like

  8. Pingback: SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 – Video « SQL Server Journey with SQL Authority

    • The second argument is the starting place. Using the statement start offset and statement end offset, we have to divide by two because the offset is in bytes and the text is Unicode. In this argument only, we must add 1 because the statement_start_offset and statement_end_offset are 0 based whereas the substring function is 1 based. This is correct in this function.

      Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

  10. Dear Pinal,
    can u suggest any way to find out ” if any body has access any particular storedprocedure” wheather by application or any other way

    Like

  11. create function findexpf
    (
    @eno int
    )
    returns @t int
    as
    begin
    declare @t int
    select @t=DATEDIFF(dd,hiredate,getdate())/365 from emp where EMPNO=@eno
    return @t
    end

    * can u plz correct it

    Like

    • Please find the corrected one below….

      create function findexpf
      (
      @eno int
      )
      returns int
      as
      begin
      declare @t int
      select @t=DATEDIFF(dd,hiredate,getdate())/365 from emp where EMPNO=@eno
      return @t
      end

      ====================
      Thanks…
      Gopal Reddy N R

      Like

  12. Hi Pinal
    this script realy helpful for finding most expensive query Thank you lot for helping us but here spid will not be displyed in any column so again its difficult in large environment which spid is eating more cpu memory can you please suggest me on this

    Like

  13. Others think the habit is more likely connected to their checking the ground
    for thhe scent of its enemies, since the dog has iits
    nose to the ground during the turning around.
    Go ahead. Doog ear are is imperative to making suire your ddog does not suffer needlessly.

    Like

  14. Great Post. My only question is there a way to add object the expensive query belongs to? Without this information, I need to search through whole database to find the where that query coming from.

    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