SQL DBA Blog: Home

PREEMPTIVE_OS_QUERYREGISTRY is a generic wait for threads calling Windows registry functions. SQL Server consults the registry for assorted configuration (service settings, network configuration, some feature lookups), and xp_regread and friends route here too. The thread runs preemptively until the registry call returns. Normally a background triviality, with one version-specific exception worth knowing. Is It a…

read more

PREEMPTIVE_OS_REPORTEVENT is recorded when a thread calls the Windows ReportEvent function, writing an entry to the Windows event log. SQL Server mirrors significant error log messages to the Application event log, and features like RAISERROR … WITH LOG, failing logins (when auditing is on), and alert-generating errors all route through this call. Its accumulation is…

read more

Windows authentication and authorization involve a parade of security APIs, and SQL Server has a preemptive wait for each. This page covers the family: PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT, ACQUIRECREDENTIALSHANDLE, AUTHORIZATIONOPS, AUTHZGETINFORMATIONFROMCONTEXT, AUTHZINITIALIZECONTEXTFROMSID, AUTHZINITIALIZERESOURCEMANAGER, COMPLETEAUTHTOKEN, DECRYPTMESSAGE, ENCRYPTMESSAGE, DELETESECURITYCONTEXT, FREECREDENTIALSHANDLE, INITIALIZESECURITYCONTEXT, QUERYCONTEXTATTRIBUTES, QUERYSECURITYCONTEXTTOKEN, REVERTTOSELF, SECURITYOPS, SETNAMEDSECURITYINFO, NETVALIDATEPASSWORDPOLICY, and NETVALIDATEPASSWORDPOLICYFREE. The SSPI negotiation members (ACCEPTSECURITYCONTEXT, INITIALIZESECURITYCONTEXT, COMPLETEAUTHTOKEN) fire on every Kerberos/NTLM…

read more

PREEMPTIVE_OS_WAITFORSINGLEOBJECT is recorded when a thread calls the Windows WaitForSingleObject function to synchronise with an external client process communicating through that kernel object. In practice it is commonly seen together with ASYNC_NETWORK_IO, depending on the network transport in use between SQL Server and its clients: the same slow-consumer condition can express itself through either wait.…

read more

PREEMPTIVE_SB_STOPENDPOINT is recorded when a thread calls out to Windows while shutting down a Service Broker endpoint, the network listener that lets Broker exchange messages between instances. Stopping an endpoint involves OS-level teardown of its networking, done preemptively. Endpoint stops happen at instance shutdown and when someone alters or drops the endpoint, so occurrences are…

read more

PREEMPTIVE_SP_SERVER_DIAGNOSTICS is recorded when the background health-monitor thread is actually running the sp_server_diagnostics procedure, the component-by-component health check added in SQL Server 2012 that feeds AG and FCI failure detection. Its sibling SP_SERVER_DIAGNOSTICS_SLEEP covers the same thread between runs; this preemptive wait covers the runs themselves. Between the two, the health monitor’s entire life is…

read more

Five preemptive waits cover Extended Events session lifecycle operations, and this page covers the set: They meter the administrative edges of Extended Events rather than event capture itself. Are They a Problem? No; none has been a contention point, and their occurrences map to session lifecycle events and target inspections: someone started or stopped a…

read more

PRINT_ROLLBACK_PROGRESS is recorded by a session that has issued ALTER DATABASE … WITH ROLLBACK IMMEDIATE (or ROLLBACK AFTER n) and is now waiting for the user transactions it killed to finish rolling back. The thread sleeps in three-second loops, checking progress, until every victim has undone its work and the database can transition state. The…

read more

PVS_PREALLOCATE is recorded by the background task that preallocates space in the Persistent Version Store (PVS) for databases with Accelerated Database Recovery (ADR) enabled. The task adapts to demand: on a quiet database it wakes every five minutes (accruing five-minute waits); under heavy versioning activity it runs far more often with much shorter waits, throttling…

read more

PWAIT_DIRECTLOGCONSUMER_GETNEXT is recorded when a thread reading from the transaction log waits for a log block to be supplied to it. The direct log consumer interface serves the components that stream the log as it is produced, Availability Group capture being the flagship, and a consumer that has caught up to log generation waits here…

read more