Since the beginning of the series, I have been getting the following question again and again:

“What are the changes in SQL Server 2011 – Denali with respect to Wait Types?”

SQL Server 2011 – Denali is yet to be released, and making statements on the subject will be inappropriate. Denali CTP1 has been released so I suggest that all of you download the same and experiment on it. I quickly compared the wait stats of SQL Server 2008 R2 and Denali (CTP1) and found the following changes:

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 and Not Exists in SQL Server 2008

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

We already know that Wait Types and Wait Stats are going to be the next big thing in the next version of SQL Server. So now I am eagerly waiting to dig deeper in the wait stats.

Read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)