How do I determine the optimal number of connections that can be open on my SQL Server 2000 DB?

Question

What is the optimal number of connections that can be open on a SQL Server 2000 DB. I know in the previous company I was working for, on a tru 64 box with Oracle 8i, 8 processor machine we'd figured out that 8*12= 96 connections seemed to be a good number. Is there any such calc for SQL Server 2000. The DB runs on a 2-processor(hyper threaded 4) machine. There are a lot of transactions that run against the DB. The reason I ask is because we have an app that typically tends to leave around 100 connections open even if it is not doing anything and I am having difficulty explaining that that might be a cause for our performance issues. Maybe, SQL Server does not have such a limitation... Can any of you pour forth some wisdom on this? Much appreciate it. Thanks,

I should add it is the Standard Edition.

Answer 1

This blog post on MSDN indicates there is no limit - at least in the Express editions: http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx

And this indicates that it might be 256, for lite editions - http://blogs.msdn.com/stevelasker/archive/2006/04/10/SqlEverywhereInfo.aspx

This also shows no limit: http://channel9.msdn.com/forums/TechOff/169030-The-difference-between-SQL-Server-2005-Express-and-Developer-Edition/?CommentID=299642

addition - from a comment, http://msdn.microsoft.com/en-us/library/aa196730(SQL.80).aspx indicates the max is 32767, while there is no "ideal"

Answer 2

If the app is a long running app and it's on the same server, if the app leaves open db handles that have created a lock this is truly bad for performance. You can check something like select * from sys.dm_tran_locks or sp_lock to give you an idea.

Answer 3

If you don't know if this is your performance bottleneck then you should be trying to determine that, not trying to limit the connections or something.

If you haven't, you should:

1. Use SQL Profiler to find long-running queries.
2. Monitor your db server's cpu load, memory/page file usage, and network usage
3. Find one of your longest running queries (see #1 above) and write a very lean test app that can throw this query at your db server during peak load and record some response times.

If #1 and #2 don't uncover anything, and #3 shows your db server has slow response times during load then you know you have a problem like "too many connections". But if you haven't done #3 then it seems advisable to do that, as mucking with connection limits and such seems like it will just create artificial bottlenecks, and not really get you to the root of your problem, IMO.

Answer 4

Your performance issue will not be caused by the number of connections.

As well as sliderhouserules' answer, as a quick fix I'd suggest switch off hyperthreading rather than limiting your connections. link1, link2 (note: this guy worked on the MS SQL 2005 code)

Each connection takes a trivial amount of memory. A shared db lock is for stability only.