MAx no of user conection to SQL ??

Question

MAx no of user conection to SQL ??

Lavanyasri

SSCarpal Tunnel

Points: 4907
More actions
September 12, 2011 at 2:20 am

#244632

HI all,
I added a perfmon counter for no of user connection to sql :
SQL Server General statistics : User connections
Its showing arround 200 connections :
I have a question ?? what are the max no of user connections allowed by a SQl server . ??
Regards,
Lavanya

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

The maximum number of connections is 32,767 (you can check it out at http://msdn.microsoft.com/en-us/library/ms143432(v=SQL.90).aspx"> http://msdn.microsoft.com/en-us/library/ms143432(v=SQL.90).aspx). Of course this is a theoretical issue. I’ve never had a server that had even 10% from that number.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

You can also execute following command:

SELECT @@MAX_CONNECTIONS

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 3

Hi friend,

Thanks for the quick response and thanks for the information .

this is the correct way to check the user connections ???

SQL Server General statistics : User connections

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Lavanyasri (9/12/2011)
Hi friend,
Thanks for the quick response and thanks for the information .
this is the correct way to check the user connections ???
SQL Server General statistics : User connections

If you want to check how many active connections SQL Server is serving in a particular moment, you can run this query:

select count(*)

from sys.dm_exec_connections

If you want to see how that number changes in time, the perfmon counter would do.

-- Gianluca Sartori

prithvi.m Mr or Mrs. 500 Points: 561 More actions · Answer 5

this should do you good. Lists only active users.

USE msdb

SELECT * FROM sys.dm_exec_sessions WHERE Status != 'sleeping'

This shall probably give you a better idea to whom to keep and whom to kick out if there is a bottleneck.

USE msdb

SELECT * FROM sys.dm_exec_sessions

Emil Fridriksson Mr or Mrs. 500 Points: 547 More actions · Answer 6

Adi Cohn-120898 (9/12/2011)
The maximum number of connections is 32,767 (you can check it out at http://msdn.microsoft.com/en-us/library/ms143432(v=SQL.90).aspx"> http://msdn.microsoft.com/en-us/library/ms143432(v=SQL.90).aspx). Of course this is a theoretical issue. I’ve never had a server that had even 10% from that number.
Adi

I've hit this limit... I have around 200 application servers that have around 12000 connections total if everything is normal. If all DB sessions on the application server are in use (blocking) for some reason, it will add connections, so if I have a DB hiccup, perhaps a table lock on a very busy table, the application servers start adding connections. A crash issue with SQL Server 2008 made me unable to release idle connections, as doing so increased the frequency of crashes quite a lot. I've only hit the limit when I've had issues though...