Obtain the current number of connections of SQL Server

I have not solved this problem.

When a netizen asks me this question,I thought it was quite simple.,Assume that SQL Server should provide corresponding system variables or something.But so far,I have not yet obtained a better solution. It may be very simple, but I don't know. I hope so ..

Let me talk about the relevant knowledge..I hope you can provide a better method..

Here are several related concepts..

SQL ServerProvides some function return connection values(This is not the current number of connections.!),I personally think,It is easy to misunderstand.

System Variables

@ Connections Returned from the last startupMicrosoft SQL ServerThe number of times that a connection has been established or attempted.

@ Max_connections ReturnMicrosoft SQL ServerThe maximum number of simultaneous user connections allowed on. The number returned does not need to be the currently configured value.

 

System stored procedures

Sp_who

Provide information about the currentMicrosoft SQL ServerUser and process information. You can filter the returned information so that only idle processes are returned.

List all active users: Sp_who 'active'

List information of a specific user:Sp_who 'sa'

 

System Table

Sysprocesses

 Sysprocesses SaveMicrosoft SQL ServerProcess information. These processes can be client processes or system processes.Sysprocesses Only stored in Master Database.

Sysperfinfo

IncludesMicrosoft SQL ServerThe internal performance counter of the notation.Windows NTPerformance Monitor display.

 

Someone suggested thatSQL ServerCurrent number of connections:Use the followingSQL:

Select count (*) as connections from Master... sysprocesses

I personally think it is incorrect,Look. SysprocessesOfLogin_timeColumn to see.

Another aspect is that processes cannot be compared with connections.,Are they one-to-one relationships?,That is to say, a process is a connection.?One connection should have multiple processes,So the relationship between the connection and the process should be1: NOf.

BecauseSysprocessesThe listed processes include system processes and user processes.,To get user connection,You can use the followingSQL:

Select cntr_value as user_connections from Master... sysperfinfo as P
Where p. object_name = 'sqlserver: General Statistics 'and P. counter_name = 'user connections'

I still think it is incorrect.,Because it is a counter,May accumulate.

Another solution is to use the followingSQL:

Select connectnum = count (distinct net_address)-1 from Master .. sysprocesses

The reason is:Net_addressIs the NIC value of the visitor's machine.,This should always be unique..But it looks like the number of connections in all time periods.

I hope you can provide your own solutions..This problem has been solved.,I believe it will be of great use..