Category: SQL Server

by Vicente Gil 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

A	ASYNC_DISKPOOL_LOCK ASYNC_IO_COMPLETION ASYNC_NETWORK_IO ASYNC_OP_COMPLETION
B	BACKUP BACKUP_OPERATOR BACKUPBUFFER BACKUPIO BACKUPTHREAD BROKER_CONNECTION_RECEIVE_TASK B ROKER_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
D	DAC_INIT DBMIRROR_DBM_EVENT DBMIRROR_DBM_MUTEX DBMIRROR_SEND DBMIRROR_WORKER_QUEUE DBMIRRORING_CMD DBSEEDING_FLOWCONTROL DBSEEDING_OPERATION DEADLOCK_ENUM_MUTEX DEADLOCK_TASK_SEARCH DI RTY_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
F	FCB_REPLICA_READ FCB_REPLICA_WRITE
H	HA DR_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
L	LATCH_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_SC H _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 LO GBUFFER LOGMGR LOGMGR_FLUSH LOGMGR_QUEUE LOGMGR_RESERVE_APPEND
M	MSQL_DQ MSSEARCH MSSQ L_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.chu vash.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.

Know More

Vicente Gil

Project Manager at Aleson ITC, Passionate about constantly developing my skills and growing professionally, I love technology and analysing big data to make strategic decisions.