September 3, 2014 at 9:01 am
We have recently started experiencing login timeouts and with the help of the connectivity ring buffer (http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx) we were able to determine the issue was related to resource governor.
Upon digging into the buffer a little deeper I am seeing timeout issues that I cannot explain. For example, a local connection timed out with a high 'EnqueueTime' under the 'Ssl' node in the XML:
<Ssl>
<TotalTime>5809</TotalTime>
<NetReadsTime>54</NetReadsTime>
<NetWritesTime>0</NetWritesTime>
<SecAPITime>1</SecAPITime>
<EnqueueTime>5754</EnqueueTime>
</Ssl>
Is there ANY documentation around what these numbers mean? My google-fu is failing me here, any help would be highly appreciated, and anything discovered will likely be shared here and/or in a blog post for the rest of the community.
September 3, 2014 at 9:56 am
Quick thought, check the lock and sys.dm_os* statistics, chances are that "popular" locks are using up most available Workers, causes connection havoc.
Search keywords: Workers, Scheduling Options, SQLOS, sys.dm_os*
😎
September 3, 2014 at 10:38 am
We are almost positive the issue has something to do with worker threads, at this point I am just interested in what all of these values tell me. I am finding ssl enqueue time even for local connections, which doesn't make sense to me. It's my understanding that the various enqueue values mean that something is "processing", but I was hoping to find more detailed explanations.
So far I know/assume the following:
- Enqueue time = CPU processing time
- SSPI* time = time spent authenticating with AD
- TriggerAndResGovTime = time spent waiting for either a login trigger or resource governor classification to complete
- SSL* = ???
I would like to assume the SSL values represent the amount of time it takes to verify the cert and go through the handshake, but why would I be seeing enqueue time for a local login (specifically a sqlcmd script run locally with a SQL login)?
September 3, 2014 at 10:52 am
eniacpx (9/3/2014)
We are almost positive the issue has something to do with worker threads, at this point I am just interested in what all of these values tell me. I am finding ssl enqueue time even for local connections, which doesn't make sense to me. It's my understanding that the various enqueue values mean that something is "processing", but I was hoping to find more detailed explanations.So far I know/assume the following:
- Enqueue time = CPU processing time
- SSPI* time = time spent authenticating with AD
- TriggerAndResGovTime = time spent waiting for either a login trigger or resource governor classification to complete
- SSL* = ???
I would like to assume the SSL values represent the amount of time it takes to verify the cert and go through the handshake, but why would I be seeing enqueue time for a local login (specifically a sqlcmd script run locally with a SQL login)?
SSL value would be the encryption effort of the login credentials. My suspect is still the available workers or rather lack there of.
😎
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply