Session Monitoring: A Practical Setup Guide

See who is connected, what is running, and what is blocking what; live, instead of reconstructing it from logs afterward.

If you use SQL Diagnostic Manager for SQL Server (SQL DM) every day for dashboards and alerts, you already have a live session view sitting one click away and most teams have never opened it. This guide covers what session monitoring shows you, when to reach for it, what to configure before you need it, and the exact click paths in both the desktop console and the 14.1 web console.

Why Session Monitoring Is Worth Your Time

When SQL Server performance is clearly degrading but nobody can say why, most teams’ first move is to pull logs and start reconstructing what happened. That works eventually, but it means investigating a problem that already occurred, using evidence that is already stale, while the underlying condition may still be actively getting worse.

Session monitoring closes that gap. Instead of reassembling a timeline after the fact, the Sessions tab gives you a live view of everything happening on an instance right now:

• Every current connection to the instance, including the login, host, application, and database.
• The specific query or batch each session is executing, not just that “a query is running.”
• How long each session has been running, so a session stuck for four minutes stands out immediately from one that started four seconds ago.
• CPU, memory, physical I/O, and wait time attributable to each individual session.
• Blocking relationships who the lead blocker is at the head of a chain, and who is waiting behind it.
• Idle vs. active and system vs. user sessions, so routine background noise does not obscure the sessions that matter.

When to Use It

Session monitoring is most valuable as a repeatable first move, not a last resort. Four situations cover almost all of it:

That last row is the difference between a fire extinguisher and an early-warning system. A few minutes reviewing active sessions and recent blocking history on your busiest instances, on a schedule, tends to surface the query worth tuning before it becomes today’s emergency.

Before You Start: Seven Considerations

1. There is nothing to install, and nothing to turn on for the live view

Because SQL DM is already collecting activity data on every monitored instance, the Sessions views are available as soon as an instance is added to monitoring. Collection is agentless and no additional software goes onto the monitored server, so the tool doing the watching does not become part of the performance problem it is meant to diagnose.

2. Blocking and deadlock detail is a separate switch

The live Sessions views work out of the box, but the Blocking Sessions Report and Deadlock Sessions Report; the ones that give you full process detail on the Alerts view depend on the Activity Monitor. If nobody has enabled Capture Blocking and Capture deadlocks on your instances, you will see that blocking happened without the detail behind it. Phase 1 of the step-by-step below fixes that.

3. Set the blocked process threshold to at least 5 seconds

Collecting blocked-process information is resource-intensive on SQL Server. When you enable Capture Blocking, SQL DM sets the blocked process threshold to 5 seconds automatically. IDERA recommends leaving it at 5 or higher; set it lower and your deadlock monitor effectively runs constantly. Note that changing this value in SQL DM also changes it on the monitored SQL Server instance itself.

4. Extended Events vs. SQL Trace

For instances running SQL Server 2012 or higher, the Activity Monitor uses Extended Events by default:  lightweight and efficient. Only fall back to SQL Trace for pre-2008 instances, and be aware that enabling SQL Trace collection can degrade performance on the monitored server. Blocking and deadlock capture both require SQL Server 2005 or greater.

5. Killing a session is a real production action

Kill Session is available directly from the Sessions views, which makes it fast, and fast is exactly why it deserves a rule. Trace the session first so you know what it is running and how long it has held its lock. A legitimate transaction that is three seconds from committing does not need to be killed; a runaway batch that has blocked twelve sessions for four minutes probably does. Decide in advance who on your team is authorized to do it.

6. Deadlocks will not appear in the blocking tree

Deadlocks are circular by nature and therefore have no lead blocker, so SQL DM does not display them in the Blocking Tree. They do appear in the Blocking chart as deadlock counts, and in the Deadlock Sessions Report. SQL DM also does not count self-blocking sessions as blocked or blocking sessions. Knowing this in advance saves you from concluding the tree is broken.

7. Decide which console your team will actually use

The desktop console and the 14.1 web console present sessions differently. The desktop console has the four-view Sessions tab plus Trace Session and Kill Session. The web console has a filterable Listing view and a Charts view, and is easier to hand to someone who does not have the console installed. Part 1 and Part 2 below cover each in turn.

Watch It First (Optional, 5 Minutes)

Part 1 Step-by-Step in the Desktop Console

Four phases. Phase 1 is one-time configuration per instance. Phases 2 and 3 are the workflow you will use during an incident. Phase 4 makes the alerts point you here.

Phase 1  Enable Blocking and Deadlock Capture

Do this once per monitored instance, ideally before you need it. The live views work without it; the detailed reports do not.

