August 6, 2015 at 6:00 am
I am seeing PREEMPTIVE_OS_SECURITYOPS as last_wait_type on almost every session. It seems to be causing issues this morning. I have never seen this wait type, so I am wondering if someone can shed some light on it?
Jared
CE - Microsoft
August 6, 2015 at 7:48 am
I looked and also couldn't find the answer, and I feel stupid suggesting anything because you've made 3000 posts.
But if it was me, and I didn't know the answer, I did think of what I'd look at just to see if anything turned up - assuming from the wait name that it's related to active directory or the domain controllers.
Part 1
- Check what the owner of the databases is. It's possible it's an AD account which no longer exists or is disabled, it's being checked (for some reason) and taking a long time to return. Some applications absolutely require an AD account to be the owner of their database (AzMan & SCOM) so be careful about changing it over to a disabled dummy SQL account (which is IMHO otherwise best practice).
- I've seen apps create a linked server link to AD and hammer it with queries that way - though I have no idea how it works or what it's for (I think they were doing an Openrowset with it). I'd look at what linked servers are set up just to rule it out.
- You've likely already checked ERRORLOG and the Windows Event Viewer logs. I'm always amazed at some of the crap buried there, but I also sometimes just forget to look in the latter. "Why would I? It's unrelated." But you just never know.
Part 2
- Check which domain controller the server is authenticated with (there's a command line, and possibly environment variable, to show it).
- Traceroute, and ping, and then check with the sysadmins if it's the one they would expect. Only because usually companies have more than one, the wrong one can be picked, and if it's far away or unhealthy it could cause problems.
- Ask the sysadmin if they can health check that particular server. I'm not sure what options are available in that regard.
- Impersonate some of the users and select * from sys.login_token. Only because it's an easy way to see all the groups they're in... there is some hard limit beyond which Windows starts having problems but it's hard to imagine everyone would be having that problem. Also anecdotally I don't trust extremely long group names.
- I'd also gather a longer period of wait stat information (e.g. a few minutes, an hour) and see what the top waits are. Only because I'd wonder if other waits are involved, or if it might reveal something (like SQLCLR) which might point to that doing something weird that needs investigating.
- Write a quick PowerShell script to occasionally query a login from the AD, just to see what the timings are like, if they're changing, mixed in with pings to see if packets are being lost.
These ideas probably sound stupid. But if there's no answer what else can you do but try stuff?
August 6, 2015 at 8:23 am
Good thoughts, but it doesn't seem related at all. At least nothing in logs or that performance counters can demonstrate. It cleared up now, so I can't get any more data. Oh well 🙂
Jared
CE - Microsoft
January 14, 2016 at 9:33 am
Noticed the same Wait Type here on queries using linked server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply