ansaurus

Question

any limit of SQL Server connection count?

Answer 1

+1 A:

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'

Nissan Fan 2009-09-30 18:01:58

Is my method of finding active connection number correct? If yes, do you mean the number I find should never exceed 32,767?

George2 2009-09-30 18:07:07

I just want to make sure the connection number I am finding is the same thing as you said. :-)

George2 2009-09-30 18:08:52

From your referred MSDN document, my most confusion is -- what is the differences between "Connections per client" and "User connections"?

George2 2009-09-30 18:12:02

Answer 2

+2 A:

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.

Remus Rusanu 2009-09-30 18:15:36

So excellent, let me study for a while. :-)

George2 2009-09-30 18:31:13

Me too :) I always took for granted that 32k+ user slots would never be a concern, but these particulars are really good details.

Nissan Fan 2009-09-30 19:30:54

Thanks Nissan, I studied the following link, http://msdn.microsoft.com/en-us/library/ms143432.aspx, my most confusion is -- what is the differences between "Connections per client" and "User connections"?

George2 2009-10-02 17:15:01

1. I am confused about your comments -- "connections actively executing something, sys.dm_exec_requests". What do you mean "something" here? Do you mean "something" is "sys.dm_exec_requests"? 2. I think the number of connection should be the same as that of requests if MARS is not used. Is that correct understanding? Any comments?

George2 2009-10-02 17:19:40

'something' means a SQL batch sent to the server (ie. a 'request'). The connections are always present as long as the client keeps the conneciton open. A request is only present from the moment the batch arrived at the server until the result is sent back to the client. Requests are visible in sys.dm_exec_requests.

Remus Rusanu 2009-10-02 17:19:58

Thanks for comments Remus, I am using sp_who2 or sys.dm_exec_connections to find active connections numbers, is that the correct method to count number of active connections?

George2 2009-10-02 17:22:30

define 'active connection'. sp_who will show system tasks (lazuy writer, ghost writer, resource monitor etc) that don't have a conneciton. sys.dm_exec_connections will show connections that don't execute anything. Also sys.dm_exec_connections show non T-SQL conenctions like Mirroring ones, you must filter out base don protocol_type.

Remus Rusanu 2009-10-02 17:33:28

Thanks Nissan, I studied the following link, http://msdn.microsoft.com/en-us/library/ms143432.aspx my most confusion is -- what is the differences between "Connections per client" and "User connections"?

George2 2009-10-02 17:35:35

And if you think my two methods to monitor SQL Server active connections are not perfect, I want to learn your perfect method to monitor SQL Server active connections. Thanks. :-)

George2 2009-10-02 17:47:13

I read again the document of sys.dm_exec_connections , and seems it does not say it will show idle connections along with active connections (i.e. no connection status column returned), how do you know "sys.dm_exec_connections will show connections that don't execute anything"? http://msdn.microsoft.com/en-us/library/ms181509.aspx

George2 2009-10-02 17:51:36

You better read up here: http://msdn.microsoft.com/en-us/library/ms189267.aspx

Remus Rusanu 2009-10-02 17:58:39

ansaurus

tags:

views:

answers:

any limit of SQL Server connection count?

related questions