Since the release of SQL Server 2011 – Denali, there has been a lot of curiosity about the changes in wait types compared to SQL Server 2008 R2. In this article, we will discuss some of the wait types that exist in SQL Server 2011 – Denali but not in SQL Server 2008 R2, as well as wait types that exist in SQL Server 2008 R2 but not in SQL Server 2011 – Denali.
Wait Types Exists in SQL Server 2008 R2 and Not Exists in SQL Server 2011 – Denali
- SOS_RESERVEDMEMBLOCKLIST
- SOS_LOCALALLOCATORLIST
- QUERY_WAIT_ERRHDL_SERVICE
- QUERY_ERRHDL_SERVICE_DONE
- XE_PACKAGE_LOCK_BACKOFF
Wait Types Exists in SQL Server 2011 – Denali and Not Exists in SQL Server 2008 R2
- SLEEP_MASTERMDREADY
- SOS_MEMORY_TOPLEVELBLOCKALLOCATOR
- SOS_PHYS_PAGE_CACHE
- FILESTREAM_WORKITEM_QUEUE
- FILESTREAM_FILE_OBJECT
- FILESTREAM_FCB
- FILESTREAM_CACHE
- XE_CALLBACK_LIST
- PWAIT_MD_RELATION_CACHE
- PWAIT_MD_SERVER_CACHE
- PWAIT_MD_LOGIN_STATS
- DISPATCHER_PRIORITY_QUEUE_SEMAPHORE
- FT_PROPERTYLIST_CACHE
- SECURITY_KEYRING_RWLOCK
- BROKER_TRANSMISSION_WORK
- BROKER_TRANSMISSION_OBJECT
- BROKER_TRANSMISSION_TABLE
- BROKER_DISPATCHER
- BROKER_FORWARDER
- UCS_MANAGER
- UCS_TRANSPORT
- UCS_MEMORY_NOTIFICATION
- UCS_ENDPOINT_CHANGE
- UCS_TRANSPORT_STREAM_CHANGE
- QUERY_TASK_ENQUEUE_MUTEX
- DBCC_SCALE_OUT_EXPR_CACHE
- PWAIT_ALL_COMPONENTS_INITIALIZED
- PREEMPTIVE_SP_SERVER_DIAGNOSTICS
- SP_SERVER_DIAGNOSTICS_SLEEP
- SP_SERVER_DIAGNOSTICS_INIT_MUTEX
- AM_INDBUILD_ALLOCATION
- QRY_PARALLEL_THREAD_MUTEX
- FT_MASTER_MERGE_COORDINATOR
- PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC
- REDO_THREAD_PENDING_WORK
- REDO_THREAD_SYNC
- COUNTRECOVERYMGR
- HADR_DB_COMMAND
- HADR_TRANSPORT_SESSION
- HADR_CLUSAPI_CALL
- PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC
- PWAIT_HADR_ACTION_COMPLETED
- PWAIT_HADR_OFFLINE_COMPLETED
- PWAIT_HADR_ONLINE_COMPLETED
- PWAIT_HADR_FORCEFAILOVER_COMPLETED
- PWAIT_HADR_WORKITEM_COMPLETED
- HADR_WORK_POOL
- HADR_WORK_QUEUE
- HADR_LOGCAPTURE_SYNC
- LOGPOOL_CACHESIZE
- LOGPOOL_FREEPOOLS
- LOGPOOL_REPLACEMENTSET
- LOGPOOL_CONSUMERSET
- LOGPOOL_MGRSET
- LOGPOOL_CONSUMER
- LOGPOOLREFCOUNTEDOBJECT_REFDONE
- HADR_SYNC_COMMIT
- HADR_AG_MUTEX
- PWAIT_SECURITY_CACHE_INVALIDATION
- PWAIT_HADR_SERVER_READY_CONNECTIONS
- HADR_FILESTREAM_MANAGER
- HADR_FILESTREAM_BLOCK_FLUSH
- HADR_FILESTREAM_IOMGR
- XDES_HISTORY
- XDES_SNAPSHOT
- HADR_FILESTREAM_IOMGR_IOCOMPLETION
- UCS_SESSION_REGISTRATION
- ENABLE_EMPTY_VERSIONING
- HADR_DB_OP_START_SYNC
- HADR_DB_OP_COMPLETION_SYNC
- HADR_LOGPROGRESS_SYNC
- HADR_TRANSPORT_DBRLIST
- HADR_FAILOVER_PARTNER
- XDESTSVERMGR
- GHOSTCLEANUPSYNCMGR
- HADR_AR_UNLOAD_COMPLETED
- HADR_PARTNER_SYNC
- HADR_DBSTATECHANGE_SYNC
Wait types and wait stats are important aspects of SQL Server performance tuning. Understanding the changes in wait types between different versions can help DBAs and developers optimize their queries and improve overall system performance.
It is important to note that the information presented here is based on the author’s experience and may not be accurate for all systems. It is recommended to refer to the official documentation for further clarification and to test any changes on a development server before implementing them in a production environment.
Stay tuned for more articles on wait types and other SQL Server concepts!