SQL Server slows down and hangs after 300,000 + connections
-
SQLGuru hehehe i'm against it 100%.
But our parent company's other magazine sites in other countries are using it and they are very convincing in the board room.
Alex S -
>Memory configured dynamically and nothing else runs on this box.
I would change dynamically memory to "fixed memory" at a "sane" level that will keep the OS having enough memory left.
-
rickwid,
Based on the info given by AlexSQLForums, i dont think its necessary to fix the sql memory in his case. Its a dedicated SQL server and AWE is not being used here.
I've never seen SQL releases memory to OS on a SQL dedicated server after it grabs the memory (On a less than 4GB memory server) even when its set to allocate memory dynamically. If it does, something else is running on your machine and you should check it out. This way, it eases the troubleshooting as well and its also one of M$ best practise.
Also, FYI, /3GB and /PAE only works on Windows 2000 Advanced or Datacenter.
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
Simon, I ALWAYS set the maximum memory SQL Server is allowed to grab. It can and does starve the OS for RAM on occassion.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
SQLGuru,
when you hit this issue, it means something is already wrong with your sql server whether its fixed or dynamic. do you mean if you fixed the memory, you'll avoid sql from starving the OS memory?
under a dynamic memory allocation, sql will never "steal" from OS unless its under some tremendous stress. by setting the max, for me, it look more like masking the memory pressure and confine to sql rather than addressing the root cause. When it "steals" from OS, it also provides entry into SQL error logs and doesnt just "take it" and keep quiet.
Dynamic Memory Manager: Stolen=xx OS Reserve=xxx
i wont say 100% that setting dynamic memory allocation is a "must", but in my experience, it has helped me a lot in determining where the pressure came from, CPU, IO, memory, etc.
another blunt reason for setting dynamic memory, if sql never steals, how do you know your sql is starving. if sql steals, i'm very sure there's something wrong with sql without any of my users complaining to me. then i'll check out the root cause.
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
I didnt say that using dynamically configured was the cause of the problem, but i have seen machines getting into a bad state during high load with dynamic memory. I saw this happen often on SQL7(or was it 6.5?) but only seen it a few times on SQL2K. On SQL2K they used W2K Standard also.
Of course the root cause must be found and best way to do that is by running test in a test (or in worst case live) environment. Having a system that gets peaks on 1.8M hits in 1hour gets 444hits every second that hasnt been tested profesionally with monitors is a big, big risk. There is to little information here to say anything about the cause - it's just a guessing game and my guess is a OS limit/resource getting exhausted.
To find a a solution
1) To lower the risk of crashed. Try to decrease the peak load on the server somehow.
2) Setup a testbench and go ahead loadtest the webapps that generates most of the traffic. Monitor the webserver, network, Dbserver and look for bad trends when raising the load. If you have a budget issue - Use OpenSTA it's for free.
ricwid
-
Hi ricwid,
I didnt say and dont believe the dynamically configure was the cause of problem too. I just didnt agree on fixing the memory as per your advice because its a dedicated SQL Server and nothing else on the box (based on info provided).
This is based on my experience that fixing memory on a dedicated server less than 4GB is unnecessary, but there's always an exception (I.e someone is running anti-virus on dedicated sql!! i've seen this and they claim its a dedicated box running sql and nothing else!!). If there's any issue with OS memory exhausted, there should be an indicator in SQL Error Logs.
But from my hunch (maybe incorrect), it does appear to me the server is un-spec-ed.
You're right, to find solution must go through troubleshooting to determine the cause.
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
Thanks for all your replies.
Simon the antivirus software excludes mdf and ldf file types.
While reviewing our application code we found some horiffic code which raised a lot of red flags.
1. Stored procedures calling functions (which contain cursors on views)
2. ad_hoc queries using select *
3. Unnessecary open connections
4. Usage of temp tables with order by clauses.
...etc
Most of the code was written by developers 3-4 years ago (they are no longer with the company) and new developers are going line by line and fixing or patching.
Alex S -
One extra point - make sure ndf is in the AV exclude list too in case you have additional data files that use that extension.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Do you really have 300,000 to 800,000 unique connections (meaning that many people or devices) really are needing the SQL Server at that moment?
If you have 1000 users and 300,000 connections in the time frame then you are not properly closing and disposing of connections in your application for sure. This is the most common mistake made. And once a large number of connections are made it does tend to overwelm the server by causing to much contention for the connections. Remember each connection has a bit of memory overhead just to support it, then you have to deal with actual IO request. If you really have 300,000+ customers in such a short period you need to evaluate your hardware.
-
Good point thank you
Alex S -
Antares
After reviewing our app code we are rewriting the code which opens extra connections.
from our developers:
in some cases there is no global oject declared to be used throughout the modules so some of the subs contain there own open connection strings
.Alex S -
I take this back
I was wrong about changing the User Connections.
SQL Server manages this dynamically.
Alex S -
Sorry, saw you had posted that already after I posted, forgot to look at all the pages.
-
I have seen numerous circumstances where sql server will request and be given too much memory, causing horrible performance on the server due to excessive paging. Setting an appropriate max memory will prevent this. Note that it will not cause memory to be released back to the OS if you set a max AFTER sql has "too much" RAM allocated.
If you haven't seen this issue before consider yourself lucky. IIRC I have seen numerous forum posts about this issue and numerous reponses about how to prevent it - setting a max memory value.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply