April 7, 2008 at 7:49 pm
Hey Folks -
Running SQL Server 2005 here and noticed the other day that the CPU usage on the server was hovering around 60%, and by the end of the day 80%. I started looking into it, and it was being caused by a number of inactive and old connections created by an application login to a stored procedure. I was able to kill these inactive connections/processes in the Activity Monitor and the CPU usage dropped back down to normal.
I have been keeping an eye on the server and every few days this seems to happen. It is always a specific application login running a specific stored procedure that this causes this behavior to occur. Basically, when I catch it misbehaving the connection for the process is hours old and what is frightening about it is that it behaves like a resource leak -- the CPU cycles increase gradually the longer I don't catch it.
Normally, the application logs onto the server, executes a stored procedure, and then the connection is closed. That's how it's supposed to work. This stored procedure probably gets executed about 3k times/day.
The script that is calling the stored procedure was written in a "classic" ASP form (slash VBScript) so the application calling the sproc is using the old ADO model and not ADO.NET, so I'm wondering if there is a connection pooling problem here or if there's something additional I could ask the programmer to include in his code to check explicitly set some maximum time that the connection can remain open at the time it is being established? I already know that the stored procedure should never take more than 1 second to execute (it's a proximity search-related procedure, usually executes around 0.3-0.5 seconds), so putting a generous 5 minute timeout or something should be fine. I have reviewed the ASP code and it is explicitly dropping/closing the database connection when it's done with the stored procedure, so I'm not sure what's up.
I'm going to double-check with the developer and see if he can't beef up the error handling to make sure that in the case of any error being thrown the db connection is closed if it is open, but in the off chance that it's not the ASP's fault and that there's just something weird going on here, is there any recommended way that I could best monitor this server and get a warning when a CPU threshold is reached and kill any "old" offending processes running under this particular login executing this particular stored procedure? Preferably in an automated fashion? I'm also wondering if there's a way that I can explicitly force this application login to have a maximum time that an instance of itself can be logged in.
Any help would be greatly appreciated, thanks!
April 9, 2008 at 7:22 am
You should be able to do this by setting up alerts on the server.
Books Online has an article on this: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/65d2c5c1-921f-4eff-9ef7-149170ab61e8.htm
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply