Monitor SQL Server Connections

When users access SQL Server, they make a connection^[1] to SQL Server. All connections exist independently of one another, even if the same application makes multiple connections. SQL Server allows you to monitor the activity of each connection through SQL and SQL-EM.

SQL Enterprise Manager (SQL-EM) was replaced by SQL Server Management Studio (SSMS) since SQL Server 2005. Microsoft SQL Server includes a feature called "SQL Server Profiler" which can be used to monitor SQL Server connections. SQL Server Profiler is a graphical user interface tool that allows administrators and developers to create and manage traces of events that occur on an instance of SQL Server.
Using SQL Server Profiler, you can monitor various events related to SQL Server connections, such as connection and disconnection events, login failures, and user activities. You can also filter and customize the events that you want to monitor, and save and replay traces for analysis and troubleshooting purposes.
SQL Server Profiler has been deprecated since SQL Server 2012 and is now replaced with Extended Events. Extended Events provide a more efficient and flexible way of monitoring SQL Server connections and other events.

Extended Events is a lightweight, high-performance event monitoring system built into Microsoft SQL Server. It provides a mechanism for collecting and analyzing events that occur within SQL Server, including events related to SQL Server connections.
Here is how Extended Events works:

1. Event Session Creation: To begin, you create an Event Session, which is a collection of events that you want to monitor. You specify the events you want to capture, and the data columns you want to include in the captured events.
2. Event Filtering: Next, you can filter the events that you want to capture based on various criteria, such as a specific user, database, or object. This allows you to capture only the events that are relevant to your monitoring needs.
3. Event Collection: Once an Event Session is created and filters are applied, SQL Server starts collecting the specified events.
4. Event Analysis: After the events are collected, you can analyze them to identify patterns and diagnose issues. You can use various tools, such as SQL Server Management Studio, to view and analyze the captured events.
5. Event Actions: You can also define actions to be performed when specific events occur. For example, you can set up a trigger to send an email or execute a script when a particular event occurs.

Extended Events is a flexible and powerful tool that allows you to monitor a wide range of SQL Server events, including SQL Server connections. It offers better performance and efficiency than its predecessor, SQL Server Profiler, and provides a more granular level of control over event monitoring.

The simplest way to monitor how users and processes are connected with SQL is to use the system stored procedure^[2], sp_who.
This procedure will retrieve information about all active connections. The syntax of sp_who is as follows:

sp_who [[@login_name =] "login|spid|ACTIVE"]

While SQL key words are always case independent, stored procedure names' case sensitivity depends on the sort order installed.
You should always type stored procedure names in lower case. The one parameter for sp_who, @login_name, allows you to limit the results of sp_who to a specific login, or spid, or to all active processes.

In the sp_who output, you may see different types of status. The most common ones are:

1. background: Identifies this as a SQL Server task.
2. runable: The task would run if it could get access to the CPU.
3. running: The process is running, sleeping.
4. waiting: The process is waiting.

In the next lesson, another method for monitoring connections will be discussed.

[1]Connection: A person or application that has logged onto SQL Server.

[2]System stored procedure: A procedure stored in the master database which begins with sp_ that is used to perform system functions.