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.
A fast start isn’t all that’s required to win the race!
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/*EXCLUDEME*/ SELECT ServerName = @@SERVERNAME , deqp.query_plan , SQL_text = dest.text , SQL_Statement = SUBSTRING( dest.text , deqs.statement_start_offset/2 , CASE WHEN deqs.statement_end_offset = -1 THEN LEN(dest.text) ELSE (deqs.statement_end_offset/2) END - (deqs.statement_start_offset/2) ) , WorkTime = deqs.last_worker_time / 1000 , ElapsedTime = deqs.last_elapsed_time / 1000 , deqs.execution_count , deqs.last_physical_reads , deqs.last_logical_reads , deqs.last_logical_writes , deqs.last_execution_time , 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.