- Accidental DBA
- Posts
- My SQL Server is Slow! What Do I Do First?
The SQL Server is Slow!
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.
Ask yourself:
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 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 me solve thousands of “slow” complaints over many years
Look for:
Long-running queries
Blocked Processes
High CPU usage queries
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:
-- This is code from the internet, test first!
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 = 0Step 3: Are There Index Problems?
SQL Server needs good indexing. Missing or heavily 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')
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:
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.
Logging sp_whoisactive to a table proactively is a great idea
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.
Get a free Health Check for your most important SQL Server today from Dallas DBAs.
My Recent LinkedIn Posts
SQL tidBITs:
SQL Server will take as much memory as you allow it on a busy system, up to and including memory that the operating system needs in order to function. The default install setting does not help you. Set it to 90% in the installer (SQL 2022+) or after the install. See memory configuration, Max Server Memory
Reply