Daily Archives: April 23, 2026

Two SQL Server Tools I Always Use for Troubleshooting and Performance Analysis

When troubleshooting Microsoft SQL Server environments—especially under pressure—having the right tools can make all the difference. Over time, I’ve found myself consistently relying on two lightweight but incredibly powerful tools that provide insight into what’s happening right now and help build historical context for analysis.

In this post, I’ll walk through the two tools I use the most:

  • sp_WhoIsActive (by Adam Machanic)
  • Extended logging setup using sp_WhoIsActive (inspired by Thomas S. Williams)

sp_WhoIsActive – Real-time insight into SQL Server

👉 http://whoisactive.com/

If you’re working with SQL Server and not using sp_WhoIsActive, you’re making life harder than it needs to be.

This stored procedure gives you a real-time snapshot of what’s happening inside your SQL Server instance:

  • Currently running queries
  • Wait types
  • Blocking chains
  • CPU and memory usage
  • Query execution plans

Example usage:

EXEC sp_WhoIsActive;

But where it really shines is when you start adding parameters:

EXEC sp_WhoIsActive
@get_plans = 1,
@get_blocking_info = 1,
@get_outer_command = 1;

Building History with sp_WhoIsActive Logging

This gives you a much deeper understanding of what’s going on—especially useful when dealing with blocking or long-running queries.

👉 https://thomasswilliams.github.io/sqlserver/2020/12/02/spwhoisactive.html

Real-time data is great—but when troubleshooting intermittent issues, you need history.

That’s where logging sp_WhoIsActive output becomes extremely valuable.

Using the approach described by Thomas S. Williams, you can:

  • Store snapshots of activity in a table
  • Schedule it via SQL Server Agent
  • Build a historical dataset for analysis

This allows you to answer questions like:

  • What happened last night at 02:00?
  • Which queries were causing blocking?
  • Was there unusual CPU or IO activity?

Example setup:

EXEC sp_WhoIsActive
@get_plans = 1,
@destination_table = 'dbo.WhoIsActiveLog';

Once you log data regularly (e.g. every minute), you essentially create your own lightweight monitoring system.


🧠 Why I use these tools

These two tools together give me:

  • 🔎 Real-time visibility when something is wrong
  • 📊 Historical insight when investigating past incidents
  • Fast troubleshooting without needing heavy monitoring tools

They’re simple, flexible, and incredibly effective.

💡 Final thoughts

You don’t always need expensive monitoring solutions to troubleshoot SQL Server effectively.

With sp_WhoIsActive and a simple logging setup, you can get:

  • Deep insight into query behavior
  • Visibility into blocking and waits
  • A historical baseline for performance

If you’re not already using these tools—I highly recommend adding them to your toolbox.


👉 I regularly use setups like this when troubleshooting large queries and performance issues across environments.