SQL Server threads which are controlled by SOS (SQL Server operating system) are Non preemptive but at times they switch preemptive when they can’t obey the rules of SOS. Some common places when SOS thread is switched preemptive are when we call extended proc’s, few Windows API etc.

 

Let us assume you use “execute as user x” in your job, SQL Server calls Windows functions like LookupAccountName  to get the credential of user. Windows functions interacts with AD services to get the credentials of account and return the info to the caller in SQL Server process and then SQL Server would build the logintoken. If there is a delay in AD and if it takes long time to respond to the windows function calls other threads in the same scheduler would get blocked so SQL Server thread would switch preemptive (Doesn’t follow SOS rules) before  making these  function  calls. PREEMPTIVE_OS_AUTHORIZATIONOPS wait type would occur when a thread is waiting on such windows functions (security) to return, So first thing which would have to do is to fix the performance of AD calls.

 

To narrow down and prove that this issue occurs because of Active directory performance. Login to SQL server using the startup account of SQL Server and execute below query when you notice PREEMPTIVE_OS_AUTHORIZATIONOPS wait type and compare the times printed. It will give you the time it takes for SQL Server to complete the AD calls.

 

 

create procedure PREEMPTIVEOSAUTHORIZATIONOPS  with execute as self

as

set nocount on

select CONVERT(varchar, getdate(), 126) PREEMPTIVEOSAUTHORIZATIONOPS

go

print convert(varchar, getdate(), 126)

exec dbo.PREEMPTIVEOSAUTHORIZATIONOPS;    

print convert(varchar, getdate(), 126)

go