What are the applications connected to MSSQL

  • As a dba, you should keep track of which applications are connected to your SQL Server.
  • Some applications will do their best to reduce the work load of the SQL Server, some others such as Excel 2007 will do their best to grind an SQL Server to it's knees.
  • The old way of keeping track of these applications was sp_who and sp_who2. But these are old stored procedures that will eventually be deprecated.
  • Microsoft SQL Server provides a dynamic table: sys.sysprocesses for MSSQL 2005 & MSSQL 2008, while it was called just sysprocesses under MSSQL 2000.
  • sys.sysprocesses keeps track of all the processes.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Applications connected

  1. USE master;
  2. go
  3.  
  4. SELECT CAST(program_name AS VARCHAR(48)) AS 'Application',
  5.        COUNT(*) AS 'Connections'
  6. FROM sys.sysprocesses
  7. WHERE ecid=0
  8. GROUP BY program_name
  9. ORDER BY 'Connections' DESC;
  10. go
Application                                      Connections
------------------------------------------------ -----------
                                                 21
Microsoft Office 2003                            2
Microsoft SQL Server Management Studio           1
Microsoft SQL Server Management Studio - Query   1

(4 row(s) affected)
  • ecid: is the Execution Context Id. It's the execution context ID of any given thread associated to a specific SPID.
  • ecid: 0 means that the main thread or the parent thread.
  • ecid: >0 means a sub-thread.
  • The applications being blank with 21 connections are the MSSQL own internal processes.