In this post I’m sharing a SQL script that will show currently running and blocking queries in SQL Server, with some other useful info along the way.
This is script I run often as a DBA. It’s useful for identifying blocking or long running transactions, or checking in on what’s throttling your server. We can use this information to get a view of what’s causing our SQL Server to run slow.
The following areas are covered in this one:
> Script to Show Current SQL Sessions
> Other Ways to Show SQL Activity
> Problem Queries or Performance Issues
Script to Show Current SQL Sessions
This script will show currently running SQL sessions, including commands being run, blocking sessions and other useful columns for review.
We can re-run the query a few times to get a better picture of the active queries coming and going. If it’s a busy SQL Server you might see 100’s of rows returned by this, some might have been running for a long time which we can see by reviewing the elapsed time column.
Other Ways to Show SQL Activity
I wanted to quickly highlight a few other ways to get this same SQL activity information.
sp_who2: A built-in SQL Server stored procedure that provides a quick overview of active sessions, including session IDs, status, and blocking information.
sp_whoisactive: A popular community-provided stored procedure that offers detailed insights into currently running queries, waits, and resource usage.
SQL Activity Monitor: A graphical tool in SQL Server Management Studio (SSMS) that displays active queries, performance metrics, and resource usage in real time.
3rd Party Tools: Monitoring solutions like SolarWinds or Redgate, which offer advanced analytics, alerting, and customizable dashboards for SQL activity.
Problem Queries or Performance Issues
When dealing with problem queries or performance issues in SQL Server, you might need to terminate problematic SPIDs. I’ve shared the syntax and guidelines in my post on killing SQL SPIDs—definitely check that out first to handle it properly.
For broader performance troubleshooting, take a look at my Performance Troubleshooting Scripts post. It also includes follow-up links for checking and maintaining high availability setups like Mirroring, Replication, and Availability Groups (AG).
Leave a Reply