Slow SQL Server: how to find the real cause before buying hardware

First: what kind of slowness are we talking about?

“The database is slow” can describe very different situations, and they don't share the same causes. Before running a single diagnostic query, it pays to pin down the symptom:

• Everything is slow, all the time: look at the instance configuration, memory or storage.
• It's slow at certain times of day: peak load, a scheduled job, or a backup or index maintenance job that runs at the wrong moment.
• One specific screen or process is slow: almost always a query, an index or an execution plan.
• It's been slow since a specific date: an application update, a migration, a compatibility level change, or a table that crossed a size threshold.

Also write down a concrete, dated example: “the orders screen took 14 seconds on Tuesday at 10:20 a.m.” One precise example is worth more than ten general impressions, and it lets you verify the improvement at the end.

Step 1: look at what SQL Server is waiting on

A query spends its time either working (CPU) or waiting for something: a page to read from disk, a lock held by another session, a write to the transaction log, memory. SQL Server keeps count of these waits, and they are the most reliable starting point for any diagnosis.

/* Heaviest waits since the last restart */
SELECT TOP (15)
    wait_type,
    waiting_tasks_count,
    CAST(wait_time_ms / 1000.0 AS decimal(12, 1))                AS total_wait_s,
    wait_time_ms / NULLIF(waiting_tasks_count, 0)                AS avg_wait_ms,
    CAST(100.0 * signal_wait_time_ms / NULLIF(wait_time_ms, 0)
         AS decimal(5, 1))                                       AS pct_cpu_wait
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
  AND wait_type NOT LIKE 'SLEEP%'
  AND wait_type NOT LIKE 'XE%'
  AND wait_type NOT LIKE 'BROKER%'
  AND wait_type NOT LIKE 'QDS%'
  AND wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'REQUEST_FOR_DEADLOCK_SEARCH',
                        'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'DIRTY_PAGE_POLL', 'WAITFOR',
                        'SP_SERVER_DIAGNOSTICS_SLEEP', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
                        'ONDEMAND_TASK_QUEUE', 'SOS_WORK_DISPATCHER')
ORDER BY wait_time_ms DESC;

These counters accumulate from the last restart. To analyze a specific period, take two snapshots an hour apart during the slowdown and compare them. Then look at two things: the total wait, which tells you where the server's time goes, and the average wait, which reveals rare but serious problems that the total hides.

Step 2: find the most expensive queries

In the vast majority of cases, a handful of queries account for most of the load. If Query Store is enabled on your databases (available since SQL Server 2016, and on by default for new databases since SQL Server 2022), its “Top Resource Consuming Queries” reports are the best tool. Otherwise, the plan cache gives you a first view:

/* Top 20 CPU-consuming queries (plan cache) */
SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000                          AS total_cpu_ms,
    qs.total_logical_reads                               AS total_reads,
    qs.total_elapsed_time / qs.execution_count / 1000    AS avg_duration_ms,
    DB_NAME(st.dbid)                                     AS database_name,
    SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
        (CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END
         - qs.statement_start_offset) / 2 + 1)           AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;   /* or total_logical_reads DESC */

Sort by total CPU, then by total reads, then by average duration: the same queries often come out on top. Keep in mind that this cache is cleared on every restart and under memory pressure, which is why a problem query may not show up in it. That is one of the reasons to enable Query Store.

Step 3: blocking, live

During a slowdown, this query shows the blocked sessions and the sessions blocking them:

/* Who is blocking whom, right now */
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000        AS wait_s,
    DB_NAME(r.database_id)    AS database_name,
    t.text                    AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Follow the chain up to the head blocker: the session that blocks the others without being blocked itself. It is often a transaction the application left open, or a heavy job launched in the middle of the day. For deadlocks, the system_health Extended Events session, which is on by default, keeps the graphs of the most recent deadlocks, so there is no need to wait for the next one to start the analysis.

When reads are blocked by writes, row versioning isolation (the READ_COMMITTED_SNAPSHOT option) removes much of that blocking: readers no longer block writers, and vice versa. Test it before you turn it on, because it puts more load on tempdb and can change how some applications behave.

Step 4: check memory and storage

A server that is short on memory keeps rereading the same pages from disk, and the resulting wait looks like a storage problem. First make sure SQL Server's max server memory is set (it isn't by default; see the default settings to change), then measure the actual latency of each file:

/* Average latency per database file */
SELECT
    DB_NAME(vfs.database_id)                               AS database_name,
    mf.type_desc                                           AS file_type,
    mf.physical_name,
    vfs.io_stall_read_ms  / NULLIF(vfs.num_of_reads, 0)    AS read_latency_ms,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)   AS write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY read_latency_ms DESC;

On decent SSD or NVMe storage, reads take a few milliseconds and transaction log writes stay around one or two. Read latencies consistently above 20 ms, or log writes above 5 to 10 ms, warrant a closer look at the storage or virtualization layer.

The causes we find most often

• Missing, ill-suited or redundant indexes. SQL Server's missing index suggestions are a useful hint, not a list to apply as is: they ignore existing indexes and the extra cost added to every write.
• Stale statistics on large tables, which lead the optimizer to bad estimates and therefore bad plans.
• Parameter sensitivity. A plan that is perfect for a customer with ten orders becomes a disaster for one with two million. Adding OPTION (RECOMPILE) everywhere is not the answer: Query Store lets you force a good plan, and SQL Server 2022 can keep several plans for the same query.
• Queries that prevent index use: a function applied to a column in the WHERE clause, an implicit type conversion, a LIKE that starts with a wildcard.
• Overly heavy maintenance: rebuilding every index every night or every Sunday burns a lot of I/O and log space for an often small gain, while updating statistics sometimes gets forgotten.
• A poorly configured instance: max server memory left uncapped, default parallelism settings, an undersized tempdb.

What about hardware?

Adding cores is the most expensive fix: SQL Server is licensed per core, and a query that reads a hundred times too many pages will do the same on a brand-new server. Hardware becomes the right answer when the measurements say so: CPU still saturated after tuning the most expensive queries, poor storage latency despite adequate memory, or data volumes that have genuinely changed in scale.

In summary

1. Pin down the slowness and write down a precise, dated example.
2. Read the wait statistics, both total and average.
3. Identify the handful of queries that generate most of the load.
4. Check blocking, memory and storage latency.
5. Fix one thing at a time, and measure the gain against the original example.

This is exactly the approach we take in our SQL Server performance tuning engagements: measure, isolate, fix, then prove the gain, without replacing the server when that isn't necessary.