Query I/O over the Last Five Minutes
When faced with a SQL Server that is performing poorly, a great starting place for troubleshooting is looking at wait stats. Once you’re gathering wait stats, if you see lots of IO-related waits, you may want to look for queries that are consuming excessive physical I/O.
I use the following query to see work time, elapsed time, execution counts, and physical reads for queries that have executed within the past 5 minutes.
SELECT ServerName = @@SERVERNAME
, SQL_text = dest.text
, SQL_Statement = SUBSTRING(
, CASE WHEN deqs.statement_end_offset = -1
END - (deqs.statement_start_offset/2)
, WorkTime = deqs.last_worker_time / 1000
, ElapsedTime = deqs.last_elapsed_time / 1000
, DatabaseName = COALESCE(d.name, N'UNKNOWN')
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) dest
LEFT JOIN sys.databases d ON dest.dbid = d.database_id
WHERE deqs.last_execution_time >= DATEADD(SECOND, -300, GETDATE())
AND dest.text NOT LIKE N'/*EXCLUDEME*/%'
AND (deqs.last_worker_time / 1000) > 100 -- 100 milliseconds minimum duration
ORDER BY deqs.last_physical_reads desc;
As-is, this shows queries performing the highest number of physical reads first. Those queries may be forcing buffer page churn, which in turn may be killing SQL Server performance. The resultset returned by the query includes the execution plan, which you can easily look at just by clicking on it in the “query plan” column. You may be able to refactor query code to avoid unnecessary spills to tempdb, and other costly or unnecessary I/O operations.
For instance, if your front-end is issuing queries with
SELECT *, but only actually consuming a couple of columns from the results, SQL Server will be reading entire rows from disk into memory, then sending them across the network, for no reason at all… refactor the query so the
SELECT clause only contains the columns needed.