Understanding the Difference Between the What Is Active and Open Transactions Reports in Database Health Monitor
When troubleshooting SQL Server Performance, it’s common to wonder why Database Health Monitor includes both the What Is Active report and the Open Transactions report. At first glance, they might seem to provide similar insights—but in reality, they highlight very different aspects of what’s happening inside your SQL Server.
What Is Active — See What’s Running Right Now
The What Is Active report is designed to give you a real-time look at what SQL Server is currently working on. It shows queries that are actively running for more than half a second—filtering out the lightning-fast queries that complete before you’d ever notice them.
If your SQL Server is healthy and keeping up with demand, this report might often appear empty or show just a few short-running queries. But when your server is struggling—perhaps due to blocking, resource contention, or inefficient queries—the What Is Active list will start to grow. It’s a quick way to spot which queries are consuming resources right now and helps pinpoint where your bottlenecks are happening in real time.
Open Transactions — Find What’s Been Left Hanging
The Open Transactions report takes a different approach. Instead of showing what’s actively executing, it shows transactions that have started but have not yet been committed or rolled back. These open transactions might not be running any code at the moment—they could be sitting idle, waiting for user input, or simply forgotten due to a missed COMMIT.
Long-running open transactions can cause a range of issues:
- Blocking other queries from completing.
- Preventing log truncation, leading to growing transaction logs.
- Increasing tempdb usage due to version store buildup.
In short, open transactions can quietly cause performance and maintenance headaches even when nothing seems to be running.
Why Two Reports Are Better Than One
While both reports deal with activity in SQL Server, they focus on two distinct types of problems:
- The What Is Active report helps identify queries that are currently executing and consuming system resources.
- The Open Transactions report helps identify transactions that are lingering in the background, potentially holding locks or preventing cleanup, even when no active query appears to be running.
It’s possible—and fairly common—for an open transaction not to appear in the What Is Active report. This happens when a session begins a transaction but then sits idle. SQL Server considers that session inactive because it’s not currently running a command, yet the open transaction still affects the system’s behavior behind the scenes.
Using Both Reports Together
When diagnosing performance issues:
- Start with What Is Active to see if anything currently running is slowing things down.
- If the server seems idle but performance problems persist—like blocking or growing transaction logs—switch to the Open Transactions report. It might reveal sessions that have left transactions open and are indirectly causing trouble.
By combining both views, you can get a complete picture of what’s happening on your SQL Server—both in motion and at rest.
