Monitor SQL Server Connections

Extended Events (XEvents) in Microsoft SQL Server is a lightweight and flexible event-handling framework that allows administrators and developers to collect and analyze performance and troubleshooting data. It serves as a powerful alternative to traditional tools like SQL Server Profiler and SQL Trace, offering enhanced capabilities with minimal performance overhead. Here’s how Extended Events works and its core components: 1. Core Concepts of Extended Events

• Events:
  • Events represent actions or occurrences within SQL Server that you want to monitor, such as SQL query execution, deadlocks, or memory usage.
  • Each event has attributes or "fields" that provide detailed context about the occurrence.
• Targets:
  • Targets are the destinations where captured event data is stored or processed. Common targets include:
    • File: Saves events to a file for later analysis.
    • Ring Buffer: Stores event data in memory in a circular buffer.
    • Event Counter: Tracks the number of times a specific event occurs.
• Actions:
  • Actions are tasks or operations performed in response to an event. For example, collecting additional data about the event context.
• Predicates:
  • Predicates are filters that allow you to capture only the events you're interested in, reducing the amount of data collected.
• Packages:
  • Packages are logical groupings of events, actions, and targets. SQL Server provides predefined packages for common scenarios.

2. How Extended Events Work

1. Session Creation:
   • You create an Extended Events session to define which events you want to capture and how you want them stored.
   • A session can have one or more events, actions, and targets.
2. Data Collection:
   • When an event occurs, it is captured based on the session's configuration.
   • Filters ensure only relevant data is captured, reducing overhead.
3. Data Storage:
   • Collected event data is stored in the specified targets for analysis. For example:
     • Use the Live Data Viewer in SQL Server Management Studio (SSMS) to view data in real time.
     • Store data in files for historical analysis using tools like the XEvent Profiler.
4. Analysis:
   • Analyze captured data to identify patterns, troubleshoot issues, or optimize performance.

3. Advantages of Extended Events

• Lightweight and Scalable:
  • Extended Events are designed to minimize the performance impact on the SQL Server instance, even under heavy workloads.
• Rich Event Library:
  • Provides a comprehensive set of events for deep monitoring and troubleshooting.
• Customizable:
  • Fine-grained control over what data is captured using filters and predicates.
• Integration with Tools:
  • Fully integrated with SQL Server Management Studio (SSMS) for easy session creation and data analysis.
• Multiple Targets:
  • Flexible options for storing and processing captured data.

4. Practical Usage of Extended Events

• Troubleshooting Performance Issues:
  • Capture long-running queries or high CPU usage events.
• Detecting Deadlocks:
  • Use the xml_deadlock_report event to capture and analyze deadlock details.
• Monitoring Query Execution:
  • Track query execution time, wait statistics, and resource usage.
• Auditing and Compliance:
  • Monitor specific actions, such as login attempts or schema changes.

5. Example: Creating an Extended Events Session Here’s an example of creating a session to capture long-running queries:

CREATE EVENT SESSION LongRunningQueries 
ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text)
    WHERE sqlserver.duration > 10000000 -- Duration in microseconds
)
ADD TARGET package0.event_file (
    SET filename = 'C:\XEvents\LongRunningQueries.xel'
)
WITH (STARTUP_STATE = OFF);

-- Start the session
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;

-- Stop the session
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = STOP;

6. Viewing and Analyzing Data

• Use the Extended Events GUI in SSMS:
  • Navigate to Management > Extended Events > Sessions.
  • Right-click a session and choose Watch Live Data to view events in real-time.
• Analyze collected .xel files using:
  • SSMS
  • Tools like PowerShell or third-party log analysis tools.

7. Tools and Integration

• SSMS Extended Events Wizard:
  • Provides a user-friendly interface for creating and managing sessions.
• Dynamic Management Views (DMVs):
  • Query system DMVs like sys.dm_xe_sessions and sys.dm_xe_objects to explore session details programmatically.

Extended Events is an essential tool for diagnosing and optimizing SQL Server performance in modern environments, offering unparalleled depth and flexibility compared to legacy tools.

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.