sql server hub / slow performance guide

SQL Server slow performance guide

SQL Server slow performance is a symptom people notice before the cause is known. The first job is to narrow it into waits, blocking, plans, indexing, tempdb, jobs, or workload timing.

Use this guide to collect safe read-only checks before changing indexes, server settings, query hints, or schedules. Slow is not a diagnosis; it is the starting point for a more careful review.

waits

blocking

plans

Guide

Guide~15 min readUpdated 4 Jul 2026

LinkedIn X Email

In this guide

01What causes SQL Server performance problems?
02When SQL Server slow performance is not one problem
03How do I find what is slowing down SQL Server?
04SQL Server slow performance waits check
05SQL Server slow performance blocking check
06SQL Server slow performance query and plan check
07SQL Server slow performance indexing pressure
08SQL Server slow performance tempdb pressure
09SQL Server slow performance job overlap
10What SQL Server slow performance review output should show
11What to fix first
12Need a SQL Server performance review?

What causes SQL Server performance problems?

SQL Server slow performance usually starts as a user complaint: screens hang, reports miss their window, jobs run late, or an API gets slower under normal load. That complaint is real, but it does not yet name the cause.

Useful diagnosis turns the complaint into a smaller class of problem. The first split is usually waits, blocking, query plans, indexing pressure, tempdb pressure, job overlap, or platform pressure. Once the class is clearer, the next check becomes much less random.

The common causes are waits, blocking, plan regression, indexing gaps, tempdb pressure, SQL Agent job overlap, storage latency, CPU pressure, memory pressure, and workload timing. Several can show up together, so the first pass should separate the cause class before changing indexes, server settings, or application code.

Class	What it means	Useful output
Waits	SQL Server is spending time waiting for locks, CPU, memory grants, storage, workers, or other resources.	Wait sample, workload timing, and top wait families.
Blocking	Sessions are waiting on locks held by other sessions.	Blocking chain, head blocker, transaction scope, and affected query text.
Plans and reads	Queries touch too much data, choose unstable plans, or regress after stats/data changes.	Query text, cached plan, Query Store data, reads, CPU, duration.
Indexing	Access paths create scans, key lookups, write pressure, or larger lock footprints than the workload can tolerate.	Plan review, index usage, missing-index context, write cost.
Tempdb	Concurrent work, spills, version store, or internal objects are putting pressure on tempdb.	Session allocation, task allocation, file growth, version-store context.
Job overlap	Agent jobs, ETL, reports, maintenance, or backups collide with user workload.	Job history, run duration, failed jobs, and business-hour overlap.

When SQL Server slow performance is not one problem

Slow pages, delayed reports, blocking chains, high CPU, long jobs, and tempdb growth can appear in the same week. That does not mean they have the same fix. A slow SQL Server review should avoid turning every symptom into one tuning bucket.

The safer move is to ask what changed, when the slowdown appears, which workload is affected, and which SQL Server details line up with the timing. A wait sample without workload timing is easy to overread. A slow query without plan context is easy to overfix.

How do I find what is slowing down SQL Server?

Start with the situation, not the tool. An active incident needs live requests, waits, blockers, and current statements. A recurring complaint needs timing, Query Store data, job history, and monitoring data. A single slow query needs plan review.

The first useful checks are active requests, current waits, blockers, Query Store, SQL Agent job history, tempdb usage, file latency, and monitoring data from the complaint window. That usually shows whether the slowdown is a live concurrency problem, a repeated query regression, scheduled-job overlap, or wider resource pressure.

The goal is not to collect every possible metric. The goal is to choose the first check that can narrow the symptom without making production more brittle.

Situation	First useful check	Why it matters
Active incident	Capture active requests, waits, blockers, and top running statements before changing settings.	Reduce current pain without losing the details needed for the next review.
Recurring pattern	Compare timing, workload path, waits, Query Store data, jobs, and deployment history.	Find the repeatable condition instead of tuning one memorable query.
Single slow query	Review query text, actual/estimated plan, reads, CPU, memory grant, parameter pattern, and indexes.	Decide whether the query is the problem or just where pressure is visible.
Broad system drag	Check waits, CPU, memory grants, storage latency, tempdb, worker pressure, and monitoring data.	Separate server pressure from application path, schedule, or indexing issues.
After a change	Compare plans, Query Store regressions, compatibility level, statistics, indexes, and job timing.	Decide whether rollback, plan correction, or workload cleanup is the next move.

SQL Server slow performance waits check

Wait stats help show where SQL Server has spent time waiting since the last restart or wait-stat reset. They are a map, not a verdict. Read them with workload timing and current symptoms.

