June 10, 2012 at 10:54 am
a few days ago I got pulled into a call regarding an issue with an SQL server in trouble. I did not have access to the server, as it belongs to a new small company we recently aquired, so i apologise if i cannot proivde any in depth details here as all i have to go on is what my collegues mentioned on the phone.
Issue:
Sql server would refuse to accept connections every few minutes. Any connections currently made to the server seemed to be ok, but new ones would just hang/timeout. After a minute or so this would subside, until the next time.
A bit of questioning showed that connections to the server were at 800+. This sounds huge to me. My gut instinct was that SQL Server could not handle all the connetions hitting it. the problem is i do not know how to confirm this.
I checked memory clerks but could not see anything taking up too much memory there, and the server had over 24GB of free unassigned memory.
CPU was not overly high, although i did notice that cpu's were affinitized. Although the issue did not reproduce itself for the duration of the call, i believe that if it had, we would have saw one CPU spiking to 100%, although again, i cannot currently confirm this.
I have advised setting up a perfmon counters trace to monitor various CPU, SQL, Memory, Disk counters so we have more to go on should the issue occurr again.
If anyone can advise however, how i would check if this issue is related to the number of connections i would appreciate it. what counters or sql dmv's will indicate pressure due to number of connections?
Thanks all!
Any advice on what to loo
June 10, 2012 at 11:27 am
Thomas Stringer (6/10/2012)
Out of curiousity, what does the following return?
select @@max_connections as max_connections;
And you say CPU isn't high. Is that the overall "_Total" CPU? If so, you could be skewing the results.
Unfortunately the issue is not reproducable at the moment so i cant check current values, but regarding the @@max_connections. that will most likely show 32k, as is the default. i know sql can technically in an ideal world handle that many connections but in reality i dont believe its so. thats why 800+ connections (as viewed in perfmon) looked like a lot to me.
Regarding the CPU, Again i dont have any figures, but what i was told is that there are 4 logical cores on this server and none of them were hitting above 50% when viewed in task manager.
I have all individual logical cores being monitored in perfmon in case this happens again however, so i get direct confirmation.
June 10, 2012 at 11:30 am
The first thing to check would be to see if someone's fiddled with the max worker threads setting. It generally should be left at default unless there is very, very, very good reason for changing it.
Look for errors like 'New queries assigned to process on Node <X> have not been picked up by a worker thread in the last 60 seconds.', or 'Limit on 'Max worker threads' reached' or VirtualAlloc failures (but generally that only appears on 32 bit), general out of memory errors also, but those are more likely to indicate that the memory config is wrong and that incorrect config is causing other problems.
800 connections shouldn't swamp a server unless there's other problems.
If it happens again, see if you can get a DAC connection and investigate live.
p.s. I'd recommend removing the processor affinity unless there's an incredibly good reason for it to be set.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply