Skip to content
Category: SQL Server
2022-11-04

Know the Waiting Stadistics or Waits for SQL Server

Welcome to a new post in the best SQL Server Blog. In today’s post we are going to talk about one of the most valued tools when creating wait statistics in SQL Server: The Waits.

But first, I will explain what Waits are and what their main functions are.

What are the Waits?

SQL Server Wait Statistics is an important tool used to analyze performance related issues or to optimize SQL Server performance. The timeouts are captured and recorded by SQL Server and all that captured information is called Wait Statistics and provides assistance in troubleshooting SQL Server performance related issues.

To know what types of waits we have in the Instance, we will use the following Script:

Ref: https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

IMPORTANT

Wait statistics are one of the most important indicators to identify performance problems in SQL Server. When we want to solve any performance problem, we must first diagnose the problem correctly, for this, we will identify the main WAITS that we can get and how to solve the problem.

SQL SERVER WAITS TYPES

  AASYNC_DISKPOOL_LOCK
ASYNC_IO_COMPLETION
ASYNC_NETWORK_IO
ASYNC_OP_COMPLETION
    B      BACKUP
BACKUP_OPERATOR
BACKUPBUFFER
BACKUPIO
BACKUPTHREAD
BROKER_CONNECTION_RECEIVE_TASK
BROKER_DISPATCHER
BROKER_ENDPOINT_STATE_MUTEX
BROKER_EVENTHANDLER
BROKER_FORWARDER
BROKER_INIT
BROKER_MASTERSTART
BROKER_RECEIVE_WAITFOR
BROKER_REGISTERALLENDPOINTS
BROKER_SERVICE
BROKER_SHUTDOWN
BROKER_TASK_SHUTDOWN
BROKER_TASK_STOP
BROKER_TASK_SUBMIT
BROKER_TRANSMISSION_OBJECT
BROKER_TRANSMISSION_TABLE
BROKER_TRANSMISSION_WORK
BROKER_TRANSMITTER  
          C        CLR_AUTO_EVENT
CLR_MONITOR
CLR_RWLOCK_READER
CLR_RWLOCK_WRITER
CLR_TASK_START
CMEMTHREAD
COMMIT_ACT
COMMIT_TABLE
CXPACKET
 DDAC_INIT
DBMIRROR_DBM_EVENT
DBMIRROR_DBM_MUTEX
DBMIRROR_SEND
DBMIRROR_WORKER_QUEUE
DBMIRRORING_CMD
DBSEEDING_FLOWCONTROL
DBSEEDING_OPERATION
DEADLOCK_ENUM_MUTEX
DEADLOCK_TASK_SEARCH
DIRTY_PAGE_POLL
DISKIO_SUSPEND
DISPATCHER_PRIORITY_QUEUE_SEMAPHORE
DLL_LOADING_MUTEX
DTC
DTC_ABORT_REQUEST
DTC_RESOLVE
DTC_STATE
DTC_TMDOWN_REQUEST
DTC_WAITFOR_OUTCOME
DTCPNTSYNC  
    E    EXCHANGE
