PREEMPTIVE Waits on 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.
Preemptive Waits SQL Server
PREEMPTIVE_COM_CREATEACCESSORPREEMPTIVE_COM_GETDATA
PREEMPTIVE_COM_QUERYINTERFACE
PREEMPTIVE_COM_RELEASE
PREEMPTIVE_COM_RELEASEROWS
PREEMPTIVE_COM_SEQSTRMREAD
PREEMPTIVE_COM_SETPARAMETERINFO
PREEMPTIVE_COM_SETPARAMETERPROPE
PREEMPTIVE_FILESIZEGET
PREEMPTIVE_HADR_LEASE_MECHANISM
PREEMPTIVE_OLEDB_RELEASE
PREEMPTIVE_OLEDBOPS
PREEMPTIVE_OS_AUTHENTICATIONOPS
PREEMPTIVE_OS_AUTHORIZATIONOPS
PREEMPTIVE_OS_AUTHZGETINFORMATIO
PREEMPTIVE_OS_AUTHZINITIALIZECON
PREEMPTIVE_OS_AUTHZINITIALIZERES
PREEMPTIVE_OS_BACKUPREAD
PREEMPTIVE_OS_CLOSEHANDLE
PREEMPTIVE_OS_CLUSTEROPS
PREEMPTIVE_OS_CREATEFILE
PREEMPTIVE_OS_CRYPTACQUIRECONTEX
PREEMPTIVE_OS_CRYPTIMPORTKEY
PREEMPTIVE_OS_CRYPTOPS
PREEMPTIVE_OS_DELETEFILE
PREEMPTIVE_OS_DEVICEIOCONTROL
PREEMPTIVE_OS_DEVICEOPS
PREEMPTIVE_OS_DTCOPS
PREEMPTIVE_OS_ENCRYPTMESSAGE
PREEMPTIVE_OS_FILEOPS
PREEMPTIVE_OS_FLUSHFILEBUFFERS
PREEMPTIVE_OS_GENERICOPS
PREEMPTIVE_OS_GETCOMPRESSEDFILES
PREEMPTIVE_OS_GETDISKFREESPACE
PREEMPTIVE_OS_GETFILEATTRIBUTES
PREEMPTIVE_OS_GETFINALFILEPATHBY
PREEMPTIVE_OS_GETPROCADDRESS
PREEMPTIVE_OS_LIBRARYOPS
PREEMPTIVE_OS_LOADLIBRARY
PREEMPTIVE_OS_LOGONUSER
PREEMPTIVE_OS_LOOKUPACCOUNTSID
PREEMPTIVE_OS_MOVEFILE
PREEMPTIVE_OS_NETVALIDATEPASSWOR
PREEMPTIVE_OS_PIPEOPS
PREEMPTIVE_OS_QUERYREGISTRY
PREEMPTIVE_OS_REPORTEVENT
PREEMPTIVE_OS_REVERTTOSELF
PREEMPTIVE_OS_SECURITYOPS
PREEMPTIVE_OS_SERVICEOPS
PREEMPTIVE_OS_SQLCLROPS
PREEMPTIVE_OS_WAITFORSINGLEOBJEC
PREEMPTIVE_OS_WRITEFILEGATHER
PREEMPTIVE_SERVICE_CONTROL_MANGE
PREEMPTIVE_SHAREDMEM_GETDATA
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_GETTARGETSTATE
PREEMPTIVE_XE_SESSIONCOMMIT
PREEMPTIVE_XE_TARGETFINALIZE
PREEMPTIVE_XE_TARGETINIT
See Also
Enroll Today!
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Contact Info
Stedman Solutions, LLC.PO Box 3175
Ferndale WA 98248
Phone: (360)610-7833
Leave a Reply