-- Just a few examples of simple queries that can be used to view SQL Server waits -- Usable for SQL 2005 / 2008 -- By Albert - v. 0.1 -- (1) View IO statistics on all database files note: (dbid, fileid): SELECT * FROM fn_virtualfilestats(null, null) -- (2) View IO statistics on all database files of a specific database (dbid, fileid): SELECT * FROM fn_virtualfilestats(dbid, null) -- e.g. like dbid=5: SELECT * FROM fn_virtualfilestats(5, null) -- (3) View the top 10 wait statistics: -- All select top 10 * from sys.dm_os_wait_stats order by wait_time_ms desc -- All except for well known wait_types due to system processes select top 10 * from sys.dm_os_wait_stats where wait_type not in ( 'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP', 'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER', 'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT', 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' ) order by wait_time_ms desc -- (4) View Signal Waits (cpu) and Resource Waits (other like IO, locks etc..): SELECT signal_wait_time_ms=sum(signal_wait_time_ms),'%signal (cpu) waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)), resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms),'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)) FROM sys.dm_os_wait_stats -- (5) Most cpu consuming statements: select top 10 object_name(st.objectid), db_name(st.dbid), total_worker_time/execution_count AS AverageCPUTime, CASE statement_end_offset WHEN -1 THEN st.text ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2) END AS StatementText from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY AverageCPUTime DESC -- (6) Top Queries and stored procedures that have been recompiled (plan_generation_num): select top 20 sql_text.text, sql_handle, plan_generation_num, execution_count, db_name(dbid), object_name(objectid) from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num >1 order by plan_generation_num desc