EXECSYNC
FFCB_REPLICA_READ
FCB_REPLICA_WRITE  
HHADR_AG_MUTEX
HADR_AR_CRITICAL_SECTION_ENTRY
HADR_AR_MANAGER_MUTEX
HADR_AR_UNLOAD_COMPLETED
HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST
HADR_BACKUP_BULK_LOCK
HADR_BACKUP_QUEUE
HADR_COMPRESSED_CACHE_SYNC
HADR_CONNECTIVITY_INFO
HADR_DATABASE_FLOW_CONTROL
HADR_DATABASE_VERSIONING_STATE
HADR_DATABASE_WAIT_FOR_RESTART
HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
HADR_DB_COMMAND
HADR_DB_OP_COMPLETION_SYNC
HADR_DB_OP_START_SYNC
HADR_DBR_SUBSCRIBER
HADR_DBR_SUBSCRIBER_FILTER_LIST
HADR_DBSEEDING
HADR_DBSEEDING_LIST
HADR_DBSTATECHANGE_SYNC
HADR_FABRIC_CALLBACK
HADR_FILESTREAM_BLOCK_FLUSH
HADR_FILESTREAM_FILE_CLOSE
HADR_FILESTREAM_FILE_REQUEST
HADR_FILESTREAM_IOMGR
HADR_FILESTREAM_MANAGER
HADR_GROUP_COMMIT
HADR_LOGCAPTURE_SYNC
HADR_LOGCAPTURE_WAIT
HADR_LOGPROGRESS_SYNC
HADR_NOTIFICATION_DEQUEUE
HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS
HADR_NOTIFICATION_WORKER_STARTUP_SYNC
HADR_NOTIFICATION_WORKER_TERMINATION_SYNC
HADR_PARTNER_SYNC
HADR_READ_ALL_NETWORKS
HADR_RECOVERY_WAIT_FOR_CONNECTION
HADR_RECOVERY_WAIT_FOR_UNDO
HADR_REPLICAINFO_SYNC
HADR_SYNC_COMMIT
HADR_SYNCHRONIZING_THROTTLE
HADR_TDS_LISTENER_SYNC
HADR_TDS_LISTENER_SYNC_PROCESSING
HADR_TIMER_TASK
HADR_TRANSPORT_DBRLIST
HADR_TRANSPORT_FLOW_CONTROL
HADR_TRANSPORT_SESSION
HADR_WORK_POOL
HADR_WORK_QUEUE
HADR_XRF_STACK_ACCESS  
  I    IO_COMPLETION
LLATCH_DT
LATCH_EX
LATCH_KP
LATCH_NL
LATCH_SH
LATCH_UP
LAZYWRITER_SLEEP
LCK_M_BU
LCK_M_BU_ABORT_BLOCKERS
LCK_M_BU_LOW_PRIORITY
LCK_M_IS
LCK_M_IS_ABORT_BLOCKERS
LCK_M_IS_LOW_PRIORITY
LCK_M_IU
LCK_M_IU_ABORT_BLOCKERS
LCK_M_IU_LOW_PRIORITY
LCK_M_IX
LCK_M_IX_ABORT_BLOCKERS
LCK_M_IX_LOW_PRIORITY
LCK_M_RIn_NL
LCK_M_RIn_NL_ABORT_BLOCKERS
LCK_M_RIn_NL_LOW_PRIORITY
LCK_M_RIn_S
LCK_M_RIn_S_ABORT_BLOCKERS
LCK_M_RIn_S_LOW_PRIORITY
LCK_M_RIn_U
LCK_M_RIn_U_ABORT_BLOCKERS
LCK_M_RIn_U_LOW_PRIORITY
LCK_M_RIn_X
LCK_M_RIn_X_ABORT_BLOCKERS
LCK_M_RIn_X_LOW_PRIORITY
LCK_M_RS_S
LCK_M_RS_S_ABORT_BLOCKERS
LCK_M_RS_S_LOW_PRIORITY
LCK_M_RS_U
LCK_M_RS_U_ABORT_BLOCKERS
LCK_M_RS_U_LOW_PRIORITY
LCK_M_RX_S
LCK_M_RX_S_ABORT_BLOCKERS
LCK_M_RX_S_LOW_PRIORITY
LCK_M_RX_U
LCK_M_RX_U_ABORT_BLOCKERS
LCK_M_RX_U_LOW_PRIORITY
LCK_M_RX_X
LCK_M_RX_X_ABORT_BLOCKERS
LCK_M_RX_X_LOW_PRIORITY
LCK_M_S
LCK_M_S_ABORT_BLOCKERS
LCK_M_S_LOW_PRIORITY
LCK_M_SCH_M
LCK_M_SCH_M_ABORT_BLOCKERS
LCK_M_SCH_M_LOW_PRIORITY
LCK_M_SCH_S
LCK_M_SCH_S_ABORT_BLOCKERS
LCK_M_SCH_S_LOW_PRIORITY
LCK_M_SIU
LCK_M_SIU_ABORT_BLOCKERS
LCK_M_SIU_LOW_PRIORITY
LCK_M_SIX
LCK_M_SIX_ABORT_BLOCKERS
LCK_M_SIX_LOW_PRIORITY
LCK_M_U
LCK_M_U_ABORT_BLOCKERS
LCK_M_U_LOW_PRIORITY
LCK_M_UIX
LCK_M_UIX_ABORT_BLOCKERS
LCK_M_UIX_LOW_PRIORIT
LCK_M_X
LCK_M_X_ABORT_BLOCKERS
LCK_M_X_LOW_PRIORITY
LOGBUFFER
LOGMGR
LOGMGR_FLUSH
LOGMGR_QUEUE
LOGMGR_RESERVE_APPEND    
    M    MSQL_DQ
