|
What does the wait type PREEMPTIVE_OS_AUTHORIZATIONOPS mean? I have a service which is executing a stored procedure to log historical data. It has been running fine for a few months. Out of the blue I found we were no longer logging data and the service calls to SQL were timeing out because the connection pool was full. The activity monitor showed hundreds of processes with that wait type, no wait resource and no block by. The only google hits on it talk about this being added in 2008.
(comments are locked)
|
|
Based on some info at MVP - Christian Bolton's blog, the name suggests that it could be a wait associated with latency talking to your domain controller for authentication. In fact if anyone is at PASS Summit - there's a talk tomorrow, 'Inside SQL Server Wait Types – Bob Ward' - get in and ask him!
Nov 02 '09 at 06:20 PM
Kev Riley ♦♦
We have been having issues with domain authentication on this server so it could be related. Infact domain authentication broke again today but SQL is running fine. I am not sure why SQL would need to authenticate to the domain for this job. The activity log showed that the waiting processes were logged in using a database account I had created specifically for the service. My stored procedure is declared "WITH EXECUTE AS SELF", and I was probably logged in with a domain account when I created it. Could that be the link?
Nov 02 '09 at 06:55 PM
Ryan
You said 'job' so I presume this is running under SQL Agent? If so could that be a domain account? Yes running "WITH EXECUTE AS SELF" on the proc will run it under the context of the user who created the proc. Also your coincidental domain auth issues I would guess are either very closely related or just clouding the situation. If you rectify that you may fix your problem.
Nov 02 '09 at 07:27 PM
Kev Riley ♦♦
Sorry for confusing the terminology. The stored procedure is being called by a custom service, not SQL Agent. We have had the domain authentication issue for a while now and I am not sure when it will get resolved. I will update the stored proceture to run in the context of my SQL account just to be safe. Unfortunatly I have not been able to reproduce the origonal isssue so I will just have to wait and see. Thanks for everyones feed back so far. I would still like to see a clear definition of that wait type if someone comes across one.
Nov 03 '09 at 10:36 AM
Ryan
(comments are locked)
|
|
I don't have a specific answer for you, but I hope this helps. You can find a nice overview of waits here: http://www.digitalconcourse.com/dropzone/MSCOMM/PASSMN/PASSEVT20090616/Joe%20Sack%20Performance%20Troubleshooting%20with%20Wait%20Stats.pdf The author (a Microsoft Dedicated Support Engineer) explains: He breaks down the preemptive wait type naming convention like this: "API function or call can often be found on MSDN"
+1 nice link - that's a very useful slide deck
Nov 02 '09 at 06:11 PM
Kev Riley ♦♦
Thank you for those slides. Adding that one to my favs. It seems odd that these are not enumerated on MSDN.
Nov 02 '09 at 06:56 PM
Ryan
(comments are locked)
|
