Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting Four preemptive waits track Windows networking APIs, and this page covers them together: Together they are the OS-call basement under SQL Server’s outbound and transport networking. Are They a Problem? At trace levels, no.…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting PREEMPTIVE_OS_PIPEOPS is recorded when a thread calls out to Windows functions that work with pipes, and in DBA reality that means one thing almost every time: xp_cmdshell. SQL Server launches the external command and…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting 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,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting 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…