Our SQL Server is Slow! What Do I Do First?

Don’t Panic!

It’s a vague but common complaint, frequently with no additional details. Before blindly restarting SQL Server, let’s walk through quick checks that don’t require deep DBA skills.

Step 1: Is the Server Itself Struggling?

Before blaming SQL, check system-level resources

• Check:
  • Is CPU pinned at 100%? SQL Server could be a victim of something else on the box
    • Task Manager is your friend for CPU…less so for memory
  • Is SQL Server using 100% of the memory, and starving windows? It should be capped to maybe 90%
  • Are disk latencies/usage high? Use Resource Monitor to check this
• Quick Fixes:
  • If CPU is the problem, find the process consuming it. SQL Server? Another app?
  • If memory is an issue, see if SQL is configured to take everything (default setting is 2 PB).
  • Low disk space? It won’t typically cause slow performance but it can cause errors.

Step 2: What Queries Are Running Right Now?

• Use sp_whoisactive. This free tool has helped solve thousands of “slow” complaints over many years
  • Look for:
    • Long-running queries
    • Blocked Processes
    • High CPU usage queries
    • Locking issues
• SQL Server has a bunch of reports at the instance level, as well as a Performance Dashboard:

• Quick Fixes:
  • If a query has been running for hours and shouldn’t be, investigate before killing it.
  • If blocking is an issue, find the lead blocker. It’s frequently the real problem. There are “lead blocker” scripts all over the internet
    • I don’t recall where I got this one, but it works:

      • 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 3: Are There Index Problems?

• SQL Server needs good indexing. Missing or fragmented indexes = bad performance.
  • Check for missing indexes:
    • SELECT * FROM sys.dm_db_missing_index_details
  • Check for fragmentation:
    • SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’)
      • If you are on SSDs, this is very unlikely to be the issue.
• Quick Fixes:
  • If missing indexes show up, don’t blindly create them—validate first.
  • Rebuilding/reorganizing indexes might help, but don’t do it blindly on huge tables.

Step 4: Is TempDB a Bottleneck?

• If TempDB is under stress, everything suffers.
• Check for open/hung transactions:
• DBCC OPENTRAN
  • Long-running transactions can fill up tempdb and transaction log files.
• Quick Fixes:
  • Verify you have proper number of TempDB data files (this is a specific fix for allocation performance issues).
  • Restarting SQL clears TempDB, but that’s a temporary fix.

Step 5: Query Store

• If Query Store is enabled, use it to find consistently bad queries.
• Look for:
  • High resource-consuming queries
  • Execution plans that suddenly changed
• Quick Fix:
  • For unstable plans, forcing a known good plan might be a short-term fix.

The Bottom Line: Don’t Panic! Measure First

• Gather stats before you restart – outside of Query Store, SQL Server logs almost nothing to help you troubleshoot.
• Most slowdowns have a root cause rather than “just SQL being slow.”
• Use these steps to find where the problem is before making changes.
• If these checks don’t point to a clear fix? That’s when you bring in a DBA.