Wait statistics

http://www.sqlskills.com/BLOGS/PAUL/category/Performance.aspx

 

Here are 1823 SQL Servers wait state survey result.

 

 

 

• 505: CXPACKET
  • This is commonly where a query is parallelized and the parallel threads are not given equal amounts of work to do. One thread may have a lot more to do than the others, and so the whole query is blocked while the long-running thread completes. If this is combined with a high number of PAGEIOLATCH_XX waits, it could be large parallel table scans going on because of incorrect non-clustered indexes, or out-of-date statistics causing a bad query plan. If neither of these are the issue, you might want to try setting MAXDOP to 1 for the offending queries (or possibly the whole instance).
• 304: PAGEIOLATCH_XX
  • This is where SQL Server is waiting for a data page to be read from disk into memory. It commonly indicates a bottleneck at the IO subsystem level, but could also indicate buffer pool pressure (i.e. not enough memory for the workload).
• 275: ASYNC_NETWORK_IO
  • This is commonly where SQL Server is waiting for a client to finish consuming data. It could be that the client has asked for a very large amount of data or just that it's consuming it reeeeeally slowly because of poor programming.
• 112: WRITELOG
  • This is the log management system waiting for a log flush to disk. It commonly indicates a problem with the IO subsystem where the log is, but on very high-volume systems it could also be caused by waiting for the LOGCACHE_ACCESS spinlock (which you can't do anything about). To be sure it's the IO subsystem, use the DMV sys.dm_io_virtual_file_stats to examine the IO latency for the log file.

 notes: Script to show I/O latency for all database files (the goal is to be less than 10ms.)

SELECT
DB_NAME(database_id) AS ‘Database Name’,
file_id,
io_stall_read_ms / num_of_reads AS ‘Avg Read Transfer/ms’,
io_stall_write_ms / num_of_writes AS ‘Avg Write Transfer/ms’
FROM sys.dm_io_virtual_file_stats(-1, -1)
WHERE num_of_reads > 0
AND num_of_writes > 0

• 109: BROKER_RECEIVE_WAITFOR
  • This is just Service Broker waiting around for new messages to receive. I would add this to the list of waits to filter out and re-run the wait stats query. 
• 086: MSQL_XP
  • This is SQL Server waiting for an extended stored-proc to finish. This could indicate a problem in your XP code. 
• 074: OLEDB
  • As its name suggests, this is a wait for something communicating using OLEDB - e.g. a linked server. It could be that the linked server has a performance issue.
• 054: BACKUPIO
  • This shows up when you're backing up directly to tape, which is slooooow. I'd be tempted to filter this out.
• 041: LCK_M_XX
  • This is simply the thread waiting for a lock to be granted and indicates blocking problems. These could be caused by unwanted lock escalation or bad programming, but could also be from IOs taking a long time causing locks to be held for longer than usual. Look at the resource associated with the lock using the DMV sys.dm_os_waiting_tasks.
• 032: ONDEMAND_TASK_QUEUE
  • This is normal and is part of the background task system (e.g. deferred drop, ghost cleanup).  I would add this to the list of waits to filter out and re-run the wait stats query.
• 031: BACKUPBUFFER
  • This shows up when you're backing up directly to tape, which is slooooow. I'd be tempted to filter this out.
• 027: IO_COMPLETION
  • This is SQL Server waiting for IOs to complete and is a sure indication of IO subsystem problems.
• 024: SOS_SCHEDULER_YIELD
  • If this is a very high percentage of all waits (had to say, but Joe suggests 80%) then this is likely indicative of CPU pressure.
• 022: DBMIRROR_EVENTS_QUEUE
• 022: DBMIRRORING_CMD
  •  These two are database mirroring just sitting around waiting for something to do. I would add these to the list of waits to filter out and re-run the wait stats query.
• 018: PAGELATCH_XX
  • This is contention for access to in-memory copies of pages. The most well-known cases of these are the PFS, SGAM, and GAM contention that can occur in tempdb under certain workloads. To find out what page the contention is on, you'll need to use the DMV sys.dm_os_waiting_tasks to figure out what page the latch is for. For tempdb issues, my friend Robert Davis (blog|twitter) has a good post showing how to do this. Another common cause I've seen is an index hot-spot with concurrent inserts into an index with an identity value key.
• 016: LATCH_XX
  • This is contention for some non-page structure inside SQL Server - so not related to IO or data at all. These can be hard to figure out and you're going to be using the DMV sys.dm_os_latch_stats. More on this in future posts.
• 013: PREEMPTIVE_OS_PIPEOPS
  • This is SQL Server switching to pre-emptive scheduling mode to call out to Windows for something. These were added for 2008 and haven't been documented yet (anywhere) so I don't know exactly what it means.
• 013: THREADPOOL
  • This says that there aren't enough worker threads on the system to satisfy demand. You might consider raising the max worker threads setting.
• 009: BROKER_TRANSMITTER
  • This is just Service Broker waiting around for new messages to send. I would add this to the list of waits to filter out and re-run the wait stats query. 
• 006: SQLTRACE_WAIT_ENTRIES
  • Part of SQL Trace. I would add this to the list of waits to filter out and re-run the wait stats query.
• 005: DBMIRROR_DBM_MUTEX
  •  This one is undocumented and is contention for the send buffer that database mirroring shares between all the mirroring sessions on a server. It could indicate that you've got too many mirroring sessions.
• 005: RESOURCE_SEMAPHORE
  • This is queries waiting for execution memory (the memory used to process the query operators - like a sort). This could be memory pressure or a very high concurrent workload. 
• 003: PREEMPTIVE_OS_AUTHENTICATIONOPS
• 003: PREEMPTIVE_OS_GENERICOPS
  • These are SQL Server switching to pre-emptive scheduling mode to call out to Windows for something. These were added for 2008 and haven't been documented yet (anywhere) so I don't know exactly what it means.
• 003: SLEEP_BPOOL_FLUSH
  • This is normal to see and indicates that checkpoint is throttling itself to avoid overloading the IO subsystem. I would add this to the list of waits to filter out and re-run the wait stats query.
• 002: MSQL_DQ
  • This is SQL Server waiting for a distributed query to finish. This could indicate a problem with the distributed query, or it could just be normal.
• 002: RESOURCE_SEMAPHORE_QUERY_COMPILE
  • When there are too many concurrent query compilations going on, SQL Server will throttle them. I don't remember the threshold, but this can indicate excessive recompilation, or maybe single-use plans.
• 001: DAC_INIT
  • I've never seen this one before and BOL says it's because the dedicated admin connection is initializing. I can't see how this is the most common wait on someone's system... 
• 001: MSSEARCH
  • This is normal to see for full-text operations.  If this is the highest wait, it could mean your system is spending most of its time doing full-text queries. You might want to consider adding this to the filter list. 
• 001: PREEMPTIVE_OS_FILEOPS
• 001: PREEMPTIVE_OS_LIBRARYOPS
• 001: PREEMPTIVE_OS_LOOKUPACCOUNTSID
• 001: PREEMPTIVE_OS_QUERYREGISTRY
  • These are SQL Server switching to pre-emptive scheduling mode to call out to Windows for something. These were added for 2008 and haven't been documented yet (anywhere) so I don't know exactly what it means. 
• 001: SQLTRACE_LOCK
  • Part of SQL Trace. I would add this to the list of waits to filter out and re-run the wait stats query.