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.

more ▼

asked Nov 02, 2009 at 03:22 PM in Default

Ryan gravatar image

13 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Nov 02, 2009 at 06:10 PM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

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, 2009 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, 2009 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, 2009 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, 2009 at 10:36 AM Ryan
(comments are locked)
10|1200 characters needed characters left

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:
"Preemptive mode used in SQL Server for external code calls, CLR with an UNSAFE assemblies, APIs that could block for extended periods (example –extended stored procedures)"

He breaks down the preemptive wait type naming convention like this:

PREEMPTIVE_<category>_<function or class name>

"API function or call can often be found on MSDN"

Unfortunately, I have not been able to find anything specific about AUTHORIZATIONOPS in MSDN or elsewhere.

more ▼

answered Nov 02, 2009 at 05:17 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

+1 nice link - that's a very useful slide deck
Nov 02, 2009 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, 2009 at 06:56 PM Ryan
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 02, 2009 at 03:22 PM

Seen: 2856 times

Last Updated: Nov 03, 2009 at 05:58 AM