SQL Server Is Slow, part 3 of 4

Have you read parts 1 and 2?

Author

Kevin Hill
September 19, 2025

If coffee tariffs go up, what is my next best option?

In parts 1 and 2 of this series, we’ve gathered info and done the triage just like anyone in almost any industry does

At this point you’ve:

  • Defined what “slow” means and built a timeline (Part 1).

  • Checked things outside SQL Server like network, storage, and VM noise (Part 2).

Now it’s time to open the hood on SQL Server itself. Fire up SSMS! (Version 21 is amazing…)

Step 1: Check Active Sessions

Run a quick active queries check (sp_whoisactive is a favorite):

  • Who’s running queries right now?

  • What queries have been running the longest? Is that normal?

  • Any blocking chains?

  • Are any queries hogging resources?

At this stage, you’re only identifying potential offenders. Next issue, we’ll dig into queries and indexes more deeply. 


-- Lead Blocker script
-- This is code from the internet
-- Understand it and test before using in production


SELECT
    spid
    ,sp.STATUS
    ,loginame   = SUBSTRING(loginame, 1, 12)
    ,hostname   = SUBSTRING(hostname, 1, 12)
    ,blk        = CONVERT(CHAR(3), blocked)
    ,open_tran
    ,dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10)
    ,cmd
    ,waittype
    ,waittime
    ,last_batch
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
    ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

Step 2: Look at Wait Stats

Wait stats tell you what SQL Server has really been waiting for (everything in SQL Server is waiting for something else):

  • PAGEIOLATCH: slow storage reads.

  • LCK_M_X: blocking/locking.

  • CXPACKET/CXCONSUMER: parallelism info.

  • THREADPOOL: CPU threads

  • RESOURCE_SEMAPHORE: memory

  • ASYNC_NETWORK_IO: Probably more of a client side problem than SQL Side

This comprehensive list of wait types and explanations from my friends at SQL Skills.

We aren’t solving yet - we’re about categorizing where SQL feels the pain.

Step 3: Review Agent Jobs & Error Logs

SQL may already be waving red flags:

  • Overlapping or stuck Agent jobs. A long running purge job or index rebuild can cause all sorts of issues during the day.

  • Failed backups or CHECKDB runs. A failed CHECKDB could mean corruption. Read this

  • Errors or memory dumps tied to patching or system instability. Look in the same folder as your ERRORLOG location

Step 4: Don’t Forget the “Gotchas”

Other less obvious issues can cause system-wide drag:

  • High VLF count on a busy database, often from failed or missing log backups.

  • Instance/Database compatibility or config changes - check SSMS reports like:

  • Recent patching issues (especially if tied to errors or dump files).

These aren’t everyday culprits, but when they show up, they can cripple performance.

Step 5: Compare Against Your Baseline

Today’s “slow” may be tomorrow’s “normal.”

  • Track batch requests/sec, CPU Utilization, wait stats, I/O latency, and log file size/VLF count.

  • Without this baseline, every slowdown feels like a brand-new mystery.

 If you don’t already have a baseline, NOW is the time to start, while the server is healthy.

  • Collect I/O stats and wait stats regularly.

  • Run sp_Blitz for a full health snapshot (free tool from Brent Ozar)

    • Many of the sp_blitz scripts can be configured to run on a schedule and drop results to a table.

  • Capture DMV performance counters (sys.dm_os_performance_counters) on a schedule.

A baseline doesn’t need to be fancy, it just needs to exist, so you know what “normal” looks like before things go sideways.

The Bottom Line

Part 3 is about categorizing slowness inside SQL Server: sessions, waits, jobs, error logs, and configuration gotchas. Don’t jump straight into query rewrites yet. You’re still isolating the nature of the slowdown. Having a consistent process for this reduces panic and anxiety.

In Part 4, we’ll cover what to do when the culprit is truly inside SQL Server: look at queries, indexes, and design choices.

Amazing Brent Ozar training for ALL of your developers and sysadmins!

This purchase covers your ENTIRE staff, not just one or two and auto-renews each year.

Affiliate link. I get a commission if you buy.

SQL tidBITs:

Free community tools like Brent Ozar’s First Responder Kit and Glenn Berry’s Diagnostic Queries build on Microsoft’s DMVs and reports, giving you faster, deeper insights into what your SQL Server is really doing.

Security Advisories
September 2025 Patch Tuesday fixes 84 MS vulnerabilities including two zero-days — Patch available; Microsoft addressed two publicly disclosed zero-days plus eight Critical vulnerabilities in this month’s release. (crowdstrike.com)

SQL / Data Vendors & Platform News
What’s New in SQL Server 2025 Preview (RC 1) – Half-Precision Vectors, Copilot in SSMS, and More — New features include support for half-precision floats in vector data, faster vector index builds, and enhanced OPENROWSET & bulk insert.

Vector database Pinecone names new CEO Ash Ashutosh — Founder moves aside to focus on tech while new leadership pushes expansion of vector DBs in AI. (techtarget.com)

Major Tech Vendor Moves
Nvidia takes $5B stake in Intel and proposes chip tech collaboration — Strategic investment could reshape chip supply and collaboration between semiconductor giants. (reuters.com)

Samsung launches Galaxy S25 FE & the Tab S11 series — New premium-lite hardware expanding Samsung’s flagship lineup. (timesofindia.indiatimes.com)

Please share with your friend that doesn’t have a baseline of their SQL Server. Yes, you may be sharing to yourself ;)

Reply

or to participate.