Microsoft SQL Server 2005
Performance Tuning
Best Practices

How to monitor slow running queries?

 

Execute the following script in Query Editor to list slow running queries.

USE AdventureWorks;

GO

 

SELECT

      DB = db_name(dbid)

      ,DBObject = object_name(objectid)

      ,Frequency = execution_count

      ,Reads =   total_logical_reads 

      ,AvgElapsedTime = total_elapsed_time / execution_count

      ,Query = LEFT(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), 256)

        ,last_execution_time

        ,total_physical_reads

        ,total_logical_writes

        ,total_worker_time

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

WHERE db_name(dbid)is not NULL

ORDER BY (execution_count * total_logical_reads) DESC;

GO

 

 

 

 

The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page