Friday, June 3, 2011

Worst Physical Reads - SQL 2005 / SQL 2008


We are seeing large average disk queue lengths (200+) on one of our non-clustered SQL2005 servers.  We used this to identify the query that is the cause of the high IO.

SELECT TOP 10
  creation_time,last_execution_time,total_physical_reads
  ,total_logical_reads,total_logical_writes, execution_count
  ,total_worker_time, total_elapsed_time, total_elapsed_time / execution_count
  avg_elapsed_time, SUBSTRING(sqlText.text, (qs.statement_start_offset/2) + 1,
  ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(sqlText.text) ELSE
  qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS SQL_Statement
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) sqlText
ORDER BY total_physical_reads DESC

No comments:

Post a Comment