1. In the SQL Diagnostic Manager desktop console, find the instance in the Servers sidebar.
2. Right-click the instance and select Properties.
3. In the Monitored SQL Server Properties window, click the Activity Monitor tab.
4. Select Enable the Activity Monitor. Nothing below this point applies until this is checked.
5. Under non-query activities, select Capture Blocking (SQL 2005+). This is what populates the Blocking Sessions Report with full blocking and blocked process detail on the Alerts view.
6. Select Capture deadlocks (SQL 2005+) as well. Deadlock capture is what lets the deadlock alert show you SPID, host, login, client application, database, wait time, wait resource, lock object, lock mode, lock owners, and lock waiters instead of just “a deadlock occurred.”
   
7.  The Activity Monitor tab of the Monitored SQL Server Properties window, with Capture Blocking and Capture deadlocks enabled.
   

   The Activity Monitor tab of the Monitored SQL Server Properties window, with Capture Blocking and Capture deadlocks enabled.

8. Optionally select Capture Autogrow while you are here, it is the third non-query event type, and it feeds the autogrow alert with log and data growth detail.
9. Confirm the collection mechanism. For SQL Server 2012 and higher, leave Use Extended Events selected. Choose Use SQL Trace only for pre-2008 instances, and expect some performance cost on the monitored server.
   

   The Blocked Process Threshold field appears once Capture Blocking is enabled, and defaults to 5 seconds.

10. Check the Blocked Process Threshold value, which appears once Capture Blocking is enabled. It defaults to 5 seconds. Keep it at 5 or higher.
11. Click OK to save, then repeat for your remaining production instances.

Phase 2 Open and Read the Sessions Tab

This is the part most SQL DM users have never done. It takes two clicks.

1. Click the monitored SQL Server instance in the Servers sidebar.
2. Click the Sessions tab. It contains four views: Summary, Details, Locks, and Blocking.
   

   The Sessions tab in the SQL Diagnostic Manager desktop console, showing the Summary, Details, Locks, and Blocking views.
3. Start on Summary for orientation. It charts Response Time, Sessions, Blocked Sessions, and Lock Statistics (average wait time, deadlocks, requests, timeouts, waits, and wait time). If the Blocked Sessions chart is flat and response time is normal, your problem is probably not in sessions, go elsewhere and save yourself the detour.
4. Click Sessions > Details for the working view. Individual sessions appear in the grid at the top of the window. 
   

   he Sessions Details view: individual sessions in the grid at the top, per-session Connection, Usage, Lock, and Tempdb statistics at the bottom.

5. Select any session. The Details section at the bottom of the window fills in with in-depth statistics for that session: Connection, Usage, Lock, and Tempdb usage.
   

   Double-clicking a session opens Session Details: connection info, usage, last command, lock information, and TempDB usage.
6. Scan the grid for the three things that matter: sessions running unusually long, sessions consuming disproportionate CPU or physical I/O, and sessions with a value in the wait-time or blocked-by columns.
7. Use the Filter button to control which items the view shows. On a busy instance this is the difference between a readable grid and a wall of idle system sessions.
8. Right-click any session for the action menu: View Locks, Show Query History, Trace Session, Kill Session, Print the associated statistics, or Export them to Microsoft Excel.
9. If you need lock-level detail, switch to Sessions > Locks, which lets you build charts based on each lock present on the instance.

Phase 3 Work a Live Blocking Chain

This is the standout use case. Blocking happens when one session holds a lock another session needs; left undiagnosed it cascades into a chain where every symptom visible from outside just looks like generic slowness. Here is the sequence.

1. With the instance selected, click Sessions > Blocking.
2. Read the blocking tree. SQL DM builds it for you and identifies the lead blockers; a lead blocker is a session that blocks at least one other session, which may in turn be blocking others.
3. Navigate the tree to the session at the head of the chain. That is your investigation target, not the twelve sessions queued behind it.
4. Select the lead blocker and click Trace Session. The Session Trace window opens with an overview of the session (CPU Time, Row Count, Lock Wait Timeout, Physical Reads and Writes), the last command executed, and all the SQL Server statements.
5. Decide. Two outcomes: a legitimate transaction that will resolve itself in seconds, or a runaway session that is clearly the root cause. The trace output is what tells you which one you are looking at.
6. If it needs to go, select the session and click Kill Session. This works on both lead blockers and blocked sessions.
7. For the full picture, open the Blocking Sessions Report (Block Reports). Access it from the Blocking view, or from the Alerts view by right-clicking the alert and selecting Show Block Details. It gives you Session ID, host name, user name, application, database, last batch started, transaction ID, and open transactions for the blocking process, plus wait time and wait resource for each blocked process. Export to XML if you need to hand it off.
8. For deadlocks, open the Deadlock Sessions Report the same way. It adds status, execution context, last batch completed, last transaction started, transaction name, wait type, and wait resource. Export is in XDL format.
9. Read the Blocking chart for context: blocked sessions, lead blockers, and total deadlocks at a given point in time. Right-click it to print, save as an image, or export the data to Microsoft Excel; select Toolbar for chart type and color options.
10. For an incident that already ended, use the History Browser to rewind to the point in time the blocking alert fired and view the sessions that were involved. This is how you investigate the 2 a.m. alert at 9 a.m.
11. Note the pattern. A blocking chain that recurs on the same server, the same application, or the same time of day is a signal worth fixing structurally, not just clearing in the moment.