MSSEARCH
MSSQL_XP
  O  OLEDB
 P  PAGEIOLATCH_DT
PAGEIOLATCH_EX
PAGEIOLATCH_KP
PAGEIOLATCH_NL
PAGEIOLATCH_SH
PAGEIOLATCH_UP
PAGELATCH_DT
PAGELATCH_EX
PAGELATCH_KP
PAGELATCH_NL
PAGELATCH_SH
PAGELATCH_UP
PREEMPTIVE_OS_AUTHENTICATIONOPS
PREEMPTIVE_OS_BACKUPREAD
PREEMPTIVE_OS_CLUSTEROPS
PREEMPTIVE_OS_DEVICEOPS
PREEMPTIVE_OS_DTCOPS
PREEMPTIVE_OS_ENCRYPTMESSAGE
PREEMPTIVE_OS_FILEOPS
PREEMPTIVE_OS_FLUSHFILEBUFFERS
PREEMPTIVE_OS_GENERICOPS
PREEMPTIVE_OS_GETDISKFREESPACE
PREEMPTIVE_OS_GETPROCADDRESS
PREEMPTIVE_OS_LIBRARYOPS
PREEMPTIVE_OS_LOOKUPACCOUNTSID
PREEMPTIVE_OS_PIPEOPS
PREEMPTIVE_OS_QUERYREGISTRY
PREEMPTIVE_OS_SECURITYOPS
PREEMPTIVE_OS_SERVICEOPS
PREEMPTIVE_OS_SQLCLROPS
PREEMPTIVE_OS_WRITEFILEGATHER
  R  REPLICA_WRITES
REQUEST_FOR_DEADLOCK_SEARCH
RESOURCE_QUEUE
RESOURCE_SEMAPHORE
RESOURCE_SEMAPHORE_MUTEX
RESOURCE_SEMAPHORE_QUERY_COMPILE  
          S      SLEEP_TASK
SOS_SCHEDULER_YIELD
SP_SERVER_DIAGNOSTICS_SLEEP
SQLTRACE_BUFFER_FLUSH
SQLTRACE_FILE_BUFFER
SQLTRACE_FILE_READ_IO_COMPLETION
SQLTRACE_FILE_WRITE_IO_COMPLETION
SQLTRACE_INCREMENTAL_FLUSH_SLEEP
SQLTRACE_PENDING_BUFFER_WRITERS
SQLTRACE_SHUTDOWN
        T      TEMPOBJ
THREADPOOL
TRACEWRITE
    W  WAITFOR
WRITE_COMPLETION
WRITELOG    
    X    XACT_OWN_TRANSACTION
XACTLOCKINFO
XE_LIVE_TARGET_TVF

Ref: https://www.sqlshack.com/sql-server-wait-types

Most Common WAITS types and its solution:

1º CXPACKET:

Notice that many execution queries are being parallelized. This does not have to be BAD, but it does consume additional CPU resources, if the processor utilization on the Instance is GENERALLY above 80%, we should investigate.

Solution

  • It examines the queries that consume more CPU to see if they can be Optimized or if they ask for a Missing Index. These queries are clear candidates to be optimized by reviewing their code or creating missing indexes, as this will improve the overall performance of the server.

