Information About Waits Stats in SQL Server Waits – Part 5

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Deadlock waits in SQL Server, Waits related to SQL Server Versioning.

DEADLOCK_ENUM_MUTEX
Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.

DEADLOCK_TASK_SEARCH
Large waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX.

DEBUG
Occurs during Transact-SQL and CLR debugging for internal synchronization.

DISABLE_VERSIONING
Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.

ENABLE_VERSIONING
Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.

DISKIO_SUSPEND
Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.

DISPATCHER_QUEUE_SEMAPHORE
Occurs when a thread from the dispatcher pool is waiting for more work to process. The wait time for this wait type is expected to increase when the dispatcher is idle.

DLL_LOADING_MUTEX
Occurs once while waiting for the XML parser DLL to load.

DROPTEMP
Occurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server related to Database Mirroring waits and parallelism waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Database Mirroring waits and parallelism waits.

CMEMTHREAD
Occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.

CXPACKET
Occurs with parallel query plans when trying to synchronize the query processor exchange iterator. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism.

CXROWSET_SYNC
Occurs during a parallel range scan.

DAC_INIT
Occurs while the dedicated administrator connection is initializing.

DBMIRROR_DBM_EVENT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

DBMIRROR_DBM_MUTEX
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
– Although this is provided for informational purposes, this generally happens with database mirroring in both Synchronous and Asynchronous configuration. This some times can correlate with the huge amount of activity on the principal database or due to contention.

DBMIRROR_EVENTS_QUEUE
Occurs when database mirroring waits for events to process.

DBMIRROR_SEND
Occurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.

DBMIRROR_WORKER_QUEUE
Indicates that the database mirroring worker task is waiting for more work.

DBMIRRORING_CMD
Occurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Waits – Part 3

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to CLR waits and Checkpoint waits.

BUILTIN_HASHKEY_MUTEX
May occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.

CHECK_PRINT_RECORD
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

CHECKPOINT_QUEUE
Occurs while the checkpoint task is waiting for the next checkpoint request.

CHKPT
Occurs at server startup to tell the checkpoint thread that it can start.

CLEAR_DB
Occurs during operations that change the state of a database, such as opening or closing a database.

CLR_AUTO_EVENT
Occurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated. Long waits are typical, and do not indicate a problem.

CLR_CRST
Occurs when a task is currently performing CLR execution and is waiting to enter a critical section of the task that is currently being used by another task.

CLR_JOIN
Occurs when a task is currently performing CLR execution and waiting for another task to end. This wait state occurs when there is a join between tasks.

CLR_MANUAL_EVENT
Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.

CLR_MEMORY_SPY
Occurs during a wait on lock acquisition for a data structure that is used to record all virtual memory allocations that come from CLR. The data structure is locked to maintain its integrity if there is parallel access.

CLR_MONITOR
Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.

CLR_RWLOCK_READER
Occurs when a task is currently performing CLR execution and is waiting for a reader lock.

CLR_RWLOCK_WRITER
Occurs when a task is currently performing CLR execution and is waiting for a writer lock.

CLR_SEMAPHORE
Occurs when a task is currently performing CLR execution and is waiting for a semaphore.

CLR_TASK_START
Occurs while waiting for a CLR task to complete startup.

CLRHOST_STATE_ACCESS
Occurs where there is a wait to acquire exclusive access to the CLR-hosting data structures. This wait type occurs while setting up or tearing down the CLR runtime.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Waits – Part 2

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Service Broker waits.

BAD_PAGE_PROCESS
Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.

BROKER_CONNECTION_RECEIVE_TASK
Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.

BROKER_ENDPOINT_STATE_MUTEX
Occurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.

BROKER_EVENTHANDLER
Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.

BROKER_INIT
Occurs when initializing Service Broker in each active database. This should occur infrequently.

BROKER_MASTERSTART
Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.

BROKER_RECEIVE_WAITFOR
Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.

BROKER_REGISTERALLENDPOINTS
Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.

BROKER_SERVICE
Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.

BROKER_SHUTDOWN
Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.

BROKER_TASK_STOP
Occurs when the Service Broker queue task handler tries to shut down the task. The state check is serialized and must be in a running state beforehand.

BROKER_TO_FLUSH
Occurs when the Service Broker lazy flusher flushes the in-memory transmission objects to a work table.

BROKER_TRANSMITTER
Occurs when the Service Broker transmitter is waiting for work.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Waits – Part 1

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Backup, Network and SQL Server Audit related waits.

ABR
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

ASSEMBLY_LOAD
Occurs during exclusive access to assembly loading.

ASYNC_DISKPOOL_LOCK
Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.

ASYNC_IO_COMPLETION
Occurs when a task is waiting for I/Os to finish.

ASYNC_NETWORK_IO
Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

AUDIT_GROUPCACHE_LOCK
Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit each audit action group.

AUDIT_LOGINCACHE_LOCK
Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit login audit action groups.

AUDIT_ON_DEMAND_TARGET_LOCK
Occurs when there is a wait on a lock that is used to ensure single initialization of audit related Extended Event targets.

AUDIT_XE_SESSION_MGR
Occurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.

BACKUP
Occurs when a task is blocked as part of backup processing.

BACKUP_OPERATOR
Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.

BACKUPBUFFER
Occurs when a backup task 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.

BACKUPIO
Occurs when a backup task 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.

BACKUPTHREAD
Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.

PARALLEL_BACKUP_QUEUE
Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Operating System Error 3002 to Operating System Error 10054 for SQL Server

SQLServerF1

As a SQL Server DBA, there are various occasions where SQL Server returns Operating System errors as it causes some failure performing certain SQL Server operations. Below are Operating System Errors from Error 3002 to Operating System Error 10054