Phase 4 Wire the Alerts That Bring You Here

Session monitoring is reactive by default. Alerts are what make it proactive.

1. Right-click the instance in the Servers sidebar and select Configure Alerts.
2. In the Alert Configuration window, select the metric you want to edit from the list. Start with Blocked Sessions (Count) and Blocking Session Wait Time (Seconds)  those two are the ones that will actually send you to the Blocking view.
3. Set warning and critical thresholds that reflect your workload. There is no universal correct value; a reporting instance and an OLTP instance have very different definitions of an acceptable wait.
4. Add Oldest Open Transaction (Minutes) next. A transaction left open for twenty minutes is usually the cause of a blocking chain rather than a victim of one.
5. Repeat for the remaining session alerts you care about (see the reference table below). Where a metric supports per-database thresholds, use Add on the Configuration tab to set different values per database.
6. Put a recurring 15 minutes on someone’s calendar to open Sessions > Summary on your two or three busiest instances. This is the habit that converts session monitoring from a fire extinguisher into an early-warning system, and it requires no new tooling or license tier.

Part 2 The Same Workflow in the Web Console

The 14.1 web console presents sessions through the single instance dashboard. The information is much the same; the navigation and the filtering are different, and there is no Trace Session or Kill Session here; those live in the desktop console.

1. Open the web console and navigate to the single instance view for the instance you care about.
2. Click the Sessions tab. It contains two tabs: Listing and Charts.
3. Start on Listing. Configure the columns you want by clicking the options icon, hovering over the column option, and choosing your metrics. A useful starting set for troubleshooting: Status, Session ID, Blocked By, Blocking, CPU (ms), Physical I/O, Wait Time 
   

   The Sessions Listing view in the SQL DM 14.1 web console, with configurable diagnostic columns.(ms), Wait Type, Wait Resource, Open Transaction, User, Host, Database, Application, and Command.The Sessions Listing view in the SQL DM 14.1 web console, with configurable diagnostic columns.

4. Use the Session Filter to cut straight to the problem. Three options: Sessions (everything), Blocked, and Blocking. During an incident, click Blocking first, that is your lead-blocker shortlist.
   

   The Session Filter narrows the Listing view to all sessions, blocked sessions, or blocking sessions.
5. Narrow further with the Column Filter: select a column, click the options icon next to its name, select Filter, and enter or select your criteria. Each column has its own selection criteria.
   

   The Time Range Filter sets the start and end date and time for the session data shown.

6. Use the Time Range Filter to look at session state within a window rather than only right now. Adjust the start and end date and time. This is the web console’s answer to reviewing an incident after the fact.
   

   The Session Filter narrows the Listing view to all sessions, blocked sessions, or blocking sessions.
7. Double-click any session to open the Session Details window, which covers connection information, usage metrics, the last command used, lock information, TempDB usage, and space used in KB (allocated and deallocated).
   

   Double-clicking a session opens Session Details: connection info, usage, last command, lock information, and TempDB usage.
8. Switch to the Charts tab for the graphical view: Response Time (ms), Sessions (concurrent connections over time, including active, idle, and system), Blocked Sessions (blocked sessions, lead blockers, and total deadlocks), and Lock Statistics.
   
9. Use the Lock Statistics chart to identify which lock object type is dominating; AllocUnit, Application, Database, Extent, File, HoBT, Key, Latch, Metadata, Object, Page, RID, or Table. Persistent Object or Table locks point at a very different fix than persistent Key or RID locks.
10. When you find the session you need to trace or kill, switch to the desktop console for those actions.

Reference

Sessions Tab Views at a Glance (Desktop Console)

Session-Related Alerts

These are the alerts associated with the Sessions tab. Configure them by right-clicking the instance and selecting Configure Alerts.

What Session Monitoring Accelerates vs. What You Decide

Source Documentation

• Monitor sessions  the Sessions tab, its four views, and the associated alerts
• Get sessions performance details  the Details view and the right-click session actions
• Analyze blocked sessions  the Blocking view, blocking tree, Trace Session, Kill Session, and the Blocking and Deadlock Sessions Reports
• Set activity monitor options Capture Blocking, Capture deadlocks, Extended Events vs. SQL Trace, blocked process threshold
• View your SQL Server sessions information  the web console Listing and Charts tabs
• Configure alerts  the Alert Configuration window