Patiently Waiting
This is just a quick post to list out new wait stats in SQL Server 2022. How many will be useful, time will tell.
Some waits that I think might be interesting:
- CXSYNC_CONSUMER (Currently in Azure)
- CXSYNC_PORT (Currently in Azure)
- PARALLEL_DB_SEEDING_SEMAPHORE
- PLPGSQL
It looks like maybe automatic seeding for Availability Groups is getting the ability to process multiple databases at once, and we’re getting some parallel query waits that used to be Azure only.
I’m really scratching my head about PLPGSQL though. That’s the “programming language” that Postgres supports, which is sort of like what Oracle supports.
Time will tell!
+-----------------------------------------------------+ | wait_type | +-----------------------------------------------------+ | ARC_IMDS_RESOURCE_INFO | | BABYLON_POLICY_UPDATE | | BABYLON_PULL_TASK | | BACKUP_BACKUP_MGR_MIHYBRIDINFO_RWLOCK | | BACKUP_LOG_IO_STALL | | BLOB_LIST_LIMIT_IO_REQUESTS | | BLOB_LIST_RWLOCK | | BUFFERPOOL_SCAN | | CDC_SCHEDULERCACHE_ACCESS | | CDC_THROTTLE_LOG_RATE_LOG_SIZE | | CMEMDETOUR | | COLLECTOR_VIEW_LIST | | COLUMNSTORE_CSI_CACHE | | COSMOSDB_INIT_MUTEX | | CXSYNC_CONSUMER | | CXSYNC_PORT | | DATA_EXPORT_COMPLETION_SYNC | | DIFF_BACKUP_SELF_THROTTLING | | DIRECTORY_CONTENT_LIST_CLERK | | DIRTY_PAGE_THROTTLING | | DISPATCHER_JOIN | | DTC_INFO_DMV | | DTC_PRECOMMIT | | DTCNEW_DWSHELLDB_PROPERTIES | | EDC_DOPP_BACKGROUND | | EDC_DOPP_LOCK | | EDC_INIT_RWLOCK | | EMC_EXEC | | EMC_FABRIC_RESOLVE_SERVICE_URI | | EMC_INIT_MUTEX | | EXECUTED_REQ_TABLE_STATE_RWLOCK | | EXTERNAL_SCRIPT_CREATE_CERTIFICATE | | EXTERNAL_SERVICE_BLOB_MESSAGE | | EXTERNAL_SERVICE_CONNECTION_CLOSE | | EXTGOV_ATTR_SYNC_BACKGROUND | | FABRIC_ENDPOINT_SYNC_EVENT | | FCB_DISKSPACE_COUNTERS_RWLOCK | | FIDO_AUTOSTATISTICS_TASK | | FIDO_CLIENT_STARTUP | | FOREIGN_FILE_VALIDATION | | FULL_BACKUP_SELF_THROTTLING | | GLOBAL_QUERY_EXTRACTOR_EXECUTE | | HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO | | HADR_THROTTLE_LOG_RATE_SLO_DOWNGRADE | | HADR_THROTTLE_REFRESH_MAX_SIZE | | HTTP_EXTERNAL_CONNECTION | | HTTP_EXTERNAL_CONNECTION_ALLOW_LIST | | HTTP_EXTERNAL_CONNECTION_IPV4_BLOCK_LIST | | INDEX_BUILD_BUCKETIZATION_BARRIER | | INDEX_BUILD_BUCKETIZATION_INFO_MAP_SYNC | | INDEX_BUILD_BUCKETIZATION_INFO_SYNC | | LCK_REQ_TSK_PROXY | | LEDGER_BLOCK_GENERATION | | LEDGER_TRUNCATION | | LOCK_SAFEMODE | | LOCK_UPDATE | | LOGPOOL_CONSUMER_DELETABLE | | LSN_LOC_MAP_LOCK | | MANAGED_DISKS_CONFIGURATION | | MULTITHREADED_VERSION_CLEANUP_WAIT_WORK | | NATIVE_SHUFFLE_OPEN_HANDLE | | ORDLOCK_POPULATE_SYNC | | PARALLEL_BPOOL_DEALLOCATION_WORKER | | PARALLEL_DB_SEEDING_SEMAPHORE | | PLPGSQL | | POLARIS_TSQL_TASK | | POPULATE_LOCK_ORDINALS | | PREEMPTIVE_AAD_HTTP_EVENT_WAIT | | PREEMPTIVE_COSMOSDB | | PREEMPTIVE_EMC | | PREEMPTIVE_FILE_MAPPING | | PREEMPTIVE_HTTP_EXTERNAL_CONNECTION_EVENT_WAIT | | PREEMPTIVE_OS_GETQUEUEDCOMPLETIONSTATUS | | PREEMPTIVE_OS_PDH_WMI_QUERY | | PREEMPTIVE_PREDICT_API | | PREEMPTIVE_REPORTING | | PREEMPTIVE_RG_HTTP | | PREEMPTIVE_SNI_SOCKET_BIND | | PREEMPTIVE_SNI_SOCKET_LISTEN | | PREEMPTIVE_SYNAPSESTREAMING_HTTP_EVENT_WAIT | | PREEMPTIVE_XCS_SNAPPY | | PREEMPTIVE_XCS_THRIFT | | PRU_PAGE_LSN_CACHE_LOCK | | PVS_TRACK_PAGES_MUTEX | | PWAIT_AUTO_START_AUDIT_SESSIONS | | PWAIT_DBCC_FREEZEIO_MUTEX | | PWAIT_DBCC_THAWIO_MUTEX | | PWAIT_EXTERNAL_SERVICE_HUB | | PWAIT_EXTERNAL_SERVICE_SEND_MESSAGE | | PWAIT_FCS_MD_READ_AHEAD | | PWAIT_FIDO_INDEXSTORE_CONNECTIONS_MANAGER_HASHTABLE | | PWAIT_LATCH_ONLY | | PWAIT_PREEMPTIVE_OS_AUTHENTICATEDWEBCALL | | PWAIT_PREEMPTIVE_OS_AUTHENTICATIONTOKEN | | PWAIT_PREEMPTIVE_OS_CRYPTOPENSTORAGEPROVIDER | | PWAIT_PREEMPTIVE_OS_VSMATTESTATIONSERVICE | | PWAIT_PS_RBPEX_HOT_PAGES_RWLOCK | | PWAIT_RBIO_IC_ACQUIRE_PAYLOAD | | PWAIT_SBS_API_STATS_PUBLISH | | PWAIT_SBS_IOAPI_STATS_PUBLISH | | PWAIT_SYNAPSE_LINK_CAPTURE | | PWAIT_SYNAPSE_LINK_COMMIT | | PWAIT_SYNAPSE_LINK_DATA_EXPORT_SESSION | | PWAIT_SYNAPSE_LINK_DB_CLEANUP | | PWAIT_SYNAPSE_LINK_END_HISTORY_SESSION | | PWAIT_SYNAPSE_LINK_GET_CURRENT_DB_LSN | | PWAIT_SYNAPSE_LINK_GET_DB_LIST, | | PWAIT_SYNAPSE_LINK_GET_TABLE_HASHTABLE, | | PWAIT_SYNAPSE_LINK_LZN_API_CALL | | PWAIT_SYNAPSE_LINK_MEM_CAP_THROTTLE | | PWAIT_SYNAPSE_LINK_POPULATE_METADATA | | PWAIT_SYNAPSE_LINK_PUBLISH | | PWAIT_SYNAPSE_LINK_UPDATE_TABLE_STATUS | | PWAIT_TOAD_CELL_ZONE | | PWAIT_TOAD_DELTA_FORCE_ZONE | | PWAIT_TOAD_OCCI_ZONE | | PWAIT_TOAD_STAR_CELL_ZONE | | PWAIT_TOAD_TUNING_ZONE | | PWAIT_VLDB_PLANNED_FAILOVER_FORWARDER_THROTTLING | | PWAIT_VLDB_PLANNED_FAILOVER_START_THROTTLING | | PWAIT_VLDB_PLANNED_FAILOVER_STOP_THROTTLING | | PWAIT_XIO_REQUEST_IN_PROGRESS_LOCK | | PWAIT_XLOG_POOL_EVICT_SLEEP | | RBIO_AWAIT_RESPONSE | | RBIO_COMM_RETRY | | RBIO_COMM_UNINITIALIZE | | RBIO_COMPLETE_LOG_READ | | RBIO_CONNECTION_MGR | | RBIO_DB_RESTART | | RBIO_DB_TRANS_PRIMARY | | RBIO_DBTRANSPRIMARY_SYNC | | RBIO_FCB_DEFERRED_IO | | RBIO_FCB_DEFERRED_IO_FN | | RBIO_INITIALIZE_MUTEX | | RBIO_PS_ACTOR_COLLECTION_RWLOCK | | RBIO_RG_DESTAGE | | RBIO_RG_GEOREPLICA | | RBIO_RG_LOCALDESTAGE | | RBIO_RG_MIGRATION_TARGET | | RBIO_RG_REPLICA | | RBIO_RG_STORAGE | | RBIO_RG_STORAGE_CHECKPOINT | | RBIO_UNINITIALIZE | | RBPEX_CHANGE_FILE_SIZE_MUTEX | | RBPEX_CREATESNAPSHOT_RETRY | | RBPEX_WRITEBEHIND_DB_STATE | | RBPEXSHRINKTASK_SHUTDOWN | | REPORTING_EXEC | | RG_MANAGER_VHD_GROWTH | | RG_SERVER_CONFIGS | | ROW_GROUP_POPULATION | | SBS_CONTEXT | | SBS_LRU_EVICTION | | SEEDING_COMPLETED_MUTEX | | SEEDING_SELF_THROTTLING | | SESSION_MGR | | SLEEP_RBPEXSHRINKTASK | | SLEEP_SAFEMODE | | SOS_RG_MEM_TARGET_LOCK | | SP_RESOLVE_DEFERRED | | SQLPAL_PREEMPTIVE_WAIT | | SQP_STATS_REPORTING | | START_BACKGROUND_TASK_MUTEX | | STREAMING_SERVICE_RESTART_FINISHED | | STREAMING_SERVICE_SEND_KILL_PROCESS | | STRIPE_META_UPDATE | | SYNAPSELINK_CAPTURE_JOBTASK_ACCESS | | SYNAPSELINK_COMMIT_JOBTASK_ACCESS | | SYNAPSELINK_FAILBATCH_ACCESS | | SYNAPSELINK_PUBLISH_JOBTASK_ACCESS | | SYNAPSELINK_SNAPSHOT_JOBTASK_ACCESS | | TELEMETRY_SNAP | | THROTTLE_LOG_RATE_LOG_STORAGE | | TOAD_AUTOSTATISTICS_ZONE | | TOAD_DELETEBITMAP_ZONE | | TOAD_DISCOVERY | | TOAD_FUTURE | | TOAD_QUEUE | | TOAD_RESOURCE_SEMAPHORE | | TOAD_SHUTDOWN | | VERSION_LEASE_HASH_LOCK | | VLDB_DUMP_LOG_LOCK | | VLDB_SNAPSHOT_MUTEX | | WAIT_AE_KEYADD | | WAIT_AETM_CRITICAL_SECTION | | WAIT_AETM_ENCLAVE_WORKER_SLEEP | | WAIT_AETM_HOST_WORKER_SLEEP | | WAIT_DELTA_CACHE | | WAIT_DW_TX_EXTERNALIZATION_IO_COMPLETION | | WAIT_FCS_MD_RESOLVE | | WAIT_FIDO_GLMS_ASYNC_WORKER | | WAIT_FIDO_GLMS_SYNC | | WAIT_FIDO_GLMS_UT | | WAIT_FOR_MS_POLL | | WAIT_FULLTEXT_CRAWL_MANAGER | | WAIT_GC_IO_COMPLETION | | WAIT_GLM_CONTROLLER | | WAIT_GLM_DB | | WAIT_GLM_DEK_LIST | | WAIT_GLM_SYNC_CLIENT | | WAIT_GLMS_ACCESS | | WAIT_GLMS_LOG | | WAIT_GLMS_LOG_BLOCK_IO | | WAIT_INDEXSTORE_COMPUTE_PARTITION_BUCKETS | | WAIT_INDEXSTORE_LIMIT_REQUESTS | | WAIT_LCKMGR | | WAIT_LM_CREATE | | WAIT_LOG_REPLICA_MGR_HASH_LOCK | | WAIT_LOG_REPLICA_ROLE_STABILITY_LOCK | | WAIT_LOG_REPLICA_WRITE_LEASE_PROPERTY_HASH_LOCK | | WAIT_ODBC | | WAIT_PHYSICAL_CATALOG | | WAIT_RBAC_PROVIDER | | WAIT_RBAC_SESSION_AUDIT | | WAIT_RBAC_SESSIONS_AUDIT | | WAIT_RBPEX_WRITEBEHIND_CKPT_CONSISTENCY_LOCK | | WAIT_RETENTION_POLICY | | WAIT_RM_RBIOCONNECTION_INIT | | WAIT_SEQUENCE | | WAIT_SYNC_LAG_PARTNERS_LIST | | WAIT_TRANSPORT | | WAIT_TSQL_TASK | | WAIT_VLF_IO_TRACKER_DRAIN_IO | | WAIT_XCS_LOCATOR_FETCH | | WAIT_XLOG_REPLICA_BG_TASK_LOCK | | WMI_REGISTRATION | | XDB_PKG_LAUNCHER_CONNECTION | | XE_LIVE_TARGET_MUTEX | | XE_PREDICATE_HEAP_ALLOC | | XE_PREDICATE_HEAP_FREE | | XE_RING_TARGET_MUTEX | | XE_SQL_TEXT_HEAP_ALLOC | | XE_SQL_TEXT_HEAP_FREE | | XE_SQL_TEXT_PREDICATE | | XFILE_CACHE_XACT | | XFILE_DISPATCH | | XFILE_OBJECT_POOL | | XFILE_TASK_PROXY_ABORT | | XLOG_BGTHREAD | | XLOG_BROKER_UNLINKING_IN_PROGRESS | | XLOG_BROKER_WAIT_PAGE | | XLOG_BROKER_WAITFULL | | XLOG_BROKER_WAITMAXALLOWED | | XLOG_EOL_REQUEST_NOTIFICATION | | XLOG_GAPFILLERTHREAD | | XLOG_LC_FWD_SEEDING | | XLOG_LC_REVERSE_SEEDING | | XLOG_LC_SEEDING_VDL_ADVANCE | | XLOG_POOL_FILLER_SLEEP | | XLOG_POOL_QUERY_WAIT | | XLOG_POOL_SHUTDOWN | | XLOG_SPACEMGR_INITIALIZE | | XLOG_TOSFILE_GET_ITER_PROXY | | XLOGREAD_SIGNAL | +-----------------------------------------------------+
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.