Saturday, November 5, 2011

Wait Stats (Quick Summary)

In SQL 2000 - 

DBCC SQLPERF(waitstats)

In SQL 2005+ 

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms  
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

To clear Wait Stats -

dbcc sqlperf(waitstats,clear)

Some you can safely ignore -

CLR_SEMAPHORE
LAZYWRITER_SLEEP
RESOURCE_QUEUE
SLEEP_TASK
SLEEP_SYSTEMTASK
SQLTRACE_BUFFER_FLUSH
WAITFOR
LOGMGR_QUEUE
CHECKPOINT_QUEUE
REQUEST_FOR_DEADLOCK_SEARCH
XE_TIMER_EVENT
BROKER_TO_FLUSH
BROKER_TASK_STOP
CLR_MANUAL_EVENT
CLR_AUTO_EVENT
DISPATCHER_QUEUE_SEMAPHORE
FT_IFTS_SCHEDULER_IDLE_WAIT
XE_DISPATCHER_WAIT
XE_DISPATCHER_JOIN
BROKER_EVENTHANDLER
TRACEWRITE
FT_IFTSHC_MUTEX
SQLTRACE_INCREMENTAL_FLUSH_SLEEP
SLEEP

No comments: