View SQL Server database connections

1. Check the system performance:
Start-> Administrative Tools-> performance (or enter MMC in the running) and then
Add a counter to add common SQL statistics (MSSQL general statistics) and select user connection in the items listed below to query the number of SQL Server database connections from time to time.

2. query through the system table:
Select * from
[Master]. [DBO]. [sysprocesses] Where [dbid]
In
(
Select
[Dbid]
From
[Master]. [DBO]. [sysdatabases]
Where
Name = 'databasename'
)
Databasename indicates the database to be viewed, and the number of queried rows is the number of connections to the current SQL Server database. However, there are some other statuses for reference.

Alternatively, use the following query statement:

Select * From sysprocesses where dbid in (select dbid from sysdatabases where name = 'mydataba ')

 

From the preceding statement, we can see that the system table obtains some connection and activity information, mainly introducing the following two system tables:

(1) sprocesses

The sysprocesses table stores information about running on Microsoft? SQL Server? Process information. These processes can be client processes or system processes. Sysprocesses is only stored in the master database.

(2) sysperfinfo

Include a Microsoft? SQL Server? The internal performance counters of the representation can be displayed through the Windows NT Performance Monitor.

 

Someone suggested that the following SQL statement be used to obtain the current number of connections of SQL Server:

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

I personally think it is incorrectSee the. sysprocesses login_time column.

Another aspect is that processes cannot be compared with connections. Are they in a one-to-one relationship, that is, a process is a connection? A connection should have multiple processes, so the relationship between the connection and the process should be 1: N.

Because the processes listed in sysprocesses contain system and user processes, you can use the following SQL statement to obtain user connection:

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 wrong because it is a counter and may accumulate.

Another solution is to use the following SQL:

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

The reason is:Net_addressIt is the network card value of the visitor's machine. It should always be unique, but it looks like the number of connections in all time periods.

I hope you can provide your own solutions. I believe this solution will be of great use.

 

3. query through the system process:
Sp_who 'loginname'
Loginname is the user name used to log on to SQL. Generally, a username is used in the program to log on to SQL. This user name can be used to check the connection occupied by the user name after login.
If loginname is not written, all SQL Server database connections are returned.

 

4. view it in SQL Enterprise Manager:

---- Management --- current activity ----- process info in Enterprise Manager

 

Here are several related concepts.

SQL Server provides some functions to return connection values (this is not the current number of connections !), I personally think it is easy to misunderstand.

System Variables

@ ConnectionsBack from the last start of Microsoft? SQL Server? The number of times that a connection has been established or attempted.

@ Max_connectionsMicrosoft? SQL Server? The 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

About the current Microsoft? SQL Server? User 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'