SQL : Common Wait Types

NETWORKIO

Waiting on network I/O completion. Waiting to read or write to a client on the network. Check NIC saturation.

LCK_x

Check for memory pressure, which causes more physical I/O, thus prolonging the duration of transactions and locks. View the following counter: Lock Wait Time (ms)

CXPACKET

On OLTP (On-line Transaction Processing) systems, if CXPACKET wait-types account for more than 5% of total locks SQL CPU parallelism can cause performance degradation. On Data WH systems if CXPACKET waits are 10% or greater then it is likely that parallelism is the cause of the performance issues.

Look to reduce parallelism or even disable it on OLTP systems. OLTP systems best practice is to disable it completely.

I/O_COMPLETION

Indicates an I/O bottleneck. Identify disk bottlenecks, using IoStallMS values and Perfmon counters:

• PhysicalDisk: Disk Sec/read, Disk Sec/write, Disk Queues
• SQL Server Buffer Manager: Page Life Expectancy, Checkpoint Pages/sec, Lazy Writes/sec

·         SQL Server Access Methods for correct indexing: Full Scans/sec, Index Searches/sec

• See the Memory: Page Faults/sec

Identify IoStallMS values using the following query:

/* Find DB ID */

SELECT DB_ID(‘SQLTraceDB’) as [Database ID]

/* Display IoStallMS */

SELECT * FROM ::fn_virtualfilestats(7,-1)

EXCHANGE

Check for parallelism using sp_configure ‘max degree of parallelism’. Disable parallelism by setting 1 or change to # of CPU’s /2.

ASYNC_I/O_COMPLETION

Waiting for asynchronous I/O requests to complete. Identify disk bottlenecks, using IoStallMS values and Perfmon Counetrs:

• PhysicalDisk: Disk Sec/read, Disk Sec/write, Disk Queues
• SQL Server Buffer Manager: Page Life Expectancy, Checkpoint Pages/sec, Lazy Writes/sec

·         SQL Server Access Methods for correct indexing: Full Scans/sec, Index Searches/sec

• See the Memory: Page Faults/sec

Identify IoStallMS values using the following query:

/* Find DB ID */

SELECT DB_ID(‘SQLTraceDB’) as [Database ID]

/* Display IoStallMS */

SELECT * FROM ::fn_virtualfilestats(7,-1)

Full Scans/Sec
This counter should always be captured. It shows how often a table index is not being used and results in sequential I/O. This is defined as the number of unrestricted full scans. These can be either base table or full index scans. Missing or incorrect indexes can result in reduced performance because of too high disk access.

Page Life Expectancy

According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer pool flushes your pages in less than 300 seconds, you probably have a memory problem.

WAITFOR

Check common SQL Stored Procedures for WAIT FOR DELAY statements. This is a fixed delay within a stored procedure.

PAGELATCH_x

Usually indicates cache contention issues

PAGEIOLATCH_x

Usually indicates I/O issues. Check disk subsystem.

OLEDB

See Disk secs/Read and Disk secs/Write. If Disk secs/Read is high, add additional I/O bandwidth, balance I/O across other drives.

To get the Transact-SQL statement involved in OLEDB waits:

DECLARE @Handle binary(20)

SELECT @Handle = sql_handle FROM sysprocesses

WHERE waittype = 0x0042

SELECT * FROM ::fn_get_sql(@Handle)