Most Common Wait Types

ASYNC_NETWORK_IO -- slow consuming by application, not SQL server issue, try to increase the Packet size with network team help

BACKUP_IO -- Backups are running slow; it might be due to storage issue.

CXPACKET --When you pass in a query, SQL Server will

consider parallelizing it across more cores – up to your Maximum Degree of

Parallelism

LCK* -- It means one query is holding locks on an

object, and another query is waiting to get locks on that same

object. Maybe one query is trying to update all the rows or inserting in

the table, and another query is trying to read them.

PAGEIOLATCH_* -- SQL Server is waiting to read data

pages from storage

SOS_SCHEDULER_YIELD -- The SQL Operating System (SOS,

get it?) is waiting for a CPU scheduler to yield more time, it means that

an individual task in a query needs more CPU time.

WRITELOG -- Users are doing delete/update/insert

(DUI) operations, your transaction log storage is having a tough time keeping

up (You can increase auto growth settings to fix write log wait type)

RESOURCE_SEMAPHORE -- can cause due to order by ,

hash joins and so on

Resolution:

create the indexes as required. sort operator may be

an issue. create an index on it to track index creation status use -- SET

STATISTICS PROFILE ON and run the below query

Summary: -

PAGEIOLATCH,IO_COMPLETION,`ASYNC_IO_COMPLETION,WRITELOG

Description: -

"Wait types that indicate I/O (Input/Output)

pressure in SQL Server typically involve situations where the server is

experiencing delays due to slow or busy I/O operations. These wait types often

suggest that SQL Server is waiting for I/O operations to complete before

proceeding with further processing. Some of the common wait types that indicate

I/O pressure include:

1. `PAGEIOLATCH_XXX`: These wait types indicate

latching on data pages while waiting for I/O operations to read or write data

from/to disk. Examples include:

 - `PAGEIOLATCH_SH`: Shared latch for reading data pages from disk.

 - `PAGEIOLATCH_EX`: Exclusive latch for writing data pages to disk.

 - `PAGEIOLATCH_UP`: Update latch for updating 

data pages on disk.

2. `IO_COMPLETION`: This wait type indicates that an

I/O operation has completed, and SQL Server is waiting for the completion

notification.

3. `ASYNC_IO_COMPLETION`: Similar to `IO_COMPLETION`,

this waits type indicates the completion of an asynchronous I/O operation.

4. `WRITELOG`: This wait type indicates that SQL

Server is waiting for log write operations to complete before proceeding.

5. `BACKUPIO`: This wait type occurs during backup

operations when SQL Server is waiting for I/O to read data for the backup.

6. `BACKUPBUFFER`: This wait type indicates that SQL

Server is waiting to allocate or write backup buffers to disk.

7. `FCB_REPLICA_WRITE`: This wait type occurs during

Always on Availability Group replication when SQL Server is waiting to write

data to the replica's disk.