Here is a link for more information: https://learn.microsoft.com/en-us/troubleshoot/sql/performance/troubleshoot-high-cpu-usage-issues

  • Configure correctly the MAXDOP and parallelism: We must check if the values of Maximum Parallelism Degree and Cost Threshold for parallelism are the most suitable, a quick definition of parallelism would be the maximum number of cores that a query will be able to use.

Here is a link for more information: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16#recommendations

2º BACKUPIO: 

This occurs when a backup job is waiting for data or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount. Note that all backup waits are also accumulated for restore operations.

Solution

  • Activate backup compress if available in the version.
  • Remove unused indexes (less data to back up) ALWAYS BE CAREFUL WITH THIS AND MAKE SURE IT IS NOT APPLICABLE.

 3º SOS_SCHEDULER_YIELD

SQL Server instances with high CPU usage often show the SOS_SCHEDULER_YIELD wait type. This does not mean that the server is underpowered; it means that more investigation is needed to find which individual task in a query needs more CPU time.

Solution

  • Examine the most CPU-consuming queries to see if they can be Optimized or ask us for a Missing Index, for this we will use the previous link provided.
  • We can also find in the following DMV (Dynamic Management Views) how many runnable task counts there are in the system. If we observe a two digit number in runnable_tasks_count continuously for a long time ( not from time to time) you will know that there is CPU pressure. The two-digit number is generally considered a bad thing.

DMV executable task count

SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count, pending_disk_io_countFROM sys.dm_os_schedulersWHERE scheduler_id < 255GO

4º ASYNC_NETWORK_IO: 

There is nothing that can be done here to tune SQL Server performance. As most commonly it could be a network pipe between the application and the SQL Server (such as a long distance wide area network), an underpowered client machine, or row-by-row processing occurring on the application server.

Solution

  • Although we said earlier that as DBAS we can’t do much, we CAN add value to the customer and provide them with information, what we can do to help developers narrow down what is going on, is to run sp_WhoIsActive several times. Look for queries that are waiting on ASYNC_NETWORK_IO, see what the queries are and what machines they are coming from. You may see a pattern of only a couple of queries.

5º WRITELOG:  

This indicates that SQL Server is waiting for writes to the LOG disk to complete. If the value is high, it could indicate a bottleneck on the LOG disk.

Solution

  • Disable unused indexes. This will decrease the number of writes during data modifications. ALWAYS BE CAREFUL WITH THIS AND MAKE SURE IT IS NOT APPLICATIVE.
  • Make sure the FILL FACTOR is set correctly to avoid page splits: The Fill Factor tells us what percentage of the index size will be left free when doing a REBUILD operation. This option is a BEST PRACTICES because leaving enough free space for the internal growth of the index, we will get that all the pages are located in the same sector of the disk so we will avoid fragmentation.

6º PAGEIOLATCH_SH 

This type of wait accumulates while SQL Server waits for a page to be fetched from disk and loaded into memory. The page fetched will be used for one purpose ( read operation). If this value is high, it is likely that the disk or available memory is not up to the workload.

Solution

  • Increase the maximum memory allocation: Currently in the new versions of SQL Server, it already recommends the Max Server Memory, but if this is not the case, the way to calculate it is RAM minus Cores multiplied by 1024.

Another useful tool to calculate this is: https://sqlmax.chuvash.eu/

  • Investigate Disk Latencies: Disk latency provides us with information on reads and writes to SQL Server related disks.

Here is a link for more information: https://theserogroup.com/dba/identifying-sql-server-disk-latency/

  • Indexing, look for high disk I/O queries, examine the most CPU consuming queries to see if they can be Optimized or ask us for any Missing Index. Use the link provided above.
  • Make sure that the tables of these databases are compressed, since compressed data will travel through the network faster, in addition the size of the database on disk will be reduced and backup or maintenance tasks will be performed considerably faster.

Here is a link for more information: https://sqlstarters.com/2017/03/28/sql-server-data-compression/

  • As a last option, add additional memory.

7º LCK_M_S:   

This wait occurs when a request is waiting to acquire a shared lock. This usually occurs when read requests are locked by write transactions (implicit or explicit) that have been held open for long periods of time.

