Tuesday, November 2, 2010

SQL server open connections

In order to solve performance issues or to check database usage, it can be quite useful to know how to get current connections to a SQL Server.

In SQL Server 2000 (if you still have one...), SQL Server 2005 and SQL Server 2008 the sp_who2 stored procedure returns information about current SQL Server 2000 users and processes. This function is unfortunately not very well documented.

As a general information, you must know that the connections returned by this function are denoted as SPID, or Server process Id. Running sp_who2 is easy, All that is required is to type sp_who2 and type F5.
Note that the first 50 results are system SPIDs (Generally these do not impact the performance of the system). So, Sp_who2 gives you this information:
  • SPID: System process id that requested the lock
  • STATUS: Background, sleeping or runnable
  • LOGIN: The login name that has requested the lock
  • HOSTNAME: The computer where the lock request has been initiated
  • BLKBY: The spid of the connection that is blocking the current connection
  • DBNAME: The database name where the lock request has been generated
  • COMMAND: General command type that requested the lock
  • CPUTIME: The number of milliseconds the request has used
  • DISKIO: Disk input / output that the command has used
  • LASTBATCH: Date and time of the last batch executed by the connection
  • PROGRAMNAME: The name of the application that issued the connection
For instance, you can run sp_who2 and get all the connections:


Or you can append the 'active' parameter and SQL will return only the active connections:

sp_who2 active

An important thing to remember when runnign sp_who2 is that the user running the query must have the VIEW SERVER STATE permission on the server in order to see all executing sessions on the instance of SQL Server. Otherwise, the user will see just the current session.

As an alternative, which is still working even if deprecated under SQL 2008, the system table sys.sysprocesses contains connection information for each connection made to the SQL Server. You can query it like this:

select * FROM sys.sysprocesses

The result will mainly contain the following information:
  • SPID: SQL Server session ID.
  • KPID: Windows thread ID.
  • BLOCKED: ID of the session that is blocking the request. If this column is NULL, the request is not blocked
  • DBID: ID of the database currently being used by the process.
  • UID: ID of the user that executed the command.
  • CPU: Cumulative CPU time for the process.
  • PROGRAM_NAME: Name of the application program.
  • CMD: Command currently being executed.
  • NT_USERNAME: Windows user name for the process, if using Windows Authentication, or a trusted connection.
  • LOGINAME: Login name

But, IMHO, for a better understanding of your SQL Server usage, this query is the best one:

SELECT db_name(dbid) as Database_Name, count(dbid) as Connections,
loginame as Login_Name
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
order by Connections desc

The output will be grouped by database ID and then by username, for a simpler reading.

If you are not sure of which SQL Server version you have, run this query: select SERVERPROPERTY('ProductVersion'). This will allow to adapt the previous queries to your version. It will return your SQL Server version. Possible values are:

  • 8.00.194 for SQL Server 2000 No Service Pack
  • 8.00.384 for SQL Server 2000 Service Pack 1
  • 8.00.532 for SQL Server 2000 Service Pack 2
  • 8.00.760 for SQL Server 2000 Service Pack 3a
  • 8.00.2039 for SQL Server 2000 Service Pack 4
  • 9.00.1399.06 for SQL Server 2005 No Service Pack (RTM)
  • 9.00.2047.00 for SQL Server 2005 Service Pack 1
  • 9.00.3042.00 for SQL Server 2005 Service Pack 2
  • 9.00.4035.00 for SQL Server 2005 Service Pack 3
  • 10.00.1600.22 for SQL Server 2008 RTM
  • 10.00.2531.00 for SQL Server 2008 Service Pack 1
  • 10.00.4000.00 for SQL Server 2008 Service Pack 2
  • 10.50.1600.10 for SQL Server 2008 R2 RTM
I hope this post will help you. Do not hesitate to leave comments or questions.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...