如何确定 SQL Server 2000 DB 上可以打开的最佳连接数？
SQL Server 2000 DB 上可以打开的最佳连接数是多少。 我知道在我工作的上一家公司，在配备 Oracle 8i、8 处理器的 tru 64 机器上，我们发现 8*12= 96 个连接似乎是一个不错的数字。 SQL Server 2000 是否有任何此类计算。数据库在 2 处理器（超线程 4）计算机上运行。 有很多针对数据库运行的事务。 我问这个问题的原因是因为我们有一个应用程序，即使它没有执行任何操作，通常也会打开大约 100 个连接，而且我很难解释这可能是导致我们性能问题的原因。 也许，SQL Server 没有这样的限制...你们中的任何人都可以就此倾诉一些智慧吗？ 非常感谢。 谢谢，
我应该补充一下它是标准版。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您的性能问题不会由连接数引起。
除了 sliderhouserules 的答案之外，作为快速解决方案，我建议关闭超线程而不是限制您的连接。
link1< /a>, link2 （注意：这个家伙从事 MS SQL 2005 代码）
每个连接占用少量内存。 共享数据库锁仅用于稳定性。
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.
MSDN 上的这篇博客文章表明没有限制 - 至少在 Express 版本中：http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx
这表明它可能是 256，对于精简版 - http://blogs.msdn.com/stevelasker/archive/2006/04/10/ SqlEverywhereInfo.aspx
这也显示没有限制：http://channel9.msdn.com/forums/TechOff/169030-SQL-Server-2005-Express-and-Developer-之间的差异- Edition/?CommentID=299642
添加 - 来自评论，http://msdn.microsoft.com/en-us/library/aa196730(SQL.80).aspx 表示最大值为 32767，而没有“理想” ”
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"
如果应用程序是一个长时间运行的应用程序并且位于同一服务器上，如果该应用程序留下已创建锁的打开数据库句柄，这确实对性能不利。 您可以检查诸如 select * from sys.dm_tran_locks 或 sp_lock 之类的内容来给您一个想法。
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.
如果您不知道这是否是您的性能瓶颈，那么您应该尝试确定它，而不是尝试限制连接或其他东西。
如果还没有，您应该：
如果 #1 和 #2 没有发现任何内容，并且 #3 显示您的数据库服务器在加载期间响应时间很慢，那么您就知道遇到了“连接过多”之类的问题。 但是，如果您还没有完成#3，那么似乎建议您这样做，因为破坏连接限制之类的事情似乎只会造成人为瓶颈，而不会真正让您找到问题的根源，IMO。
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:
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.