SQL Server Wait_Type full description table

In preparing for another post to continue identifying blocking within your database, I found I did not have a full description for each wait_type, so I took the data from MSDN and created a table that holds all the full descriptions of each wait type, original data here. This was taken from SQL Server 2012 material, your mileage may vary in other versions.

Here is the table I used, very simple:

CREATE TABLE dbo.Wait_Type_Description
   (
   Wait_type VARCHAR(50) NOT NULL,
   Wait_Type_desc VARCHAR(8000) NOT NULL
   )  

and I am joining it like this, again super simple example:

SELECT *
   FROM sys.dm_exec_requests der
       JOIN dbo.wait_Type_description wtd
       ON der.wait_type = wtd.wait_type

All 345 INSERT statements can be found here, sorry this is obnoxious to look at you may download the script or cut and paste it below.

GitHub Download Link

	INSERT INTO wait_type_description VALUES('ABR','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('ASSEMBLY_LOAD','Occurs during exclusive access to assembly loading.')
	INSERT INTO wait_type_description VALUES('ASYNC_DISKPOOL_LOCK','Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.')
	INSERT INTO wait_type_description VALUES('ASYNC_IO_COMPLETION','Occurs when a task is waiting for I/Os to finish.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('BACKUP','Occurs when a task is blocked as part of backup processing.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('BROKER_CONNECTION_RECEIVE_TASK','Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('BROKER_EVENTHANDLER','Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.')
	INSERT INTO wait_type_description VALUES('BROKER_INIT','Occurs when initializing Service Broker in each active database. This should occur infrequently.')
	INSERT INTO wait_type_description VALUES('BROKER_MASTERSTART','Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.')
	INSERT INTO wait_type_description VALUES('BROKER_RECEIVE_WAITFOR','Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.')
	INSERT INTO wait_type_description VALUES('BROKER_REGISTERALLENDPOINTS','Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.')
	INSERT INTO wait_type_description VALUES('BROKER_SERVICE','Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.')
	INSERT INTO wait_type_description VALUES('BROKER_SHUTDOWN','Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('BROKER_TO_FLUSH','Occurs when the Service Broker lazy flusher flushes the in-memory transmission objects to a work table.')
	INSERT INTO wait_type_description VALUES('BROKER_TRANSMITTER','Occurs when the Service Broker transmitter is waiting for work.')
	INSERT INTO wait_type_description VALUES('BUILTIN_HASHKEY_MUTEX','May occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.')
	INSERT INTO wait_type_description VALUES('CHECK_PRINT_RECORD','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('CHECKPOINT_QUEUE','Occurs while the checkpoint task is waiting for the next checkpoint request.')
	INSERT INTO wait_type_description VALUES('CHKPT','Occurs at server startup to tell the checkpoint thread that it can start.')
	INSERT INTO wait_type_description VALUES('CLEAR_DB','Occurs during operations that change the state of a database, such as opening or closing a database.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('CLR_MANUAL_EVENT','Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('CLR_MONITOR','Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.')
	INSERT INTO wait_type_description VALUES('CLR_RWLOCK_READER','Occurs when a task is currently performing CLR execution and is waiting for a reader lock.')
	INSERT INTO wait_type_description VALUES('CLR_RWLOCK_WRITER','Occurs when a task is currently performing CLR execution and is waiting for a writer lock.')
	INSERT INTO wait_type_description VALUES('CLR_SEMAPHORE','Occurs when a task is currently performing CLR execution and is waiting for a semaphore.')
	INSERT INTO wait_type_description VALUES('CLR_TASK_START','Occurs while waiting for a CLR task to complete startup.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('CXPACKET','Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.')
	INSERT INTO wait_type_description VALUES('CXROWSET_SYNC','Occurs during a parallel range scan.')
	INSERT INTO wait_type_description VALUES('DAC_INIT','Occurs while the dedicated administrator connection is initializing.')
	INSERT INTO wait_type_description VALUES('DBMIRROR_DBM_EVENT','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('DBMIRROR_DBM_MUTEX','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('DBMIRROR_EVENTS_QUEUE','Occurs when database mirroring waits for events to process.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('DBMIRROR_WORKER_QUEUE','Indicates that the database mirroring worker task is waiting for more work.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('DEBUG','Occurs during Transact-SQL and CLR debugging for internal synchronization.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('DLL_LOADING_MUTEX','Occurs once while waiting for the XML parser DLL to load.')
	INSERT INTO wait_type_description VALUES('DROPTEMP','Occurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.')
	INSERT INTO wait_type_description VALUES('DTC','Occurs when a task is waiting on an event that is used to manage state transition. This state controls when the recovery of Microsoft Distributed Transaction Coordinator (MS DTC) transactions occurs after SQL Server receives notification that the MS DTC service has become unavailable. This state also describes a task that is waiting when a commit of a MS DTC transaction is initiated by SQL Server and SQL Server is waiting for the MS DTC commit to finish.')
	INSERT INTO wait_type_description VALUES('DTC_ABORT_REQUEST','Occurs in a MS DTC worker session when the session is waiting to take ownership of a MS DTC transaction. After MS DTC owns the transaction, the session can roll back the transaction. Generally, the session will wait for another session that is using the transaction.')
	INSERT INTO wait_type_description VALUES('DTC_RESOLVE','Occurs when a recovery task is waiting for the master database in a cross-database transaction so that the task can query the outcome of the transaction.')
	INSERT INTO wait_type_description VALUES('DTC_STATE','Occurs when a task is waiting on an event that protects changes to the internal MS DTC global state object. This state should be held for very short periods of time.')
	INSERT INTO wait_type_description VALUES('DTC_TMDOWN_REQUEST','Occurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First, the worker will wait for the MS DTC recovery process to start. Then, the worker waits to obtain the outcome of the distributed transaction that the worker is working on. This may continue until the connection with the MS DTC service has been reestablished.')
	INSERT INTO wait_type_description VALUES('DTC_WAITFOR_OUTCOME','Occurs when recovery tasks wait for MS DTC to become active to enable the resolution of prepared transactions.')
	INSERT INTO wait_type_description VALUES('DUMP_LOG_COORDINATOR','Occurs when a main task is waiting for a subtask to generate data. Ordinarily, this state does not occur. A long wait indicates an unexpected blockage. The subtask should be investigated.')
	INSERT INTO wait_type_description VALUES('DUMPTRIGGER','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('EC','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('EE_PMOLOCK','Occurs during synchronization of certain types of memory allocations during statement execution.')
	INSERT INTO wait_type_description VALUES('EE_SPECPROC_MAP_INIT','Occurs during synchronization of internal procedure hash table creation. This wait can only occur during the initial accessing of the hash table after the SQL Server instance starts.')
	INSERT INTO wait_type_description VALUES('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.')
	INSERT INTO wait_type_description VALUES('ERROR_REPORTING_MANAGER','Occurs during synchronization of multiple concurrent error log initializations.')
	INSERT INTO wait_type_description VALUES('EXCHANGE','Occurs during synchronization in the query processor exchange iterator during parallel queries.')
	INSERT INTO wait_type_description VALUES('EXECSYNC','Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state.')
	INSERT INTO wait_type_description VALUES('EXECUTION_PIPE_EVENT_INTERNAL','Occurs during synchronization between producer and consumer parts of batch execution that are submitted through the connection context.')
	INSERT INTO wait_type_description VALUES('FAILPOINT','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('FCB_REPLICA_READ','Occurs when the reads of a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.')
	INSERT INTO wait_type_description VALUES('FCB_REPLICA_WRITE','Occurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file is synchronized.')
	INSERT INTO wait_type_description VALUES('FS_FC_RWLOCK','Occurs when there is a wait by the FILESTREAM garbage collector to do either of the following: Disable garbage collection (used by backup and restore), Execute one cycle of the FILESTREAM garbage collector.')
	INSERT INTO wait_type_description VALUES('FS_GARBAGE_COLLECTOR_SHUTDOWN','Occurs when the FILESTREAM garbage collector is waiting for cleanup tasks to be completed.')
	INSERT INTO wait_type_description VALUES('FS_HEADER_RWLOCK','Occurs when there is a wait to acquire access to the FILESTREAM header of a FILESTREAM data container to either read or update contents in the FILESTREAM header file (Filestream.hdr).')
	INSERT INTO wait_type_description VALUES('FS_LOGTRUNC_RWLOCK','Occurs when there is a wait to acquire access to FILESTREAM log truncation to do either of the following: Temporarily disable FILESTREAM log (FSLOG) truncation (used by backup and restore). Execute one cycle of FSLOG truncation.')
	INSERT INTO wait_type_description VALUES('FSA_FORCE_OWN_XACT','Occurs when a FILESTREAM file I/O operation needs to bind to the associated transaction, but the transaction is currently owned by another session.')
	INSERT INTO wait_type_description VALUES('FSAGENT','Occurs when a FILESTREAM file I/O operation is waiting for a FILESTREAM agent resource that is being used by another file I/O operation.')
	INSERT INTO wait_type_description VALUES('FSTR_CONFIG_MUTEX','Occurs when there is a wait for another FILESTREAM feature reconfiguration to be completed.')
	INSERT INTO wait_type_description VALUES('FSTR_CONFIG_RWLOCK','Occurs when there is a wait to serialize access to the FILESTREAM configuration parameters.')
	INSERT INTO wait_type_description VALUES('FT_METADATA_MUTEX','Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('FT_RESTART_CRAWL','Occurs when a full-text crawl needs to restart from a last known good point to recover from a transient failure. The wait lets the worker tasks currently working on that population to complete or exit the current step.')
	INSERT INTO wait_type_description VALUES('FULLTEXT GATHERER','Occurs during synchronization of full-text operations.')
	INSERT INTO wait_type_description VALUES('GUARDIAN','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('HADR_AG_MUTEX','Occurs when an AlwaysOn DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the configuration of an availability group.')
	INSERT INTO wait_type_description VALUES('HADR_AR_CRITICAL_SECTION_ENTRY','Occurs when an AlwaysOn DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the runtime state of the local replica of the associated availability group.')
	INSERT INTO wait_type_description VALUES('HADR_AR_MANAGER_MUTEX','Occurs when an availability replica shutdown is waiting for startup to complete or an availability replica startup is waiting for shutdown to complete. Internal use only. NOTE: Availability replica shutdown is initiated either by SQL Server shutdown or by SQL Server handling the loss of quorum by the Windows Server Failover Clustering node. Availability replica startup is initiated either by SQL Server startup or by SQL Server recovering from the loss of quorum by the Windows Server Failover Clustering node.')
	INSERT INTO wait_type_description VALUES('HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST','The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers. Internal use only.')
	INSERT INTO wait_type_description VALUES('HADR_BACKUP_BULK_LOCK','The AlwaysOn primary database received a backup request from a secondary database and is waiting for the background thread to finish processing the request on acquiring or releasing the BulkOp lock.')
	INSERT INTO wait_type_description VALUES('HADR_BACKUP_QUEUE','The backup background thread of the AlwaysOn primary database is waiting for a new work request from the secondary database. (typically, this occurs when the primary database is holding the BulkOp log and is waiting for the secondary database to indicate that the primary database can release the lock).')
	INSERT INTO wait_type_description VALUES('HADR_CLUSAPI_CALL','A SQL Server thread is waiting to switch from non-preemptive mode (scheduled by SQL Server) to preemptive mode (scheduled by the operating system) in order to invoke Windows Server Failover Clustering APIs.')
	INSERT INTO wait_type_description VALUES('HADR_COMPRESSED_CACHE_SYNC','Waiting for access to the cache of compressed log blocks that is used to avoid redundant compression of the log blocks sent to multiple secondary databases.')
	INSERT INTO wait_type_description VALUES('HADR_DATABASE_FLOW_CONTROL','Waiting for messages to be sent to the partner when the maximum number of queued messages has been reached. Indicates that the log scans are running faster than the network sends. This is an issue only if network sends are slower than expected.')
	INSERT INTO wait_type_description VALUES('HADR_DATABASE_VERSIONING_STATE','Occurs on the versioning state change of an AlwaysOn secondary database. This wait is for internal data structures and is usually is very short with no direct effect on data access.')
	INSERT INTO wait_type_description VALUES('HADR_DATABASE_WAIT_FOR_RESTART','Waiting for the database to restart under AlwaysOn Availability Groups control. Under normal conditions, this is not a customer issue because waits are expected here.')
	INSERT INTO wait_type_description VALUES('HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING','A query on object(s) in a readable secondary database of an AlwaysOn availability group is blocked on row versioning while waiting for commit or rollback of all transactions that were in-flight when the secondary replica was enabled for read workloads. This wait type guarantees that row versions are available before execution of a query under snapshot isolation.')
	INSERT INTO wait_type_description VALUES('HADR_DB_COMMAND','Waiting for responses to conversational messages (which require an explicit response from the other side, using the AlwaysOn conversational message infrastructure). A number of different message types use this wait type.')
	INSERT INTO wait_type_description VALUES('HADR_DB_OP_COMPLETION_SYNC','Waiting for responses to conversational messages (which require an explicit response from the other side, using the AlwaysOn conversational message infrastructure). A number of different message types use this wait type.')
	INSERT INTO wait_type_description VALUES('HADR_DB_OP_START_SYNC','An AlwaysOn DDL statement or a Windows Server Failover Clustering command is waiting for serialized access to an availability database and its runtime state.')
	INSERT INTO wait_type_description VALUES('HADR_DBR_SUBSCRIBER','The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the runtime state of an event subscriber that corresponds to an availability database. Internal use only.')
	INSERT INTO wait_type_description VALUES('HADR_DBR_SUBSCRIBER_FILTER_LIST','The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers that correspond to availability databases. Internal use only.')
	INSERT INTO wait_type_description VALUES('HADR_DBSTATECHANGE_SYNC','Concurrency control wait for updating the internal state of the database replica.')
	INSERT INTO wait_type_description VALUES('HADR_FILESTREAM_BLOCK_FLUSH','The FILESTREAM AlwaysOn transport manager is waiting until processing of a log block is finished.')
	INSERT INTO wait_type_description VALUES('HADR_FILESTREAM_FILE_CLOSE','The FILESTREAM AlwaysOn transport manager is waiting until the next FILESTREAM file gets processed and its handle gets closed.')
	INSERT INTO wait_type_description VALUES('HADR_FILESTREAM_FILE_REQUEST','An AlwaysOn secondary replica is waiting for the primary replica to send all requested FILESTREAM files during UNDO.')
	INSERT INTO wait_type_description VALUES('HADR_FILESTREAM_IOMGR','The FILESTREAM AlwaysOn transport manager is waiting for R/W lock that protects the FILESTREAM AlwaysOn I/O manager during startup or shutdown.')
	INSERT INTO wait_type_description VALUES('HADR_FILESTREAM_IOMGR_IOCOMPLETION','The FILESTREAM AlwaysOn I/O manager is waiting for I/O completion.')
	INSERT INTO wait_type_description VALUES('HADR_FILESTREAM_MANAGER','The FILESTREAM AlwaysOn transport manager is waiting for the R/W lock that protects the FILESTREAM AlwaysOn transport manager during startup or shutdown.')
	INSERT INTO wait_type_description VALUES('HADR_GROUP_COMMIT','Transaction commit processing is waiting to allow a group commit so that multiple commit log records can be put into a single log block. This wait is an expected condition that optimizes the log I/O, capture, and send operations.')
	INSERT INTO wait_type_description VALUES('HADR_LOGCAPTURE_SYNC','Concurrency control around the log capture or apply object when creating or destroying scans. This is an expected wait when partners change state or connection status.')
	INSERT INTO wait_type_description VALUES('HADR_LOGCAPTURE_WAIT','Waiting for log records to become available. Can occur either when waiting for new log records to be generated by connections or for I/O completion when reading log not in the cache. This is an expected wait if the log scan is caught up to the end of log or is reading from disk.')
	INSERT INTO wait_type_description VALUES('HADR_LOGPROGRESS_SYNC','Concurrency control wait when updating the log progress status of database replicas.')
	INSERT INTO wait_type_description VALUES('HADR_NOTIFICATION_DEQUEUE','A background task that processes Windows Server Failover Clustering notifications is waiting for the next notification. Internal use only.')
	INSERT INTO wait_type_description VALUES('HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS','The AlwaysOn availability replica manager is waiting for serialized access to the runtime state of a background task that processes Windows Server Failover Clustering notifications. Internal use only.')
	INSERT INTO wait_type_description VALUES('HADR_NOTIFICATION_WORKER_STARTUP_SYNC','A background task is waiting for the completion of the startup of a background task that processes Windows Server Failover Clustering notifications. Internal use only.')
	INSERT INTO wait_type_description VALUES('HADR_NOTIFICATION_WORKER_TERMINATION_SYNC','A background task is waiting for the termination of a background task that processes Windows Server Failover Clustering notifications. Internal use only.')
	INSERT INTO wait_type_description VALUES('HADR_PARTNER_SYNC','Concurrency control wait on the partner list.')
	INSERT INTO wait_type_description VALUES('HADR_READ_ALL_NETWORKS','Waiting to get read or write access to the list of WSFC networks. Internal use only. NOTE: The engine keeps a list of WSFC networks that is used in dynamic management views (such as sys.dm_hadr_cluster_networks) or to validate AlwaysOn Transact-SQL statements that reference WSFC network information. This list is updated upon engine startup, WSFC related notifications, and internal AlwaysOn restart (for example, losing and regaining of WSFC quorum). Tasks will usually be blocked when an update in that list is in progress.')
	INSERT INTO wait_type_description VALUES('HADR_RECOVERY_WAIT_FOR_CONNECTION','Waiting for the secondary database to connect to the primary database before running recovery. This is an expected wait, which can lengthen if the connection to the primary is slow to establish.')
	INSERT INTO wait_type_description VALUES('HADR_RECOVERY_WAIT_FOR_UNDO','Database recovery is waiting for the secondary database to finish the reverting and initializing phase to bring it back to the common log point with the primary database. This is an expected wait after failovers.Undo progress can be tracked through the Windows System Monitor (perfmon.exe) and dynamic management views.')
	INSERT INTO wait_type_description VALUES('HADR_REPLICAINFO_SYNC','Waiting for concurrency control to update the current replica state.')
	INSERT INTO wait_type_description VALUES('HADR_SYNC_COMMIT','Waiting for transaction commit processing for the synchronized secondary databases to harden the log. This wait is also reflected by the Transaction Delay performance counter. This wait type is expected for synchronized availability groups and indicates the time to send, write, and acknowledge log to the secondary databases.')
	INSERT INTO wait_type_description VALUES('HADR_SYNCHRONIZING_THROTTLE','Waiting for transaction commit processing to allow a synchronizing secondary database to catch up to the primary end of log in order to transition to the synchronized state. This is an expected wait when a secondary database is catching up.')
	INSERT INTO wait_type_description VALUES('HADR_TDS_LISTENER_SYNC','Either the internal AlwaysOn system or the WSFC cluster will request that listeners are started or stopped. The processing of this request is always asynchronous, and there is a mechanism to remove redundant requests. There are also moments that this process is suspended because of configuration changes. All waits related with this listener synchronization mechanism use this wait type. Internal use only.')
	INSERT INTO wait_type_description VALUES('HADR_TDS_LISTENER_SYNC_PROCESSING','Used at the end of an AlwaysOn Transact-SQL statement that requires starting and/or stopping an availability group listener. Since the start/stop operation is done asynchronously, the user thread will block using this wait type until the situation of the listener is known.')
	INSERT INTO wait_type_description VALUES('HADR_TIMER_TASK','Waiting to get the lock on the timer task object and is also used for the actual waits between times that work is being performed. For example, for a task that runs every 10 seconds, after one execution, AlwaysOn Availability Groups waits about 10 seconds to reschedule the task, and the wait is included here.')
	INSERT INTO wait_type_description VALUES('HADR_TRANSPORT_DBRLIST','Waiting for access to the transport layers database replica list. Used for the spinlock that grants access to it.')
	INSERT INTO wait_type_description VALUES('HADR_TRANSPORT_FLOW_CONTROL','Waiting when the number of outstanding unacknowledged AlwaysOn messages is over the out flow control threshold. This is on an availability replica-to-replica basis (not on a database-to-database basis).')
	INSERT INTO wait_type_description VALUES('HADR_TRANSPORT_SESSION','AlwaysOn Availability Groups is waiting while changing or accessing the underlying transport state.')
	INSERT INTO wait_type_description VALUES('HADR_WORK_POOL','Concurrency control wait on the AlwaysOn Availability Groups background work task object.')
	INSERT INTO wait_type_description VALUES('HADR_WORK_QUEUE','AlwaysOn Availability Groups background worker thread waiting for new work to be assigned. This is an expected wait when there are ready workers waiting for new work, which is the normal state.')
	INSERT INTO wait_type_description VALUES('HADR_XRF_STACK_ACCESS','Accessing (look up, add, and delete) the extended recovery fork stack for an AlwaysOn availability database.')
	INSERT INTO wait_type_description VALUES('HTTP_ENUMERATION','Occurs at startup to enumerate the HTTP endpoints to start HTTP.')
	INSERT INTO wait_type_description VALUES('HTTP_START','Occurs when a connection is waiting for HTTP to complete initialization.')
	INSERT INTO wait_type_description VALUES('IMPPROV_IOWAIT','Occurs when SQL Server waits for a bulkload I/O to finish.')
	INSERT INTO wait_type_description VALUES('INTERNAL_TESTING','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('IO_AUDIT_MUTEX','Occurs during synchronization of trace event buffers.')
	INSERT INTO wait_type_description VALUES('IO_COMPLETION','Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.')
	INSERT INTO wait_type_description VALUES('IO_RETRY','Occurs when an I/O operation such as a read or a write to disk fails because of insufficient resources, and is then retried.')
	INSERT INTO wait_type_description VALUES('IOAFF_RANGE_QUEUE','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('KSOURCE_WAKEUP','Used by the service control task while waiting for requests from the Service Control Manager. Long waits are expected and do not indicate a problem.')
	INSERT INTO wait_type_description VALUES('KTM_ENLISTMENT','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('KTM_RECOVERY_MANAGER','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('KTM_RECOVERY_RESOLUTION','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('LATCH_DT','Occurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.')
	INSERT INTO wait_type_description VALUES('LATCH_EX','Occurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.')
	INSERT INTO wait_type_description VALUES('LATCH_KP','Occurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.')
	INSERT INTO wait_type_description VALUES('LATCH_NL','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('LATCH_SH','Occurs when waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.')
	INSERT INTO wait_type_description VALUES('LATCH_UP','Occurs when waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.')
	INSERT INTO wait_type_description VALUES('LAZYWRITER_SLEEP','Occurs when lazywriter tasks are suspended. This is a measure of the time spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls.')
	INSERT INTO wait_type_description VALUES('LCK_M_BU','Occurs when a task is waiting to acquire a Bulk Update (BU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_IS','Occurs when a task is waiting to acquire an Intent Shared (IS) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_IU','Occurs when a task is waiting to acquire an Intent Update (IU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_IX','Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RIn_NL','Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RIn_S','Occurs when a task is waiting to acquire a shared lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RIn_U','Task is waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RIn_X','Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RS_S','Occurs when a task is waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RS_U','Occurs when a task is waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RX_S','Occurs when a task is waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RX_U','Occurs when a task is waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_RX_X','Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_S','Occurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_SCH_M','Occurs when a task is waiting to acquire a Schema Modify lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_SCH_S','Occurs when a task is waiting to acquire a Schema Share lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_SIU','Occurs when a task is waiting to acquire a Shared With Intent Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_SIX','Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_U','Occurs when a task is waiting to acquire an Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_UIX','Occurs when a task is waiting to acquire an Update With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LCK_M_X','Occurs when a task is waiting to acquire an Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('LOGBUFFER','Occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server.')
	INSERT INTO wait_type_description VALUES('LOGGENERATION','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('LOGMGR','Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database.')
	INSERT INTO wait_type_description VALUES('LOGMGR_FLUSH','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('LOGMGR_QUEUE','Occurs while the log writer task waits for work requests.')
	INSERT INTO wait_type_description VALUES('LOGMGR_RESERVE_APPEND','Occurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.')
	INSERT INTO wait_type_description VALUES('LOWFAIL_MEMMGR_QUEUE','Occurs while waiting for memory to be available for use.')
	INSERT INTO wait_type_description VALUES('MISCELLANEOUS','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('MSQL_DQ','Occurs when a task is waiting for a distributed query operation to finish. This is used to detect potential Multiple Active Result Set (MARS) application deadlocks. The wait ends when the distributed query call finishes.')
	INSERT INTO wait_type_description VALUES('MSQL_XACT_MGR_MUTEX','Occurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation.')
	INSERT INTO wait_type_description VALUES('MSQL_XACT_MUTEX','Occurs during synchronization of transaction usage. A request must acquire the mutex before it can use the transaction.')
	INSERT INTO wait_type_description VALUES('MSQL_XP','Occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.')
	INSERT INTO wait_type_description VALUES('MSSEARCH','Occurs during Full-Text Search calls. This wait ends when the full-text operation completes. It does not indicate contention, but rather the duration of full-text operations.')
	INSERT INTO wait_type_description VALUES('NET_WAITFOR_PACKET','Occurs when a connection is waiting for a network packet during a network read.')
	INSERT INTO wait_type_description VALUES('OLEDB','Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.')
	INSERT INTO wait_type_description VALUES('ONDEMAND_TASK_QUEUE','Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern.')
	INSERT INTO wait_type_description VALUES('PAGEIOLATCH_DT','Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem.')
	INSERT INTO wait_type_description VALUES('PAGEIOLATCH_EX','Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.')
	INSERT INTO wait_type_description VALUES('PAGEIOLATCH_KP','Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem.')
	INSERT INTO wait_type_description VALUES('PAGEIOLATCH_NL','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('PAGEIOLATCH_SH','Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.')
	INSERT INTO wait_type_description VALUES('PAGEIOLATCH_UP','Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem.')
	INSERT INTO wait_type_description VALUES('PAGELATCH_DT','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.')
	INSERT INTO wait_type_description VALUES('PAGELATCH_EX','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.')
	INSERT INTO wait_type_description VALUES('PAGELATCH_KP','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.')
	INSERT INTO wait_type_description VALUES('PAGELATCH_NL','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('PAGELATCH_SH','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.')
	INSERT INTO wait_type_description VALUES('PAGELATCH_UP','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode.')
	INSERT INTO wait_type_description VALUES('PARALLEL_BACKUP_QUEUE','Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_ABR','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_AUDIT_ACCESS_EVENTLOG','Occurs when the SQL Server Operating System (SQLOS) scheduler switches to preemptive mode to write an audit event to the Windows event log.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_AUDIT_ACCESS_SECLOG','Occurs when the SQLOS scheduler switches to preemptive mode to write an audit event to the Windows Security log.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_CLOSEBACKUPMEDIA','Occurs when the SQLOS scheduler switches to preemptive mode to close backup media.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_CLOSEBACKUPTAPE','Occurs when the SQLOS scheduler switches to preemptive mode to close a tape backup device.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_CLOSEBACKUPVDIDEVICE','Occurs when the SQLOS scheduler switches to preemptive mode to close a virtual backup device.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL','Occurs when the SQLOS scheduler switches to preemptive mode to perform Windows failover cluster operations.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_COM_COCREATEINSTANCE','Occurs when the SQLOS scheduler switches to preemptive mode to create a COM object.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_HADR_LEASE_MECHANISM','AlwaysOn Availability Groups lease manager scheduling for CSS diagnostics.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_SOSTESTING','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_STRESSDRIVER','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_TESTING','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('PREEMPTIVE_XETESTING','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('PRINT_ROLLBACK_PROGRESS','Used to wait while user processes are ended in a database that has been transitioned by using the ALTER DATABASE termination clause. For more information, see ALTER DATABASE (Transact-SQL).')
	INSERT INTO wait_type_description VALUES('PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC','Occurs when a background task is waiting for the termination of the background task that receives (via polling) Windows Server Failover Clustering notifications. Internal use only.')
	INSERT INTO wait_type_description VALUES('PWAIT_HADR_CLUSTER_INTEGRATION','An append, replace, and/or remove operation is waiting to grab a write lock on an AlwaysOn internal list (such as a list of networks, network addresses, or availability group listeners). Internal use only.')
	INSERT INTO wait_type_description VALUES('PWAIT_HADR_OFFLINE_COMPLETED','An AlwaysOn drop availability group operation is waiting for the target availability group to go offline before destroying Windows Server Failover Clustering objects.')
	INSERT INTO wait_type_description VALUES('PWAIT_HADR_ONLINE_COMPLETED','An AlwaysOn create or failover availability group operation is waiting for the target availability group to come online.')
	INSERT INTO wait_type_description VALUES('PWAIT_HADR_POST_ONLINE_COMPLETED','An AlwaysOn drop availability group operation is waiting for the termination of any background task that was scheduled as part of a previous command. For example, there may be a background task that is transitioning availability databases to the primary role. The DROP AVAILABILITY GROUP DDL must wait for this background task to terminate in order to avoid race conditions.')
	INSERT INTO wait_type_description VALUES('PWAIT_HADR_WORKITEM_COMPLETED','Internal wait by a thread waiting for an async work task to complete. This is an expected wait and is for CSS use.')
	INSERT INTO wait_type_description VALUES('QPJOB_KILL','Indicates that an asynchronous automatic statistics update was canceled by a call to KILL as the update was starting to run. The terminating thread is suspended, waiting for it to start listening for KILL commands. A good value is less than one second.')
	INSERT INTO wait_type_description VALUES('QPJOB_WAITFOR_ABORT','Indicates that an asynchronous automatic statistics update was canceled by a call to KILL when it was running. The update has now completed but is suspended until the terminating thread message coordination is complete. This is an ordinary but rare state, and should be very short. A good value is less than one second.')
	INSERT INTO wait_type_description VALUES('QRY_MEM_GRANT_INFO_MUTEX','Occurs when Query Execution memory management tries to control access to static grant information list. This state lists information about the current granted and waiting memory requests. This state is a simple access control state. There should never be a long wait on this state. If this mutex is not released, all new memory-using queries will stop responding.')
	INSERT INTO wait_type_description VALUES('QUERY_ERRHDL_SERVICE_DONE','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN','Occurs in certain cases when offline create index build is run in parallel, and the different worker threads that are sorting synchronize access to the sort files.')
	INSERT INTO wait_type_description VALUES('QUERY_NOTIFICATION_MGR_MUTEX','Occurs during synchronization of the garbage collection queue in the Query Notification Manager.')
	INSERT INTO wait_type_description VALUES('QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX','Occurs during state synchronization for transactions in Query Notifications.')
	INSERT INTO wait_type_description VALUES('QUERY_NOTIFICATION_TABLE_MGR_MUTEX','Occurs during internal synchronization within the Query Notification Manager.')
	INSERT INTO wait_type_description VALUES('QUERY_NOTIFICATION_UNITTEST_MUTEX','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('QUERY_OPTIMIZER_PRINT_MUTEX','Occurs during synchronization of query optimizer diagnostic output production. This wait type only occurs if diagnostic settings have been enabled under direction of Microsoft Product Support.')
	INSERT INTO wait_type_description VALUES('QUERY_TRACEOUT','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('QUERY_WAIT_ERRHDL_SERVICE','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('RECOVER_CHANGEDB','Occurs during synchronization of database status in warm standby database.')
	INSERT INTO wait_type_description VALUES('REPL_CACHE_ACCESS','Occurs during synchronization on a replication article cache. During these waits, the replication log reader stalls, and data definition language (DDL) statements on a published table are blocked.')
	INSERT INTO wait_type_description VALUES('REPL_SCHEMA_ACCESS','Occurs during synchronization of replication schema version information. This state exists when DDL statements are executed on the replicated object, and when the log reader builds or consumes versioned schema based on DDL occurrence.')
	INSERT INTO wait_type_description VALUES('REPLICA_WRITES','Occurs while a task waits for completion of page writes to database snapshots or DBCC replicas.')
	INSERT INTO wait_type_description VALUES('REQUEST_DISPENSER_PAUSE','Occurs when a task is waiting for all outstanding I/O to complete, so that I/O to a file can be frozen for snapshot backup.')
	INSERT INTO wait_type_description VALUES('REQUEST_FOR_DEADLOCK_SEARCH','Occurs while the deadlock monitor waits to start the next deadlock search. This wait is expected between deadlock detections, and lengthy total waiting time on this resource does not indicate a problem.')
	INSERT INTO wait_type_description VALUES('RESMGR_THROTTLED','Occurs when a new request comes in and is throttled based on the GROUP_MAX_REQUESTS setting.')
	INSERT INTO wait_type_description VALUES('RESOURCE_QUEUE','Occurs during synchronization of various internal resource queues.')
	INSERT INTO wait_type_description VALUES('RESOURCE_SEMAPHORE','Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.')
	INSERT INTO wait_type_description VALUES('RESOURCE_SEMAPHORE_MUTEX','Occurs while a query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests.')
	INSERT INTO wait_type_description VALUES('RESOURCE_SEMAPHORE_QUERY_COMPILE','Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.')
	INSERT INTO wait_type_description VALUES('RESOURCE_SEMAPHORE_SMALL_QUERY','Occurs when memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds, because the server transfers the request to the main query memory pool if it fails to grant the requested memory within a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries.')
	INSERT INTO wait_type_description VALUES('SEC_DROP_TEMP_KEY','Occurs after a failed attempt to drop a temporary security key before a retry attempt.')
	INSERT INTO wait_type_description VALUES('SECURITY_MUTEX','Occurs when there is a wait for mutexes that control access to the global list of Extensible Key Management (EKM) cryptographic providers and the session-scoped list of EKM sessions.')
	INSERT INTO wait_type_description VALUES('SEQUENTIAL_GUID','Occurs while a new sequential GUID is being obtained.')
	INSERT INTO wait_type_description VALUES('SERVER_IDLE_CHECK','Occurs during synchronization of SQL Server instance idle status when a resource monitor is attempting to declare a SQL Server instance as idle or trying to wake up.')
	INSERT INTO wait_type_description VALUES('SHUTDOWN','Occurs while a shutdown statement waits for active connections to exit.')
	INSERT INTO wait_type_description VALUES('SLEEP_BPOOL_FLUSH','Occurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.')
	INSERT INTO wait_type_description VALUES('SLEEP_DBSTARTUP','Occurs during database startup while waiting for all databases to recover.')
	INSERT INTO wait_type_description VALUES('SLEEP_DCOMSTARTUP','Occurs once at most during SQL Server instance startup while waiting for DCOM initialization to complete.')
	INSERT INTO wait_type_description VALUES('SLEEP_MSDBSTARTUP','Occurs when SQL Trace waits for the msdb database to complete startup.')
	INSERT INTO wait_type_description VALUES('SLEEP_SYSTEMTASK','Occurs during the start of a background task while waiting for tempdb to complete startup.')
	INSERT INTO wait_type_description VALUES('SLEEP_TASK','Occurs when a task sleeps while waiting for a generic event to occur.')
	INSERT INTO wait_type_description VALUES('SLEEP_TEMPDBSTARTUP','Occurs while a task waits for tempdb to complete startup.')
	INSERT INTO wait_type_description VALUES('SNI_CRITICAL_SECTION','Occurs during internal synchronization within SQL Server networking components.')
	INSERT INTO wait_type_description VALUES('SNI_HTTP_WAITFOR_0_DISCON','Occurs during SQL Server shutdown, while waiting for outstanding HTTP connections to exit.')
	INSERT INTO wait_type_description VALUES('SNI_LISTENER_ACCESS','Occurs while waiting for non-uniform memory access (NUMA) nodes to update state change. Access to state change is serialized.')
	INSERT INTO wait_type_description VALUES('SNI_TASK_COMPLETION','Occurs when there is a wait for all tasks to finish during a NUMA node state change.')
	INSERT INTO wait_type_description VALUES('SOAP_READ','Occurs while waiting for an HTTP network read to complete.')
	INSERT INTO wait_type_description VALUES('SOAP_WRITE','Occurs while waiting for an HTTP network write to complete.')
	INSERT INTO wait_type_description VALUES('SOS_CALLBACK_REMOVAL','Occurs while performing synchronization on a callback list in order to remove a callback. It is not expected for this counter to change after server initialization is completed.')
	INSERT INTO wait_type_description VALUES('SOS_DISPATCHER_MUTEX','Occurs during internal synchronization of the dispatcher pool. This includes when the pool is being adjusted.')
	INSERT INTO wait_type_description VALUES('SOS_LOCALALLOCATORLIST','Occurs during internal synchronization in the SQL Server memory manager.')
	INSERT INTO wait_type_description VALUES('SOS_MEMORY_USAGE_ADJUSTMENT','Occurs when memory usage is being adjusted among pools.')
	INSERT INTO wait_type_description VALUES('SOS_OBJECT_STORE_DESTROY_MUTEX','Occurs during internal synchronization in memory pools when destroying objects from the pool.')
	INSERT INTO wait_type_description VALUES('SOS_PROCESS_AFFINITY_MUTEX','Occurs during synchronizing of access to process affinity settings.')
	INSERT INTO wait_type_description VALUES('SOS_RESERVEDMEMBLOCKLIST','Occurs during internal synchronization in the SQL Server memory manager.')
	INSERT INTO wait_type_description VALUES('SOS_SCHEDULER_YIELD','Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.')
	INSERT INTO wait_type_description VALUES('SOS_SMALL_PAGE_ALLOC','Occurs during the allocation and freeing of memory that is managed by some memory objects.')
	INSERT INTO wait_type_description VALUES('SOS_STACKSTORE_INIT_MUTEX','Occurs during synchronization of internal store initialization.')
	INSERT INTO wait_type_description VALUES('SOS_SYNC_TASK_ENQUEUE_EVENT','Occurs when a task is started in a synchronous manner. Most tasks in SQL Server are started in an asynchronous manner, in which control returns to the starter immediately after the task request has been placed on the work queue.')
	INSERT INTO wait_type_description VALUES('SOS_VIRTUALMEMORY_LOW','Occurs when a memory allocation waits for a resource manager to free up virtual memory.')
	INSERT INTO wait_type_description VALUES('SOSHOST_EVENT','Occurs when a hosted component, such as CLR, waits on a SQL Server event synchronization object.')
	INSERT INTO wait_type_description VALUES('SOSHOST_INTERNAL','Occurs during synchronization of memory manager callbacks used by hosted components, such as CLR.')
	INSERT INTO wait_type_description VALUES('SOSHOST_MUTEX','Occurs when a hosted component, such as CLR, waits on a SQL Server mutex synchronization object.')
	INSERT INTO wait_type_description VALUES('SOSHOST_RWLOCK','Occurs when a hosted component, such as CLR, waits on a SQL Server reader-writer synchronization object.')
	INSERT INTO wait_type_description VALUES('SOSHOST_SEMAPHORE','Occurs when a hosted component, such as CLR, waits on a SQL Server semaphore synchronization object.')
	INSERT INTO wait_type_description VALUES('SOSHOST_SLEEP','Occurs when a hosted task sleeps while waiting for a generic event to occur. Hosted tasks are used by hosted components such as CLR.')
	INSERT INTO wait_type_description VALUES('SOSHOST_TRACELOCK','Occurs during synchronization of access to trace streams.')
	INSERT INTO wait_type_description VALUES('SOSHOST_WAITFORDONE','Occurs when a hosted component, such as CLR, waits for a task to complete.')
	INSERT INTO wait_type_description VALUES('SQLCLR_APPDOMAIN','Occurs while CLR waits for an application domain to complete startup.')
	INSERT INTO wait_type_description VALUES('SQLCLR_ASSEMBLY','Occurs while waiting for access to the loaded assembly list in the appdomain.')
	INSERT INTO wait_type_description VALUES('SQLCLR_DEADLOCK_DETECTION','Occurs while CLR waits for deadlock detection to complete.')
	INSERT INTO wait_type_description VALUES('SQLCLR_QUANTUM_PUNISHMENT','Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks.')
	INSERT INTO wait_type_description VALUES('SQLSORT_NORMMUTEX','Occurs during internal synchronization, while initializing internal sorting structures.')
	INSERT INTO wait_type_description VALUES('SQLSORT_SORTMUTEX','Occurs during internal synchronization, while initializing internal sorting structures.')
	INSERT INTO wait_type_description VALUES('SQLTRACE_BUFFER_FLUSH','Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.')
	INSERT INTO wait_type_description VALUES('SQLTRACE_LOCK','Occurs during synchronization on trace buffers during a file trace.')
	INSERT INTO wait_type_description VALUES('SQLTRACE_SHUTDOWN','Occurs while trace shutdown waits for outstanding trace events to complete.')
	INSERT INTO wait_type_description VALUES('SQLTRACE_WAIT_ENTRIES','Occurs while a SQL Trace event queue waits for packets to arrive on the queue.')
	INSERT INTO wait_type_description VALUES('SRVPROC_SHUTDOWN','Occurs while the shutdown process waits for internal resources to be released to shutdown cleanly.')
	INSERT INTO wait_type_description VALUES('TEMPOBJ','Occurs when temporary object drops are synchronized. This wait is rare, and only occurs if a task has requested exclusive access for temp table drops.')
	INSERT INTO wait_type_description VALUES('THREADPOOL','Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking unusually long, thus reducing the number of workers available to satisfy other batches.')
	INSERT INTO wait_type_description VALUES('TIMEPRIV_TIMEPERIOD','Occurs during internal synchronization of the Extended Events timer.')
	INSERT INTO wait_type_description VALUES('TRACEWRITE','Occurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process.')
	INSERT INTO wait_type_description VALUES('TRAN_MARKLATCH_DT','Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.')
	INSERT INTO wait_type_description VALUES('TRAN_MARKLATCH_EX','Occurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.')
	INSERT INTO wait_type_description VALUES('TRAN_MARKLATCH_KP','Occurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.')
	INSERT INTO wait_type_description VALUES('TRAN_MARKLATCH_NL','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('TRAN_MARKLATCH_SH','Occurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.')
	INSERT INTO wait_type_description VALUES('TRAN_MARKLATCH_UP','Occurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.')
	INSERT INTO wait_type_description VALUES('TRANSACTION_MUTEX','Occurs during synchronization of access to a transaction by multiple batches.')
	INSERT INTO wait_type_description VALUES('UTIL_PAGE_ALLOC','Occurs when transaction log scans wait for memory to be available during memory pressure.')
	INSERT INTO wait_type_description VALUES('VIA_ACCEPT','Occurs when a Virtual Interface Adapter (VIA) provider connection is completed during startup.')
	INSERT INTO wait_type_description VALUES('VIEW_DEFINITION_MUTEX','Occurs during synchronization on access to cached view definitions.')
	INSERT INTO wait_type_description VALUES('WAIT_FOR_RESULTS','Occurs when waiting for a query notification to be triggered.')
	INSERT INTO wait_type_description VALUES('WAITFOR','Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.')
	INSERT INTO wait_type_description VALUES('WAITFOR_TASKSHUTDOWN','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('WAITSTAT_MUTEX','Occurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.')
	INSERT INTO wait_type_description VALUES('WCC','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('WORKTBL_DROP','Occurs while pausing before retrying, after a failed worktable drop.')
	INSERT INTO wait_type_description VALUES('WRITE_COMPLETION','Occurs when a write operation is in progress.')
	INSERT INTO wait_type_description VALUES('WRITELOG','Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.')
	INSERT INTO wait_type_description VALUES('XACT_OWN_TRANSACTION','Occurs while waiting to acquire ownership of a transaction.')
	INSERT INTO wait_type_description VALUES('XACT_RECLAIM_SESSION','Occurs while waiting for the current owner of a session to release ownership of the session.')
	INSERT INTO wait_type_description VALUES('XACTLOCKINFO','Occurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.')
	INSERT INTO wait_type_description VALUES('XACTWORKSPACE_MUTEX','Occurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.')
	INSERT INTO wait_type_description VALUES('XE_BUFFERMGR_ALLPROCESSED_EVENT','Occurs when Extended Events session buffers are flushed to targets. This wait occurs on a background thread.')
	INSERT INTO wait_type_description VALUES('XE_BUFFERMGR_FREEBUF_EVENT','Occurs when either of the following conditions is true: An Extended Events session is configured for no event loss, and all buffers in the session are currently full. This can indicate that the buffers for an Extended Events session are too small, or should be partitioned. OR Audits experience a delay. This can indicate a disk bottleneck on the drive where the audits are written.')
	INSERT INTO wait_type_description VALUES('XE_DISPATCHER_CONFIG_SESSION_LIST','Occurs when an Extended Events session that is using asynchronous targets is started or stopped. This wait indicates either of the following: An Extended Events session is registering with a background thread pool. OR The background thread pool is calculating the required number of threads based on current load.')
	INSERT INTO wait_type_description VALUES('XE_DISPATCHER_JOIN','Occurs when a background thread that is used for Extended Events sessions is terminating.')
	INSERT INTO wait_type_description VALUES('XE_DISPATCHER_WAIT','Occurs when a background thread that is used for Extended Events sessions is waiting for event buffers to process.')
	INSERT INTO wait_type_description VALUES('XE_MODULEMGR_SYNC','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('XE_OLS_LOCK','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('XE_PACKAGE_LOCK_BACKOFF','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('FT_COMPROWSET_RWLOCK','Full-text is waiting on fragment metadata operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('FT_IFTS_RWLOCK','Full-text is waiting on internal synchronization. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('FT_IFTS_SCHEDULER_IDLE_WAIT','Full-text scheduler sleep wait type. The scheduler is idle.')
	INSERT INTO wait_type_description VALUES('FT_IFTSHC_MUTEX','Full-text is waiting on an fdhost control operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('FT_IFTSISM_MUTEX','Full-text is waiting on communication operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.')
	INSERT INTO wait_type_description VALUES('FT_MASTER_MERGE','Full-text is waiting on master merge operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.')

view raw Wait_type-INSERT-Statements.sql hosted with ❤ by GitHub

Are your customers abusing you?

Do you have Customers that you dread talking to? You know the types, they are not happy, ever, and have a seemingly endless stream of new requirements (read as: demands) of you and your team. While this post is geared toward those of us who have or are currently working in a large enterprise where database teams and IT specifically work 'for' other internal teams, it applies to everyone with customers or users.
In a large company, you often don't get to 'charge' for your services, the end users get to create requirements and hand them to you, and you don't get to make any requests of them in response, in essence your services are "free". Whenever you offer something for free, you are going to get a somewhat irrational response, people are really effected by the word or concept of "free". Dan Ariely writes about this concept in his book "Predictably Irrational", he shows us through experimentation that when the price is free, the demand will increase incredibly fast, and at the expense of reasonable decision making. I think this holds true in most transactions, whether its free cell phones or free software, if it's free demand will increase beyond what could be expected from otherwise rational consumers. to quote Dan Ariely (@danariely):

... First of all, let me say that there are many times when getting FREE! items can make perfect sense. If you find a bin of FREE! athletic socks at a department store, for instance, there is no downside to grabbing all the socks you can. The critical issue arises when FREE! becomes a struggle between a free item and another item - a struggle in which the presence of FREE! leads us to make a bad decision. *

I have never seen this concept more ably demonstrated than I have working in a large corporate IT department. There are other examples, for example this post on SQLServerCentral's forums is a great example of exactly the same thing happening but in a smaller company

Amazing what a little regulation does for requirements! After looking into PCI requirements, I also found that CSC (or other authentication methods) can never be stored. So when I brought this to the attention of the department head and suggested we look into our options with the lawyer, he quickly rescinded that particular requirement. Turns out, the CSC is not required by our CC processing software, that requirement was just put there "just in case we ever needed it."  (full thread here)

 This is a great example, since there was no cost involved, an unnecessary and in this case legally precarious, requirement was thrown in 'just in case'. For those suffering under the pile of "nice to haves" and "just in case" requirements and constant revisions, what can be done? I have found that even implementing a nominal cost for IT services can have a big impact on the way your customers will view your services. When it comes to nominal cost, it does not have to be monetary, it can be additional regulatory overheard, legal or financial risk, or an unacceptable increase in time lines that brings the needed 'costs' into the equation. The example from the recent forum thread above did not involve any financial costs, the cost was in the potential legal exposure, and the cost was too great. So how does one go about implementing a cost structure in a corporate environment that realistically does not allow you to actually charge money for your interdepartmental services?
If there is a cost center or ledger you can charge, even a nominal amount, then do it. Having to reconcile charges made between departments really isn't money being 'spent' by the company, however having to justify the expense is usually costly enough in terms of hours and headaches to really give someone pause as to whether the expenditure is necessary. I have seen this implemented and work with surprising effectiveness, if the only rationale for requesting something is "why not", then all you need to do is supply that reason. If the concept of charging for a service won't work in your workplace, then charge your customers in a different way, with time. If a requirement is changed or added last minute, address it with the requester by showing him/her what else you are working on and what will suffer delays because of this change, your charging them the opportunity cost of this new item. By having to look over everything they are asking of you and weigh the new requirement against the others, value judgments will be made and unimportant items eliminated quickly.

If you are dealing with a customer or client that is not internal, but are actual customers in the true sense of the word, then it can be trickier to handle. as evidenced here on TechCrunch in a post by @JasonKincaid
there can be a large cost to FREE! promotions, including the overhead to support additional users both with bandwidth/hardware/storage type needs, as well as the user support requirements of exponentially growing a user base that needs support but has paid you nothing in return .In this case there may be a simple answer, stop giving your app away free and you wont have these problems, but if these freeloaders are also paying customers of another of your products or services, alienating them could be costly. You will need or determine whether the traffic and 'exposure' your brand or product is getting has any payoff down the road.

So when you are considering your next set of requirements or thinking of offering a service or product for free, ask your potential customers or users, what is this worth to you? If they can't say, be prepared to propose a value yourself and be confident in asking for compensation for your hard work, and both you and your consumer will be satisfied that value is being exchanged for value.

*Ariely, Dan Predictably Irrational. New York: Harper Perennial 2010
Predictably Irrational, Revised and Expanded Edition: The Hidden Forces That Shape Our Decisions

If you found this interesting or useful, please click the +1 to share (it's FREE!) -- thank you

Deadlock and blocking in SQL Server - Part 2

With SQL Server 2005 a set of dynamic management views were introduced to allow you better visibility into what exactly was happening within the instances of SQL Server you manage or use. The below script takes the outdated sys.sysprocesses and fn_get_sql functions (which still work but are deprecated) and replaces them with new DMVs sys.dm_exec_requests and sys.dm_exec_sql_text.
Additionally it more narrowly defines the SQL statement being executed by both the blockee and blocker by trimming down the full text of the script or procedure and only returning the actual code currently executing. This is done by using the statement_start and statement_end offset values in the sys.dm_exec_requests view.
There is a lot more information available on what currently executing statements are doing within your instance, but this script will get you some of the most important information about who is blocking who quickly. I will expand on this again in the near future with more details about what resources are being contested, and other metrics.

/*
   Created: October 2012
   Author: SQLWorks blog
   URL: http://www.SQLWorks.blogspot.com
   Changelog:
   10/17/2012 - replaced sysprocesses with new DMV sys.dm_exec_requests
              - replaced fn_get_sql with new DMV dm_exec_sql_text
              - Used SUBSTRING and statement_offet values to determine exact statement that               is currently executing
              - instead of returning entire command or proceure
*/

DECLARE @sql_handle TABLE(blockee_spid INT, blockee_cmd VARCHAR(MAX), blocker_spid INT, blocker_cmd VARCHAR(MAX))
INSERT INTO @sql_handle
   SELECT sp1.session_id AS blockee_spid,

           SUBSTRING(blockee_cmd.TEXT,
               CASE WHEN sp1.statement_start_offset >= 0 THEN sp1.statement_start_offset
                   ELSE 0 END,
               CASE WHEN (sp1.statement_end_offset-sp1.statement_start_offset) >= 0 THEN                      (SELECT LEN(TEXT) FROM sys.dm_exec_sql_text(sp1.sql_handle))
                   ELSE (sp1.statement_end_offset-sp1.statement_start_offset) END),

           sp1.blocking_session_id AS blocker_spid,

           SUBSTRING(blocker_cmd.TEXT,
               CASE WHEN sp2.statement_start_offset >= 0 THEN sp2.statement_start_offset
                   ELSE 0 END,
               CASE WHEN (sp2.statement_end_offset-sp2.statement_start_offset)<= 0 THEN          (SELECT LEN(TEXT) FROM sys.dm_exec_sql_text(sp2.sql_handle))
                   ELSE (sp2.statement_end_offset-sp2.statement_start_offset) END) AS blocker_cmd

       FROM sys.dm_exec_requests sp1
           JOIN sys.dm_exec_requests sp2
           ON sp1.blocking_session_id = sp2.session_id
       CROSS APPLY sys.dm_exec_sql_text((sp1.sql_handle)) AS blockee_cmd
       CROSS APPLY sys.dm_exec_sql_text((sp2.sql_handle)) AS blocker_cmd
       WHERE sp1.blocking_session_id <> 0

SELECT *FROM @sql_handle

Using the same test data from Part 1 of this post, I added a second simple SELECT query that would be blocked by the update loop and then ran the above statement. The results show the way that the statements are blocked by each other.

What you can see here is that spid 52 (the update loop) is blocking spid 56 (the first SELECT) and then spid 56 is blocking spid 53 (the second SELECT). These results are a bit hard to look at, so in the next post I will modify how they are displayed and get a more usable result set returned.

Go back to Part 1 - blocking and deadlocks

If you found this useful please click the +1 to share - its FREE!

Deadlocks and blocking in SQL Server

As a DBA you are probably faced with the issue of users blocking each other's queries in your databases and potentially deadlocks between queries as well. Here is how I identify and resolve those issues. This method is simple and quick and probably all you need for most situations, there are much more elaborate ways of tracing and troubleshooting these, but for user queries and in-the-moment identification of issues, this way almost always works.

First, the difference between blocking or contention and a deadlock in simple terms, blocking is when one spid or query blocks another from using a resource or placing the lock it needs on said resource, and a deadlock is when two users or queries each holds a resource the other needs next and neither will let go, so the engine chooses a 'victim' and ends the standoff by killing one query. Again this is really simplified but its all you need to know to get started. Now how do we identify when each scenario is happening?

Lets set up some test data first:

/* Created: October 2012
   Author: SQLWorks Consulting blog
   URL: http://www.SQLWorks.blogspot.com
*/
CREATE TABLE [dbo].[EmployeePerf](
   [EmployeeID] [int] NOT NULL,
   [EmployeeName] [varchar](25) NOT NULL,
   [Sales] [int] NULL,
   [TimePeriod] [int] NULL
)
-- test data
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',100,'1')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',300,'2')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',200,'3')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',150,'4')  

This is just a table I had set up already on my server to test something else, so I am reusing it, the contents of the table are not really relevant to the discussion on database contention.

So now we need two queries, one to block the other for our testing:
-- Looping update to create a lock for demonstration
DECLARE @sales INT = 100
BEGIN TRAN
WHILE @sales < 500000
BEGIN
   UPDATE [dbo].[EmployeePerf]
   SET sales = @sales
       WHERE Employeename = 'tony' AND timeperiod = 1

   SET @sales = @sales +1
CONTINUE
END

COMMIT TRAN

and in a second window, you will run this concurrently and it will be blocked by the first:

-- select statement that will be blocked by above update statement for demonstration
-- Run this in a different query window!
SELECT *
   FROM dbo.EmployeePerf

So now that we have blocking occurring, what do we do about it? Well first I always like to know who is blocking who, and the commands being attempted by each party. To find that out there are several methods, here is the one I use in SQL Server 2012:

DECLARE @sql_handle TABLE(blockee_spid INT, blockee_cmd VARCHAR(MAX), blocker_spid INT, blocker_cmd VARCHAR(MAX))INSERT INTO @sql_handle
   SELECT sp1.spid AS blockee_spid, blockee_cmd.TEXT AS blockee_cmd, sp1.blocked AS blocker_spid, blocker_cmd.TEXT AS blocker_cmd
       FROM sys.sysprocesses sp1
           JOIN sys.sysprocesses sp2
           ON sp1.blocked = sp2.spid
       CROSS APPLY sys.dm_exec_sql_text((sp1.sql_handle)) AS blockee_cmd --updated
       CROSS APPLY sys.dm_exec_sql_text((sp2.sql_handle)) AS blocker_cmd --updated
       WHERE sp1.blocked <> 0

SELECT *FROM @sql_handle  

This uses the sys.sysprocesses system view to see what is blocking and being blocked and then calls the fn_get_sql function for each to determine what code is being run. If you run the two queries above, then run this script in a third window you will get the below results:

As you can see, it provides the spid of the blocker and blockee, as well as the commands being run by each. There is so much more to delve into on this subject but this will get you moving, and give you a quick way to answer the recurring user question we all love "Who is blocking my query?!?!"


-- EDIT - updated with suggestions from @AaronBertrand of SQLBlog.com
-- changed fn_get_sql to sys.dm_exec_sql_text

-- EDIT - changed formatting of code so comment lines didn't run over code
  -- EDIT - again fixed weird formatting in the code snippets

coming tomorrow, Part 2 - new version using dm views

If you found this useful, click the +1 or Twitter buttons to share -- thanks

SQL code format for web pages

Just found this piece of genius: http://extras.sqlservercentral.com/prettifier/prettifier.aspx 

Paste your code and it will format it to appear like it does in SSMS, and supply you with the HTML. I know there are many, many sites that offer this, but this one actually gets it right according to SSMS colorization. Thank you SQL Server Central.

Logitech K810 - one keyboard for phone, tablet and computer

I love this idea, but more for the fact that I could pair both my laptops to this one keyboard and switch between them without having to plug/unplug anything. It's a keyboard that can be paired with up to three devices, including computers, tablets and phones, and hot swapped between them.

Logitech K810 write up from geekbeat.com

They are not out yet, full details from Logitech here:
Logitech Product page
But preorders are available for $99 USD, no number pad, but I might still sign up

You probably need a new keyboard

Need a new keyboard? So did I, so I checked a few out, went to Best Buy and opened a lot of boxes to test the keys, went to Staples and did the same, then shopped around Newegg, TigerDirect and Amazon looking for the best prices of course, and I ended up with an HP Elite Wireless V2 keyboard (ended up getting it at Best Buy). I may have some bias here because I have an HP Elite book that I like a lot, but this is one of the best keyboards I have ever used. It is very thin, but very rigid and strong, the keys are easy to use and feel great over long periods of use (8+ hours a day). If you haven't gotten a new keyboard lately, do yourself a favor and get a better one, if you are a professional and spend 8 or more (sometimes many more) hours in front of the keyboard every day you owe it to yourself to get a decent one. The link in this image is the best price I could find.

The Elite V2 is also very thin and light, you could bring it with you on a plane if you had to and you really wouldn't be giving up much space in your carry on. Overall this seems to be the best value in this price range, you can spend a hell of a lot more if you really wanted to (see example below) but for someone who is just looking for a tool to do their work, this is the way to go in my opinion.

For those who have more money than sense, please click here:

How is the role of the 'Analyst' changing in a data driven world?

     I remember the days when I was considered a top notch resource when it came to answering data driven questions. My Excel sheets were sparkling, my PowerPoint slides were beautiful, and my MS Access database was driving it all. In those days, when you needed to know how many widgets the sales team had sold, you asked them for their results sheet, and probably received it by email. When you needed to see attendance records for the call centers, they replied with their own spreadsheet, and a few paper based updates perhaps. After you had all your data collected and entered or imported to Access you could do with it what you pleased, and get some results worth sharing. But those days are gone, over the last 10 years I have witnessed an amazing transition from the scenario I describe above to the new paradigm of the 'Analyst' as power user and SQL dabbler. The data companies house internally is larger and more broad than ever before and growing quickly, and in order to present informed conclusions about a business, market or customer group, you need to be able to intelligently ask questions of these large stores of data.
     Enter the new 'Analyst', someone who is expected to know how to interact with databases like never before and to collate and present data from varied sources. Where a job description from those bygone days might have read "requires advanced MS Office suite knowledge" it is typical now to see requirements similar to "Must be familiar with SQL querying and tools such as SQL Server, Toad for SQL or PC SAS in order to gather results".

Searching Across SQL Server Part 3

To give other users access to use your SQL search script, but keep it centrally managed and compiled on your server, you need to implement it as a stored procedure or function. The choice between stored procedure and function for most installations is largely personal preference, I chose to go with a stored procedure, if you're interested in the relative merits of each there is some good reading here:

I have placed the code for the full stored procedure on git hub here: GitHub Full Procedure Code
I will only display the changes I made in the interest of keeping this post from filling your screen with code.
/*
Created by SQLWorks blog 2012
http://SQLWorks.blogspot.com
SQLWorkers@gmail.com
*/ 

CREATE PROCEDURE Usp_search @search_str VARCHAR(1000)
AS
    SET nocount ON 

...

I added the CREATE statements in the beginning, and made a couple of tweaks to the dynamic SQL in order to prevent duplicate results, see comments in the full code for details.

The usage is different of course, now you will use EXECUTE to run your new procedure and pass in the string you are searching for as a parameter as shown below.

EXECUTE Usp_search 'Employees' 


Which yields the following results showing this keyword is present in two object names:

Let's pass in a keyword present in column names:

EXECUTE Usp_search 'EmployeeID' 

The results yielded are shown below:

Now you can incorporate this procedure into jobs or other processes on your database server or in your applications, or base a report on it to allow users to search for items in your databases as well.

Part 4 coming up next, creating an SSRS report around this procedure

Go back to Part 1
Go back to Part 2

NTILE ranking functions in SQL Server

Following the previous post on the ROW_NUMBER function, lets look at other rank type functions in SQL Server and how you can use them.
Books Online does a good job of explaining the syntax, so I will link to it here and not go into too much detail beside to place this link. Ranking functions via MSDN

So, now that you have the syntax, where would you use these functions? what kind of questions can they help you answer with your data? Here is a scenario, suppose your boss asks you to apply the 10-80-10 rule (placing employees into performance groups comprising 10% (high) 80% (medium) 10% (low) ) to a group of employees every week, so they can evaluate who is performing above and below expectations. You could use any of the ranking functions to achieve this, but the quartile function works the best because it will always divide the group of employees into the number of quartiles you specify, so if people come and go you wont have to worry about counting how many are in each rank to determine over all percentile. Example below.

Here is the test data first:

CREATE TABLE dbo.EmployeePerf
(
employeeid INT NOT NULL,
employeename VARCHAR(25) NOT NULL,
sales INT,
timeperiod INT
)

INSERT INTO employeeperf VALUES (1, 'Tony', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Tony', 300, 2)

INSERT INTO employeeperf VALUES (1, 'Tony', 200, 3)

INSERT INTO employeeperf VALUES (1, 'Tony', 150, 4)

INSERT INTO employeeperf VALUES (1, 'Meg', 900, 1)

INSERT INTO employeeperf VALUES (1, 'Meg', 400, 2)

INSERT INTO employeeperf VALUES (1, 'Meg', 500, 3)

INSERT INTO employeeperf VALUES (1, 'Meg', 850, 4)

INSERT INTO employeeperf VALUES (1, 'Ben', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Ben', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Ben', 50, 3)

INSERT INTO employeeperf VALUES (1, 'Ben', 90, 4)
INSERT INTO employeeperf VALUES (1, 'Jim', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Jim', 3000, 2)

INSERT INTO employeeperf VALUES (1, 'Jim', 2000, 3)

INSERT INTO employeeperf VALUES (1, 'Jim', 1500, 4)

INSERT INTO employeeperf VALUES (1, 'Abigail', 90, 1)

INSERT INTO employeeperf VALUES (1, 'Abigail', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Abigail', 500, 3)

INSERT INTO employeeperf VALUES (1, 'Abigail', 80, 4)

INSERT INTO employeeperf VALUES (1, 'Mike', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Mike', 840, 2)

INSERT INTO employeeperf VALUES (1, 'Mike', 350, 3)

INSERT INTO employeeperf VALUES (1, 'Mike', 190, 4)

INSERT INTO employeeperf VALUES (1, 'John', 10, 1)

INSERT INTO employeeperf VALUES (1, 'John', 30, 2)

INSERT INTO employeeperf VALUES (1, 'John', 20, 3)

INSERT INTO employeeperf VALUES (1, 'John', 10, 4)

INSERT INTO employeeperf VALUES (1, 'Deb', 500, 1)

INSERT INTO employeeperf VALUES (1, 'Deb', 400, 2)

INSERT INTO employeeperf VALUES (1, 'Deb', 200, 3)

INSERT INTO employeeperf VALUES (1, 'Deb', 180, 4)

INSERT INTO employeeperf VALUES (1, 'Peter', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Peter', 740, 2)

INSERT INTO employeeperf VALUES (1, 'Peter', 650, 3)

INSERT INTO employeeperf VALUES (1, 'Peter', 890, 4)

INSERT INTO employeeperf VALUES (1, 'Bill', 230, 1)

INSERT INTO employeeperf VALUES (1, 'Bill', 450, 2)

INSERT INTO employeeperf VALUES (1, 'Bill', 245, 3)

INSERT INTO employeeperf VALUES (1, 'Bill', 180, 4)

INSERT INTO employeeperf VALUES (1, 'Julie', 50, 1)

INSERT INTO employeeperf VALUES (1, 'Julie', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Julie', 20, 3)

INSERT INTO employeeperf VALUES (1, 'Julie', 10, 4)

INSERT INTO employeeperf VALUES (1, 'Paul', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Paul', 700, 2)

INSERT INTO employeeperf VALUES (1, 'Paul', 600, 3)

INSERT INTO employeeperf VALUES (1, 'Paul', 590, 4)

Now to divide these employees up into the requested 10-80-10 we can apply the NTILE() function like this:

SELECT employeeid, employeename, sales, timeperiod, Ntile(10) OVER (ORDER BY sales) AS Quartile
  FROM employeeperf 
  WHERE timeperiod = 1

This gives us our results, the employees group in 10% increments by sales for the time period we care about, but what if you don't want to re-run it for each time period, and you only want to display those in the top and bottom 10% brackets? A common need for performance management, lets change the code to the following:

;WITH salescte 
AS 
( 
SELECT employeeid, employeename, sales, timeperiod, 
     Ntile(10) OVER (partition BY timeperiod ORDER BY sales) AS Quartile  -- added PARTITION BY
  FROM employeeperf 
  --WHERE TimePeriod = 1    <-- removed to include all periods 
) 

SELECT employeeid, employeename, sales, timeperiod, quartile 
  FROM salescte 
  WHERE quartile IN (1, 10) 
    ORDER BY timeperiod, quartile ASC


And the results are exactly what we wanted, the top and bottom 10%

My random test data is a bit skewed, as you can see some sales are radically different, if you were in a more normalized environment the number of people in each quartile would be more even. Hope this is useful.