any limit of SQL Server connection count?

Question

I am using SQL Server 2008 Enterprise + C# + ADO.Net + .Net 3.5. I am using sp_who2 or sys.dm_exec_connections to find active connections numbers (let me know if my method to find active connection numbers are wrong). For some heavy database consumer application, I can find even active connection count > 1000.

I am wondering whether there are any upper bound limitations of active connection numbers of SQL Server?

thanks in advance, George

Answer 1

It is a per-instance, not per database, configuration. You can check the current value in sys.configurations and change it with sp_configure. The relevant option is user connections:

Use the user connections option to specify the maximum number of simultaneous user connections allowed on Microsoft SQL Server. The actual number of user connections allowed also depends on the version of SQL Server you are using and the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections.

1000 of connections is not an exceedingly high number. On high end systems the server can listen on multiple ports affinitized to NUMA nodes and have hundreds and thousands of clients connected to each node.

Note that the number of connections is different from the number of requests, ie. connections actively executing something, sys.dm_exec_requests. Each Request requires one or more workers and the number of workers is configured with the max worker threads option.

Answer 2

http://msdn.microsoft.com/en-us/library/ms143432.aspx

32,767 is the max limit per database.

I would do it as follows:

SELECT      COUNT(*) FROM      master.dbo.syslockinfo WHERE       DB_NAME(rsc_dbid) = 'your_database_name'