Solution

  • Investigate the possibility of setting the READ_COMMITEDD_SNAPSHOT configuration at the DB level.
  • Examine the most CPU-consuming queries to see if they can be Optimized or ask for a Missing Index. Use the link provided above.

8º OLEDB:

It is common to see this type of wait on instances hosting linked servers. OLEDB is also used in Bulk Insert, the full text engine and within SQL Server Management Studio.

Solution

  • Make sure that the Linked Servers are configured with the Best Practices.

Here is a link for more information: https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/

  • Optimize queries running on Linked Servers.
  • Keep transactions on Linked Servers as short as possible.
  • For bulk upload, make sure that the files are placed in fast storage.

9º BACKUPTHREAD

This wait usually occurs while waiting for some I/O to complete in a thread, such as zero initialization of a data or log file. Note that all backup waits are also accumulated for restore operations.

Solution

  • Enable volume maintenance tasks to avoid zero initialization of data files.
  • Look at the configuration of the DB growth and make sure that the growth is automatic in a fixed number of megabytes instead of %.

10º RESOURCE_SEMAPHORE:

This occurs when memory requests for queries cannot be granted due to other simultaneous queries. High values of this type of wait may indicate an excessive number of simultaneous queries or excessive memory requests.

Solution

  • Make sure that the statistics are up to date. Keep in mind that statistics are one of the most important elements that help the performance of the SQL Server servers. Statistics are the main source of information used by the query optimizer to make decisions on how to resolve a query. With statistics the optimizer can estimate the selectivity of the predicates and thus make decisions about when to use an index or not
  • Execute the following script to find out which queries require an excessive amount of memory:

Queries that use more memory

EXEC dbo.sp_BlitzCache @SortOrder = 'memory grant'
  • Make sure that the MAX Server Memory is properly configured.
  • Finally, if everything is correct, we must allocate more memory to the SQL Server, since it indicates memory pressure.

11º LCK_M_U:  

This wait occurs while an application is waiting to acquire an update lock. An update lock is not just for UPDATE operations. They are used when SQL Server needs to read and then modify a row/page/table. Prior to the modifications, you will place an update lock on the data. Once the system is ready, these locks will be updated to unique locks. This wait usually occurs while the modification requests are locked by other write transactions (implicit or explicit).

Solution

  • Keep transaction durations short or view transactions that are sleeping.
  • Ensure that transaction isolation levels are appropriate (avoid SERIALIZABLE and REPEATABLE READ if possible).
  • Enable if possible the configuration of the databases in READ_COMMITED_SNAPSHOT.
  • Investigate queries for lack of indexation.

And here ends today’s post, we want to recommend the following SQL Server posts:

Learning to organize SQL Server Management Studio for different environments

Discover the main functionalities of SQL Server Always On Cluster

If you have performance problems with your SQL Server, we recommend our SQL Server Health Chek service.

Complete este formulario para recibir la guía de Windows Server en Azure
*Obligatorio
Complete este formulario para recibir la guía de Windows Server en Azure
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir 4 best practices to implement a comprehensive Zero Trust security approach
*Obligatorio
Complete este formulario para recibir 4 best practices to implement a comprehensive Zero Trust security approach
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Cloud Migration Essentials
*Obligatorio
Complete este formulario para recibir Cloud Migration Essentials
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
*Obligatorio
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Prevent data leaks with Microsoft 365 Business Premium
*Obligatorio
Complete este formulario para recibir Prevent data leaks with Microsoft 365 Business Premium
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete this form to recieve the guide of Windows Server on Azure
*Required
Complete this form to recieve the guide of Windows Server on Azure
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve 4 best practices to implement a comprehensive Zero Trust security approach
*Required
Complete this form to recieve 4 best practices to implement a comprehensive Zero Trust security approach
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud Migration Essentials
*Required
Complete this form to recieve Cloud Migration Essentials
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud security Advice for Nonprofit Leaders
*Required
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
*Obligatorio
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud Migration Simplified Ebook.
*Obligatorio
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
Thank you for filling out the form [name]. Here's your Free eBook!