SQL Query Usage

Sometimes when trying to find out the cause of a high load on a SQL server, you need to find out what is executing and taking its resources, luckily SQL keeps track of query usage and you can query those statistics.


If you're lucky (or not, depending on your point of view), you might be able to catch these queries in the act, Pinal Dave helped me to do it the first time. This query will show you the currently executing queries.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Elisabeth Redei made my life very easy when she wrote this query, its been with me for quite a while, it will show you the queries using the most resources, you can order by whatever you need to know, and you can uncomment the where code to find specific queries.

--select * from sys.dm_exec_query_stats
SELECT

 (
  total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms]
  , max_elapsed_time/1000 AS [MaxExecTime in ms]
  , min_elapsed_time/1000 AS [MinExecTime in ms]
  , (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
  , qs.execution_count AS NumberOfExecs
  , (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
  , max_logical_reads AS MaxLogicalReads
  , min_logical_reads AS MinLogicalReads
  , max_logical_writes AS MaxLogicalWrites
  , min_logical_writes AS MinLogicalWrites
  , qs.last_execution_time
  ,
   (
    SELECT SUBSTRING(text,statement_start_offset/2,
     (CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2
      ELSE statement_end_offset
     end -statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(sql_handle)
    ) AS query_text

FROM sys.dm_exec_query_stats qs
--where(
--    SELECT SUBSTRING(text,statement_start_offset/2,
--     (CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2
--      ELSE statement_end_offset
--     end -statement_start_offset)/2)
--    FROM sys.dm_exec_sql_text(sql_handle)
--    ) like '%insert%'
ORDER BY [Avg Exec Time in ms] DESC

Read More

Tags: sql, statistics

Dror Gluska April 26, 2011  No comment