Wait stats summary

Reads top cumulative waits after filtering common background waits.

SELECT TOP (30)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CASE
        WHEN waiting_tasks_count = 0 THEN 0
        ELSE wait_time_ms / waiting_tasks_count
    END AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    N'BROKER_EVENTHANDLER',
    N'BROKER_RECEIVE_WAITFOR',
    N'BROKER_TASK_STOP',
    N'BROKER_TO_FLUSH',
    N'BROKER_TRANSMITTER',
    N'CHECKPOINT_QUEUE',
    N'CHKPT',
    N'CLR_AUTO_EVENT',
    N'CLR_MANUAL_EVENT',
    N'DIRTY_PAGE_POLL',
    N'DISPATCHER_QUEUE_SEMAPHORE',
    N'EXECSYNC',
    N'FSAGENT',
    N'FT_IFTS_SCHEDULER_IDLE_WAIT',
    N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    N'LAZYWRITER_SLEEP',
    N'LOGMGR_QUEUE',
    N'MEMORY_ALLOCATION_EXT',
    N'ONDEMAND_TASK_QUEUE',
    N'PREEMPTIVE_XE_GETTARGETSTATE',
    N'PWAIT_ALL_COMPONENTS_INITIALIZED',
    N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
    N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
    N'QDS_ASYNC_QUEUE',
    N'REQUEST_FOR_DEADLOCK_SEARCH',
    N'RESOURCE_QUEUE',
    N'SERVER_IDLE_CHECK',
    N'SLEEP_BPOOL_FLUSH',
    N'SLEEP_DBSTARTUP',
    N'SLEEP_DCOMSTARTUP',
    N'SLEEP_MASTERDBREADY',
    N'SLEEP_MASTERMDREADY',
    N'SLEEP_MASTERUPGRADED',
    N'SLEEP_MSDBSTARTUP',
    N'SLEEP_SYSTEMTASK',
    N'SLEEP_TASK',
    N'SLEEP_TEMPDBSTARTUP',
    N'SNI_HTTP_ACCEPT',
    N'SP_SERVER_DIAGNOSTICS_SLEEP',
    N'SQLTRACE_BUFFER_FLUSH',
    N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    N'SQLTRACE_WAIT_ENTRIES',
    N'WAIT_FOR_RESULTS',
    N'WAITFOR',
    N'WAITFOR_TASKSHUTDOWN',
    N'XE_DISPATCHER_JOIN',
    N'XE_DISPATCHER_WAIT',
    N'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

Wait stats are cumulative since restart or wait-stat reset. High wait time should be compared with workload timing. Use the waits guide when the top waits need deeper interpretation.

SQL Server slow performance blocking check

Broad slowness is often lock waiting described in softer language. Check active requests before changing indexes or server settings. If there is a blocking chain, find the head blocker and transaction shape first.

Active requests, waits, and blockers

Shows currently executing requests, wait details, blockers, open transactions, reads, writes, and client context.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.open_transaction_count,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE r.session_id <> @@SPID
ORDER BY
    CASE WHEN r.blocking_session_id <> 0 THEN 0 ELSE 1 END,
    r.wait_time DESC,
    r.cpu_time DESC;

blocking_session_id identifies sessions waiting on another session. open_transaction_count helps spot long transaction scope. Use the blocking guide when a chain is active or recurring.

SQL Server slow performance query and plan check

When one statement dominates CPU, reads, writes, or duration, review the query text and plan before changing server-wide configuration. Cached plan data is useful, but it can be cleared by restart, memory pressure, recompilation, and cache churn.

A SQL Server slow query fix should start with the query text, actual or estimated plan, reads, CPU, memory grant, parameter pattern, indexes, Query Store history, and recent changes. Fixing the first missing-index suggestion or adding a query hint can make the next regression harder to explain.

Expensive cached statements

Reads high-cost cached statements with query text and cached execution plan.

SELECT TOP (50)
    qs.execution_count,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_elapsed_time / 1000 AS total_duration_ms,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.max_elapsed_time / 1000 AS max_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 statement_text,
    st.text AS batch_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Cache data is not a complete history. High reads can point to access-path waste. Use Query Store when historical regression data is needed and available.

Query Store state

Checks whether Query Store is enabled and writable for user databases.

SELECT
    d.name AS database_name,
    d.is_query_store_on,
    qso.actual_state_desc,
    qso.desired_state_desc,
    qso.readonly_reason,
    qso.current_storage_size_mb,
    qso.max_storage_size_mb
FROM sys.databases AS d
LEFT JOIN sys.database_query_store_options AS qso
    ON qso.database_id = d.database_id
WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
ORDER BY d.name;

Query Store can help compare query behavior over time. It may be disabled, read-only, or not retained long enough for the incident. Use it as historical context, not as a replacement for plan review.

SQL Server slow performance indexing pressure

Indexing pressure is not only missing indexes. It can be scans, key lookups, stale statistics, write overhead, wide included columns, or plans that touch too much data for the concurrency level.

Missing-index DMVs can provide context, but they do not understand the full workload, write cost, existing indexes, or business timing. Do not create indexes blindly from suggestions.

Missing-index context

Reads high-impact missing-index suggestions as context for plan review.

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS object_name,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
ORDER BY
    (migs.user_seeks + migs.user_scans) * migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) DESC;

Treat this as a starting point for index review. Compare with existing indexes and real query plans before creating anything. Use the indexing guide for write/read tradeoffs.

SQL Server slow performance tempdb pressure

Tempdb pressure can come from user objects, internal worktables, spills, version store, maintenance, reporting, and concurrent workload. A slow system with tempdb pressure may need query, indexing, isolation, or scheduling review before file layout changes.

Tempdb session allocation

Shows sessions with current user-object and internal-object allocation in tempdb.

SELECT TOP (50)
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    r.wait_type,
    r.blocking_session_id,
    (su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8 AS user_object_kb,
    (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8 AS internal_object_kb,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes
FROM sys.dm_db_session_space_usage AS su
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = su.session_id
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = su.session_id
WHERE s.session_id <> @@SPID
ORDER BY
    ((su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count)
     + (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count)) DESC;

Large internal allocation can point to sorts, hashes, spools, or spills. Large user-object allocation can point to temp tables or table variables. Use the tempdb guide when file setup, growth, or version store also needs review.

SQL Server slow performance job overlap

SQL Agent jobs can make normal workload look broken when maintenance, ETL, reports, backups, or imports overlap with business use. `msdb` stores SQL Agent job history, which gives a useful first timing check.

Recent failed or long SQL Agent jobs

Reads recent SQL Agent job failures and long-running steps from msdb job history.

SELECT TOP (100)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS duration_seconds,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -14, GETDATE()), 112))
  AND (h.run_status <> 1 OR h.run_duration >= 3000)
ORDER BY run_start_time DESC, duration_seconds DESC;

run_duration uses HHMMSS format before conversion. Compare run_start_time with the complaint window. A long job is not automatically bad; the overlap is what matters.

What SQL Server slow performance review output should show

Useful review output should make the complaint easier to explain and easier to act on. It should connect the user-facing slowdown to SQL Server details that can be checked again after the next change.

Output	What it should say
Symptom	What users or jobs experienced, when it happened, and what was not affected.
Primary class	Waits, blocking, plan regression, indexing pressure, tempdb pressure, job overlap, or platform pressure.
Supporting details	Wait sample, blocking chain, query plan, Query Store data, job history, tempdb allocation, or monitoring data.
Business timing	The business path, report, batch, API, or job window where the slowdown matters.
Next action	The smallest change worth making first, plus what should be checked afterward.
Owner	The person or group that controls the next action: application, DBA, reporting, operations, vendor, or platform.

What to fix first

The first fix should follow the strongest checked pattern, not the loudest opinion. Separate immediate relief from structural cleanup, and avoid changes that make the next comparison harder.

Finding	What to fix first	Why
Production is actively blocked	Find the head blocker and transaction shape first.	Do not start broad tuning while sessions are waiting on one chain.
Waits are broad but no blocker exists	Classify the top wait families and compare them to workload timing.	Different waits lead to different work; avoid treating all waits as bad.
One query dominates reads or CPU	Review the query plan, indexes, parameters, and Query Store history.	Fix the path that shapes the workload before changing server-wide settings.
Tempdb is hot during the slowdown	Check task/session allocation, spills, version store, file growth, and overlapping work.	Tempdb may be the pressure point, not the root cause.
Jobs overlap user workload	Move, split, throttle, or review the job before tuning unrelated queries.	Bad timing can look like a SQL Server tuning problem.
Monitoring data is too thin	Add a short capture plan for the next recurrence.	Changing too much from weak data makes the next incident harder to compare.

Performance review

Need a SQL Server performance review?

Get in touch for a SQL Server performance review when waits, blocking, plans, tempdb, or workload timing keep pointing in different directions.

Request performance review See service

Next step

Use the SQL Server waits guide when the wait sample is the best next clue.

Use the SQL Server blocking guide when active sessions are waiting on another session.

Use the SQL Server indexing guide, tempdb guide, or monitoring guide when those areas match the checked pattern.