SQL Server Performance Forum – Threads Archive
db connection jump from 350 to 2000Approximately once a week for the past 3 weeks I have had some issues with
performance in sql database that have caused signification problems with the custom winform based editorial tool and/or live site rendering.
The primary symptoms have been extreme sluggishness in the tool, the publish
process very slow or not moving at all and slow rendering on the live
site. When the issues have occurred we have noticed a couple of things on the
sql database. The user connections on the database take a significant and
fairly sudden jump from about 350 to 2000 or more, the server itself becomes
very slow, in 2 of the 3 cases it became totally unresponsive, unable to
connect via QA. A sql restart has fixed the problem in all cases. Any suggest
to look at appreciated….. We have a custome built CMS winApp to push contents to
live site. All contents stored in sql database. But for some reason we have
this issue with connetion jump suddenly without any indication.
The application is not closing the SQL connection properly so it just keeps on increasing until SQL Server runs out of memory.
You should be able to find out which machine/task/SQL the rouge connections are doing and inform developer of the CMS winApp.
what’s probably happening is you have web server with connection pooling
normally 350 connections is sufficient such that whenever a request comes in, there is an available open connection from the pool then, some query or other event comes along causing a horrible slow down in SQL Server,
so all requests now take a very long time.
as new user requests come in, these first get allocated an already open connection
but the query never completes, and the connection is never returned to the pool so the web server opens new connections for each subsequent request.
the true cause of the problem is whatever started this.
the excessive new connections is just throwing fuel on the fire there could be any number of causes.
if it is predicted, then is there a scheduled query causing this?
if it is over time with no apparent source, it could be the architect did not know certain things about virtual address space (VAS), see if restarting SQL Server every 2-3 days makes this go away