February 13, 2008 at 6:06 am
Hi,
I have never really worried about SPIDs with a status of sleeping, they are not really doing anything, so why worry?
Recently a new application has been rolled out, this application appears to be causing an increase in the number of SPIDs left as sleeping. The questions are, should this be a cause for concern, is there anything within SQL server that can be used to remove the sleeping processes (other than the sledgehammer KILL)
I would also like to definitively establish how much memory in total the sleeping processes are using up and/or determine if they are having any impact upon server/database performance.
My understanding is that the sleeping process is waiting on the connection to do something else. If the connection is not dropped, or is not dropped cleanly, how long will the process remain (is there a timeout value, or will they remain until sql is restarted)?
Any suggestions gratefully received.
February 13, 2008 at 6:29 am
The process should remain while the connection is there. If it drops, I would have thought that the process would drop on the server.
Each one uses a small amount of memory, I think something like 55KB.
What's a lot of processes? Do you think the app is opening connections and then not closing them?
February 13, 2008 at 6:42 am
Hi,
yes it looks as though the appl is not clsoing conenctions once it completes it routine. The number of connections created is around 25, with potentially up to 30 end users, so a max of 750 sleeping connections. Although this number is unlikely to be acheived at one time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply