preemptive waits sql server

Understanding `PREEMPTIVE` Waits in SQL Server

When working with SQL Server performance tuning, one of the common challenges is identifying and interpreting wait statistics. Waits are a vital diagnostic tool that reveals where SQL Server might be slowing down. Among these, the preemptive waits sql server stand out as a class that often puzzles DBAs. What are they, and how should you handle them?

What Are `PREEMPTIVE_*` Waits?

PREEMPTIVE_* waits occur when SQL Server has to call an external process or resource outside its own scheduling framework, specifically when it interacts with Windows Operating System APIs. Normally, SQL Server uses a cooperative scheduling model called “non-preemptive scheduling,” where its worker threads voluntarily yield execution. However, when SQL Server needs to make a system call, it switches to “preemptive scheduling,” allowing the operating system to take control.

For example, if SQL Server interacts with the file system, Active Directory, or performs a network call, it will use preemptive scheduling. While these waits are typically a small fraction of total waits in a healthy system, excessive PREEMPTIVE_* waits could indicate performance bottlenecks caused by external dependencies.

Common Types of `PREEMPTIVE_*` Waits

Here are some common PREEMPTIVE_* waits and what they signify:

1. `PREEMPTIVE_OS_AUTHENTICATIONOPS`

This occurs when SQL Server is performing Windows Authentication tasks, such as validating logins against Active Directory. If your environment relies heavily on AD and has connectivity issues or delays, you might see this wait.

2. `PREEMPTIVE_OS_FILEOPS`

These waits happen when SQL Server interacts with the file system. It could involve reading or writing files, creating backups, or interacting with the storage subsystem. High occurrences might suggest slow disk performance or file system issues.

3. `PREEMPTIVE_OS_ENCRYPTMESSAGE`

Found when SQL Server encrypts or decrypts data, often associated with Transparent Data Encryption (TDE). If these waits are prominent, the encryption/decryption process might be a performance bottleneck.

4. `PREEMPTIVE_OS_PIPEOPS`

Related to SQL Server communicating over named pipes. These waits might occur in environments with inter-process communication over named pipes and could signal latency in those interactions.

5. `PREEMPTIVE_OS_NETWORKOPS`

Indicates SQL Server is performing network operations. This wait is common in distributed architectures or replication setups where network latency or throughput could be an issue.

6. `PREEMPTIVE_OS_UNMANAGED_INTEROP`

This occurs when SQL Server calls unmanaged code, such as external DLLs or extended stored procedures. Excessive waits here often point to poorly optimized or unstable external code.

7. `PREEMPTIVE_OS_COMOPS`

Reflects waits related to COM (Component Object Model) operations. This might appear if SQL Server interacts with COM objects, such as SQL Server Agent or SSIS.

Diagnosing and Resolving `PREEMPTIVE_*` Waits

When you see PREEMPTIVE_* waits in your wait stats, follow these steps to investigate:

Step 1: Identify the Wait

Use sys.dm_os_wait_stats to determine the specific PREEMPTIVE_* wait types contributing the most to overall wait time:

   SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
   FROM sys.dm_os_wait_stats
   WHERE wait_type LIKE 'PREEMPTIVE%';

Step 2: Examine External Dependencies

For the identified wait types:

Authentication waits: Check Active Directory connectivity and performance.
File system waits: Use tools like DiskSpd or PerfMon to assess disk performance.
Network waits: Test network latency using ping, tracert, or similar tools.
Unmanaged interop waits: Review any external DLLs or code SQL Server calls.

Step 3: Optimize SQL Server Configuration

If the issue is related to backups or file operations, consider optimizing backup strategies or file placement.
For authentication, use connection pooling to minimize repeated login overhead.

Step 4: Monitor and Adjust

Utilize a tool like Database Health Monitor to continuously monitor wait stats. Database Health Monitor provides detailed insights into SQL Server performance, including wait types, and can alert you to unusual patterns.

Real-World Example

A client recently contacted Stedman Solutions with complaints of slow query performance and intermittent login failures. Analysis of their wait stats revealed high PREEMPTIVE_OS_AUTHENTICATIONOPS waits. After investigation, we identified slow Active Directory responses due to high replication latency between their domain controllers. By optimizing their AD environment and implementing caching strategies, the waits were drastically reduced, improving overall system responsiveness.

Conclusion

Preemptive waits sql server provide valuable insights into SQL Server’s interactions with external systems. While not always the root cause of performance issues, they point to areas outside SQL Server that may need attention. If you’re encountering high PREEMPTIVE_* waits and need expert guidance, our Managed Services can help identify and resolve these issues before they become critical. Let us take care of your SQL Server performance, so you can focus on growing your business!

Introduced in SQL Server 2008.