The Operating System Error 3002(The spool file was not found.)
ERROR_SPOOL_FILE_NOT_FOUND

The Operating System Error 3003(A StartDocPrinter call was not issued.)
ERROR_SPL_NO_STARTDOC

The Operating System Error 3004(An AddJob call was not issued.)
ERROR_SPL_NO_ADDJOB

The Operating System Error 3005(The specified print processor has already been installed.)
ERROR_PRINT_PROCESSOR_ALREADY_INSTALLED

The Operating System Error 3006(The specified print monitor has already been installed.)
ERROR_PRINT_MONITOR_ALREADY_INSTALLED

The Operating System Error 3007(The specified print monitor does not have the required functions.)
ERROR_INVALID_PRINT_MONITOR

The Operating System Error 3008(The specified print monitor is currently in use.)
ERROR_PRINT_MONITOR_IN_USE

The Operating System Error 3009(The requested operation is not allowed when there are jobs queued tothe printer.)
ERROR_PRINTER_HAS_JOBS_QUEUED

The Operating System Error 3010(The requested operation is successful. Changes will not be effectiveuntil the system is rebooted.)
ERROR_SUCCESS_REBOOT_REQUIRED

The Operating System Error 3011(The requested operation is successful. Changes will not be effectiveuntil the service is restarted.)
ERROR_SUCCESS_RESTART_REQUIRED

The Operating System Error 4000(WINS encountered an error while processing the command.)
ERROR_WINS_INTERNAL

The Operating System Error 4001(The local WINS can not be deleted.)
ERROR_CAN_NOT_DEL_LOCAL_WINS

The Operating System Error 4002(The importation from the file failed.)
ERROR_STATIC_INIT

The Operating System Error 4003(The backup failed. Was a full backup done before?)
ERROR_INC_BACKUP

The Operating System Error 4004(The backup failed. Check the directory to which you are backing thedatabase.)
ERROR_FULL_BACKUP

The Operating System Error 4005(The name does not exist in the WINS database.)
ERROR_REC_NON_EXISTENT

The Operating System Error 4006(Replication with a nonconfigured partner is not allowed.)
ERROR_RPL_NOT_ALLOWED

The Operating System Error 6118(The list of servers for this workgroup is not currently available)
ERROR_NO_BROWSER_SERVERS_FOUND

The Operating System Error 10053(Connection aborted. (An established connection was aborted by the software in your host machine.))
WSAECONNABORTED

The Operating System Error 10054(Connection reset by peer. (An existing connection was forcibly closed by the remote host))
WSAECONNRESET

This is applicable for below versions of Operating Systems

Windows Server 2003
Windows Server 2008 R2
Windows Server 2012

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Operating System Error 1761 to Operating System Error 1781 for SQL Server

SQLServerF1

As a SQL Server DBA, there are various occasions where SQL Server returns Operating System errors as it causes some failure performing certain SQL Server operations. Below are Operating System Errors from Error 1761 to Operating System Error 1781

The Operating System Error 1761(The entry is not found.)
RPC_S_ENTRY_NOT_FOUND

The Operating System Error 1762(The name service is unavailable.)
RPC_S_NAME_SERVICE_UNAVAILABLE

The Operating System Error 1763(The network address family is invalid.)
RPC_S_INVALID_NAF_ID

The Operating System Error 1764(The requested operation is not supported.)
RPC_S_CANNOT_SUPPORT

The Operating System Error 1765(No security context is available to allow impersonation.)
RPC_S_NO_CONTEXT_AVAILABLE

The Operating System Error 1766(An internal error occurred in a remote procedure call (RPC).)
RPC_S_INTERNAL_ERROR

The Operating System Error 1767(The RPC server attempted an integer division by zero.)
RPC_S_ZERO_DIVIDE

The Operating System Error 1768(An addressing error occurred in the RPC server.)
RPC_S_ADDRESS_ERROR

The Operating System Error 1769(A floating-point operation at the RPC server caused a division byzero.)
RPC_S_FP_DIV_ZERO

The Operating System Error 1770(A floating-point underflow occurred at the RPC server.)
RPC_S_FP_UNDERFLOW

The Operating System Error 1771(A floating-point overflow occurred at the RPC server.)
RPC_S_FP_OVERFLOW

The Operating System Error 1772(The list of RPC servers available for the binding of auto handles hasbeen exhausted.)
RPC_X_NO_MORE_ENTRIES

The Operating System Error 1773(Unable to open the character translation table file.)
RPC_X_SS_CHAR_TRANS_OPEN_FAIL

The Operating System Error 1774(The file containing the character translation table has fewer than512 bytes.)
RPC_X_SS_CHAR_TRANS_SHORT_FILE

The Operating System Error 1775(A null context handle was passed from the client to the host during aremote procedure call.)
RPC_X_SS_IN_NULL_CONTEXT

The Operating System Error 1777(The context handle changed during a remote procedure call.)
RPC_X_SS_CONTEXT_DAMAGED

The Operating System Error 1778(The binding handles passed to a remote procedure call do not match.)
RPC_X_SS_HANDLES_MISMATCH

The Operating System Error 1779(The stub is unable to get the remote procedure call handle.)
RPC_X_SS_CANNOT_GET_CALL_HANDLE

The Operating System Error 1780(A null reference pointer was passed to the stub.)
RPC_X_NULL_REF_POINTER

The Operating System Error 1781(The enumeration value is out of range.)
RPC_X_ENUM_VALUE_OUT_OF_RANGE

This is applicable for below versions of Operating Systems

Windows Server 2003
Windows Server 2008 R2
Windows Server 2012

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2 3 4 5 22