Quick Performance Troubleshooting Script for SQL Server

When your SQL Server starts to experience performance issues, it’s important to have tools at your disposal to quickly identify and address the root causes.

One such tool is having a performance troubleshooting scripts that provides some insights into SQL Server’s current state; including worker threads, running queries, and various performance metrics.

In this blog post, I’m sharing some scripts that will help check in on your SQL Server performance and problem processes. Diagnosing performance issues in SQL Server is complex, and every issue is unique to your own environment. The tips I share here are based on my experience in the field troubleshooting such issues. It’s aimed to help you see the issue at-hand, get you out of the danger zone, and live another day so we can investigate the root cause while things are operational.

The following is included in this article:
1. Get SQL Worker Threads
2. Show Currently Running SQL Queries
3. Additional Notes & Follow-up Tasks

MSSQL Performance Troubleshooting Scripts

1. Get SQL Worker Threads

This part retrieves the current count of SQL worker threads, providing visibility into the server’s overall workload and resource utilisation.

-- Get SQL worker threads
SELECT SUM(current_workers_count) as [Current Worker Threads] FROM sys.dm_os_schedulers

In an unhealthy scenario, thread starvation may occur, leading to increased wait times and degraded performance.

During my current role and experience, if threads are above 600/700 things are nearing danger and a MSSQL Team on call alert may have been triggered. It means we have to check in on the SQL Server activity, look at processes and figure out what’s causing SQL to be throttled. More explained on this in the next part.

Understanding good and normal thread counts takes understanding over time. Have a look at this Microsoft article on the recommendations with SQL Server thread configurations. If you want to get to know your ideal number(s) take your time with it, monitor/check current worker thread counts, and read the MS docs.

2. Show Running SQL Queries

Next up, I’m sharing a SQL script displays information about the queries currently running on the SQL Server instance. This includes details such as the hostname the connection is coming from, as well as usernames, session ID’s, program names, wait types, and more.

By running this script we can see all the active queries at the time of execution, and using the information displayed within the columns we can easily get a picture of what’s happening. We’ll be able to see the longest running queries, and the number of rows returned is always a point of interest, if there’s 100’s of rows it might be harder to find a root cause.

-- Show current sessions
SELECT 
     s.host_name, 
     s.program_name, 
     r.blocking_session_id 'Blk by', 
     Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' +
     Quotename(Object_name(st.objectid,st.dbid)), '') AS command_text, 
     s.session_id, 
     r.cpu_time, 
     r.logical_reads, 
     r.reads, 
     r.status, 
     r.wait_type, 
     wait_resource, 
     r.wait_time / (1000.0) 'Wait (in sec)', 
     r.writes, 
     r.total_elapsed_time / (1000.0) 'Elapsed (in sec)', 
     SUBSTRING(st.TEXT,(r.statement_start_offset / 2) + 1, 
     ((CASE r.statement_end_offset 
          WHEN -1 
          THEN Datalength(st.TEXT) 
          ELSE r.statement_end_offset 
          END - r.statement_start_offset) / 2) + 1) AS statement_text, 
     r.command, 
     s.login_name, 
     s.last_request_end_time, 
     s.login_time, 
     r.open_transaction_count 
FROM  sys.dm_exec_sessions AS s 
JOIN sys.dm_exec_requests AS r 
ON r.session_id = s.session_id 
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
ORDER BY r.cpu_time DESC;

If the script to show running queries returns a large number of rows, indicating a high level of activity, it suggests that the server is busy processing numerous concurrent queries, potentially leading to resource saturation and system instability. The reason you are seeing so many active sessions may also be due to the root issue, we should look for blocking SPIDs and find the lead blocker.

SQL Server DBAs must monitor worker thread counts and query activity closely. Interpreting results effectively to proactively address performance issues is a key part of our day to day role to ensure optimal database operations.

3. Additional Notes & Follow-Up Tasks

Here are some suggestions on what we can be doing if troubleshooting a critical performance issue in SQL Server:

Try optimise/recompile Stored Procedures
Use sp_recompile to recompile stored procedures periodically, ensuring optimal execution plans. As indexes or statistics change, compiled procedures, triggers, and functions may become less efficient. Recompiling them can reoptimise queries for better performance.

Identified a problem SQL query (Blocking/Locking/Resource Intense)?
You might need to kill the SQL Session (SPID) for various reasons, including blocking, locking or resource heavy queries. My other post as linked here contains good info on terminating SQL Server sessions.

Is your SQL Server part of Always On, Replication or Mirroring?
The following scripts/articles may help identify if you have issues with High Availability SQL Features.
1. Get Always On Availability Group (AAG) Latency
2. Get Mirroring Status & Latency
3. Get Replication Latency

Reindexing/Updating Statistics
Regularly maintain indexes and update statistics to ensure efficient query execution. This includes adding new indexes where required. Index optimisation should happen on regular schedules during non-busy periods.

I hope these suggestions and explanations are helpful, cheers.