Slow SQL Server? Three Queries to Run in the First Minute

A slow SQL Server needs a quick look before any deep tuning. Three queries give you that look in about a minute. They show what’s running, who is holding everyone up and what the server is waiting on. They use only what SQL Server ships, with nothing extra to install.

Stage a Slowdown First

Reading about these queries is easier with a real problem in front of you. So I built one on SQL Server 2025, on a test server. Create a small database with 100,000 accounts.

CREATE DATABASE TriageLab;
GO
USE TriageLab;
CREATE TABLE dbo.Account (AccountID int NOT NULL PRIMARY KEY, Balance decimal(12,2) NOT NULL);
INSERT dbo.Account (AccountID, Balance)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 100
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
SELECT AccountID, Balance INTO dbo.AccountCopy FROM dbo.Account;

Now open four query windows and run one script in each, in this order. The first window starts a transaction and never finishes it. It’s a classic cause of a sudden pile-up.

-- Window 1: change one row, then leave the transaction open
USE TriageLab;
BEGIN TRAN;
UPDATE dbo.Account SET Balance = Balance + 1 WHERE AccountID = 7;

-- Window 2: change the same row
USE TriageLab;
UPDATE dbo.Account SET Balance = Balance - 1 WHERE AccountID = 7;

-- Window 3: a report over the whole table
USE TriageLab;
SELECT COUNT(*) AS accounts, SUM(Balance) AS total_balance FROM dbo.Account;

-- Window 4: forty seconds of pure CPU work on a copy of the table
USE TriageLab;
DECLARE @stop datetime2 = DATEADD(SECOND, 40, SYSDATETIME()), @x bigint;
WHILE SYSDATETIME() < @stop
    SELECT @x = SUM(CAST(CHECKSUM(AccountID, Balance) AS bigint)) FROM dbo.AccountCopy;

Windows 2 and 3 now wait and don’t finish. Open a fifth window for the three queries below. The numbers I quote came from my run, about twelve seconds after the last window started.

Query 1: What Is Running Right Now

SELECT r.session_id,
       r.status,
       r.wait_type,
       r.wait_time AS wait_ms,
       r.blocking_session_id AS blocked_by,
       r.cpu_time AS cpu_ms,
       r.total_elapsed_time AS elapsed_ms,
       DB_NAME(r.database_id) AS database_name,
       LEFT(t.text, 80) AS query_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

It returned three rows, and each one told a different story. I ran the whole scene twice, so the table shows both runs.

Read the CPU and elapsed columns together. The loop used about 11,970 milliseconds of CPU in about 11,970 milliseconds, so it was busy the whole time. The update and the report used almost no CPU at all. They were waiting on locks, and the blocked_by column says on whom.

Look at the report’s blocked_by value. In one run it pointed at Window 1, and in the other at the waiting update. The column names the nearest session in the way, and that session can be stuck itself. Window 1 doesn’t appear at all, because it isn’t running anything. That’s why the next query follows the chain to its front.

Query 2: Who Is at the Head of the Line

Blocking forms chains, and only the session at the front matters. This query follows each chain back to its head and counts everyone stuck behind it.

WITH waiting AS
(
    SELECT r.session_id, r.blocking_session_id AS head_blocker
    FROM sys.dm_exec_requests AS r
    WHERE r.blocking_session_id > 0
      AND NOT EXISTS (SELECT 1 FROM sys.dm_exec_requests AS b
                      WHERE b.session_id = r.blocking_session_id AND b.blocking_session_id > 0)
    UNION ALL
    SELECT r.session_id, w.head_blocker
    FROM sys.dm_exec_requests AS r
    JOIN waiting AS w ON r.blocking_session_id = w.session_id
)
SELECT s.session_id AS head_blocker,
       s.status,
       s.open_transaction_count AS open_transactions,
       COUNT(*) AS sessions_waiting,
       DATEDIFF(SECOND, s.last_request_end_time, SYSDATETIME()) AS idle_seconds,
       s.program_name,
       LEFT(t.text, 80) AS last_query
FROM waiting AS w
JOIN sys.dm_exec_sessions AS s ON s.session_id = w.head_blocker
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
GROUP BY s.session_id, s.status, s.open_transaction_count, s.last_request_end_time, s.program_name, t.text;

It found Window 1. The session was sleeping, with one open transaction and two sessions waiting behind it. It had been idle for 17 to 18 seconds, and its last query was the UPDATE. The program name shows which application owns it.

That’s the pattern to remember. The session causing the trouble isn’t busy. It finished its statement and walked away with the transaction still open. On a real server, the fix is to find the owner and have the application commit or roll back. KILL is the last resort: it rolls the transaction back, and a big rollback takes time.

Query 3: What the Server Is Waiting On

SELECT w.wait_type,
       COUNT(*) AS tasks_waiting,
       MAX(w.wait_duration_ms) AS longest_wait_ms
FROM sys.dm_os_waiting_tasks AS w
JOIN sys.dm_exec_sessions AS s ON s.session_id = w.session_id
WHERE s.is_user_process = 1
  AND w.session_id <> @@SPID
GROUP BY w.wait_type
ORDER BY tasks_waiting DESC, longest_wait_ms DESC;

Both runs showed the same two waits. LCK_M_S had two tasks from the report, and LCK_M_X had one task from the update. The CPU loop wasn’t on the list, because it wasn’t waiting. It was working.

The wait name tells you which direction to look next. LCK_M waits mean blocking, so go back to Query 2. PAGEIOLATCH waits mean pages are being read from disk. WRITELOG means the log disk is slow. CXPACKET and CXCONSUMER come from parallel queries. When the busy sessions show no waits and high CPU, the server is short on CPU. Query 1 tells you which query is using it.

Clean Up

Go back to Window 1 and roll it back. Windows 2 and 3 finish right away. In my runs, the report then showed a total of 9,999,999.00. The rolled-back change disappeared, and the change from Window 2 remained.

-- In Window 1
ROLLBACK;
-- When every window is done
USE master;
ALTER DATABASE TriageLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TriageLab;

The first minute is not for fixing, it is for knowing where to look.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.