|
our SQL Server 2000 sometimes gets more than 600 processes opened (not all active though). When we search with sp_who, and we have noticed that when the server peaks over 800 processes, it starts to behave unstable... Is it any peak here that I can or must respect? where can I find more information about this?
| ||||
|
feedback
|
|
There is no discrete limit to the number of processes that can be running at a given time. Ultimately, it's dependant upon your server's resources. There is a small amount of memory consumption for each open connection (500kb in SQL 2005, I believe - should be more or less the same for SQL 2000). It would probably help if you define "it starts to behave unstable". I imagine that you're simply taxing the server, and you'll need to inspect your operating environment to see whether you can optimize the environment, tune your server, or (as a last resort) if you need to add hardware resources. | |||
|
feedback
|
|
I would question why you're reaching that large a number of processes to begin with. We've run applications and web sites with thousands of concurrent users before and I don't recall ever seeing more than a couple hundred processes even with multiple separate applications running at the same time. Connection pooling is your friend. | |||
feedback
|
|
as you mentioned allready, sp_who shows the connections. sp_who 'active' shows the active connections so don't worry about the sleeping one. these are closed as soon as the client who invoked the connection is gone from the net. In Enterprise-Manager you can see the ip- and mac-address of clients invoking a connection. You can go thru the list and kill the connections by spid if you are shure that the client-side application is properly closed. | |||
|
feedback
|
