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 in SQL Server Box.

SQL SERVER - Find Most Expensive Queries Using DMV mostexpensivequeries-800x429

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. Please note that this query is valid on any version of SQL Server from SQL Server 2005 or later version of SQL Server. I have been using this query on all of my performance tuning consultation and it works perfectly fine.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Four Posts on Removing the Bookmark Lookup – Key Lookup
Next Post
SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions

Related Posts

55 Comments. Leave new

  • pradip kumar sahoo
    June 25, 2013 12:13 pm

    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

    Reply
    • 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

      Reply
    • Gopakumar N Kurup
      October 12, 2017 10:48 pm

      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

      Reply
  • Mainoddin Khazi
    October 6, 2013 5:53 pm

    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

    Reply
  • hi, how can i clear these counters ?

    Reply
  • Hi All,
    Can we use SPID in the above query because witout spid we can not help application team.

    Thanks in advance

    Reply
  • 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.

    Reply
  • How is this query different from the results generated by the Activity Monitor in SSMS?

    Reply
  • Great Post Thanks…:)!

    Reply
  • do we have any query to find out the most consuming memory with spid

    Reply
  • This is great post thanks. My only question is how to include which object the expensive the queries belongs to?

    Reply
  • 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.

    Reply
  • Gaurav Kumar Chauhan
    December 4, 2014 12:04 pm

    which permissions need to be grant to normal user, to run this query.

    Reply
  • I have seen many queries to take out this information but is there a way to also add information on what user did the query?
    We have a few different systems that uses the same databases and it would be nice to see depending on user or perhaps server name/ip what system is performing the expensive query.

    Possible?

    Reply
    • sys.dm_exec_query_stats doesn’t contain use details because its about the query execution. You need to use your own data collection mechanism to find server/IP/Login running those queries.

      Reply
  • SELECT DB_NAME(st.dbid) DBName
    ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
    ,OBJECT_NAME(objectid,st.dbid) StoredProcedure
    ,max(cp.usecounts) execution_count
    ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO
    ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) avg_total_IO
    ,sum(qs.total_physical_reads) total_physical_reads
    ,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) avg_physical_read
    ,sum(qs.total_logical_reads) total_logical_reads
    ,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) avg_logical_read
    ,sum(qs.total_logical_writes) total_logical_writes
    ,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) avg_logical_writes
    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
    join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
    where DB_NAME(st.dbid) is not null and cp.objtype = ‘proc’
    group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
    order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc

    I have run this got output, i sort the total_IO desc.. so i get some value around 692942307
    milliseconds.

    So how do i check how much of the time is this query taking to execute

    Reply
  • Total total_physical_reads = 0 and total_logical_reads = 514358205, so is it required to fine tune these type of procedures

    Reply
  • I am a student and struggling with how to input this syntax:

    #i19 display only the price of the highest priced item,
    # display should have column header “Max Price” and prefix price with $
    # you must use an aggregate function

    Reply
  • For me, the most interesting statistic is the average duration i.e. total_worker_time/executions. Also, if you store a snapshot of this data at regular intervals, you can the detect when that average jumps by a significant amount, which could alert you to a bad plan.

    Reply
  • jean bulinckx
    June 4, 2019 12:41 am

    You wrote:

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

    Is that right?
    I tried:

    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt

    and looks like return to me more correct results

    Reply

Leave a Reply

Menu