June 26, 2007 at 3:51 pm
Hi All,
Is there a way to determine if a particular application is using too many db processes? More basically, what exactly is a process and what can I learn by looking at the Activity Monitor?
Here's the situation: I'm getting performance complaints about a specific application and I've noticed that it seems to show a lot of processes compared both to other apps and to the number of actual users. The Activity Monitor shows 76 processes for only 15 end users; I'm counting users based on the Host name because the User is the same for every process. One host name had 18 processes associated with it. They are all "sleeping" and "awaiting command" and none of them appear to be blocked or otherwise waiting. Does this seem unusual? Can "too many" (however many that might be) processes drag down db/server response times?
btw, I'm comfortable w/ routine DBA tasks (e.g. security, backups/restores), but I'm just starting to figure out how much I don't know about performance tuning and troubleshooting. So if this seems like an ignorant question, it probably is...
Thanks!
June 26, 2007 at 10:42 pm
The answer to your question,
Can "too many" (however many that might be) processes drag down db/server response times?
is yes. Too many processes in the server either running or not running can cause performance problems. This is because each process is allocated an amount of memory when the process starts and this is not released until the connection is closed. Since you say that there are processes that have a sleeping state and is not blocking other processes it means that they are not closed properly usually windows does a check to see any unused processes and closes it but it happens only for every 2-3 hours and not frequently. You need to find a way in such that the connection gets closed once the transaction is completed. You can also use connection pooling feature available.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 10:19 am
OK, that makes sense. So does one process = one connection?
This is a third-party desktop client application, so I can't control (or even directly determine) how it opens and closes db connections. Also, I always thought connection pooling was something that was handled by IIS and/or the CLR. Are you saying that there are SQL Server configuration changes I can make that will affect connection pooling and/or garbage collection?
Thanks for responding,
MIJ
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply