I received a call yesterday from a customer who was complaining about high CPU usage on their SQL Server. We're using SQL Server 2012 64 bit SE. Server is running Windows Server 2008 R2 Standard, 2.20 GHz Intel Xeon (4 Cores), 16 GB RAM.
After making sure that the culprit was in fact SQL Server, I took a look at the top waits for the instance using the DMV query here. The top two waits were: (1)
PREEMPTIVE_OS_DELETESECURITYCONTEXT and (2)
EDIT: Here are the result of the "top waits query" (although someone restarted the server this morning against my wishes):
We do a lot of intense calculations/conversion, so I can understand
SOS_SCHEDULER_YIELD. However, I am very curious about the
PREEMPTIVE_OS_DELETESECURITYCONTEXT wait type and why it might be the highest.
The best description/discussion that I can find on this wait type can be found here. It mentions:
The PREEMPTIVE_OS_ wait types are calls that left the database engine, typically to a Win32 API, and are performing code outside of SQL Server for various task. In this case, it is deleting a security context previously used for remote resource access. The related API is actually named DeleteSecurityContext()
To my knowledge, we don't have any external resources like linked servers or filetables. And we don't do any impersonation, etc. Could a backup have caused this to spike or maybe a faulty domain controller?
What the heck could cause this to be the dominant wait type? How can I track this wait type further?
Edit 2: I checked the contents of the Windows Security Log. I see a few entries that may be of interest, but I'm not sure if these are normal:
Special privileges assigned to new logon. Subject: Security ID: NT SERVICE\MSSQLServerOLAPService Account Name: MSSQLServerOLAPService Account Domain: NT Service Logon ID: 0x3143c Privileges: SeImpersonatePrivilege Special privileges assigned to new logon. Subject: Security ID: NT SERVICE\MSSQLSERVER Account Name: MSSQLSERVER Account Domain: NT Service Logon ID: 0x2f872 Privileges: SeAssignPrimaryTokenPrivilege SeImpersonatePrivilege
Edit 3: @Jon Seigel, as you requested, here are the results of your query. A little different than Paul's:
Edit 4: I admit, I'm a first time Extended Events user. I added this wait type to the wait_info_external event and saw hundreds of entries. There is no sql text or plan handle, only a call stack. How can I further track down the source?