DBA Field Guide.

When to use it

Investigation sequence

1. Confirm the symptomCompare host CPU, SQL Server process CPU, and the timing of the slowdown. Capture a baseline before interpreting a single spike.
2. Identify current workInspect active requests and sessions; note CPU time, elapsed time, waits, database, and query text where permitted.
3. Look at historyUse Query Store (if enabled) and monitoring history to identify regressions, plan changes, or unusual execution counts.
4. Test competing explanationsConsider parallelism, compile pressure, workload changes, maintenance jobs, and non-SQL processes.
5. Validate safelyCompare before/after metrics under similar load. Do not clear caches or change server-wide settings as a first response.

Read-only starting query

SELECT TOP (20)
    r.session_id, DB_NAME(r.database_id) AS database_name,
    r.status, r.cpu_time AS cpu_ms, r.total_elapsed_time AS elapsed_ms,
    r.wait_type, r.blocking_session_id,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
      ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
        ELSE r.statement_end_offset END-r.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

How to interpret it

Current-request CPU is cumulative for each request, not a server-wide CPU percentage. A query can be expensive historically while absent from this snapshot.

Access requirements

Requires applicable DMV visibility permissions; on SQL Server 2022 and 2025 this commonly includes VIEW SERVER PERFORMANCE STATE. Exact permissions vary by version and scope.

Escalation checkpoint

When to use it

Investigation sequence

1. Establish the chainCapture blocked and blocking session IDs repeatedly; transient blocking is normal.
2. Inspect the head blockerIdentify the session at the root of the chain, its last request, open transaction state, and application owner.
3. Assess impactRecord affected applications, duration, lock types, and whether the blocker is doing useful work.
4. Choose a controlled responseCoordinate with the workload owner before canceling or terminating anything. Understand rollback costs.
5. Prevent recurrenceReview transaction scope, indexing, isolation-level tradeoffs, and application retry behavior.

Read-only starting query

SELECT r.session_id, r.blocking_session_id, r.status,
       r.wait_type, r.wait_time AS wait_ms,
       r.total_elapsed_time AS elapsed_ms,
       DB_NAME(r.database_id) AS database_name,
       t.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
   OR r.session_id IN (
       SELECT blocking_session_id FROM sys.dm_exec_requests
       WHERE blocking_session_id > 0)
ORDER BY r.blocking_session_id, r.session_id;

How to interpret it

A blocking session may be sleeping and absent from sys.dm_exec_requests. This query is a starting snapshot, not a complete lock-chain reconstruction.

Access requirements

Requires applicable DMV visibility permissions; SQL Server 2022+ may require VIEW SERVER PERFORMANCE STATE.

Escalation checkpoint

When to use it

Investigation sequence

1. Define the comparisonCapture the query, parameters, runtime window, and expected duration.
2. Check Query StoreIf enabled, compare execution statistics and plans across good and bad intervals.
3. Inspect the actual plan safelyWhen practical, review estimates versus actual rows, spills, memory grants, scans, and lookups.
4. Check contextInvestigate parameter sensitivity, statistics, indexes, schema changes, and concurrency.
5. Propose measurable fixesPrioritize low-risk changes, test representative parameters, and prepare rollback criteria.

Read-only starting query

SELECT TOP (20)
       qs.query_id, qt.query_sql_text,
       SUM(rs.count_executions) AS executions,
       CAST(SUM(rs.avg_duration * rs.count_executions)
            / NULLIF(SUM(rs.count_executions),0) / 1000.0 AS decimal(18,1))
            AS weighted_avg_duration_ms
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p ON p.plan_id=rs.plan_id
JOIN sys.query_store_query AS qs ON qs.query_id=p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id=qs.query_text_id
GROUP BY qs.query_id, qt.query_sql_text
ORDER BY weighted_avg_duration_ms DESC;

How to interpret it

Query Store duration values are in microseconds. This is an aggregate over retained data, not a time-window comparison; run in the relevant user database with Query Store enabled.

Access requirements

Requires Query Store to be enabled and appropriate database permissions (often VIEW DATABASE STATE or VIEW DATABASE PERFORMANCE STATE, depending on version).

Escalation checkpoint

When to use it

Investigation sequence

1. Check immediate riskRecord free disk space, log size, used percentage, and autogrowth settings.
2. Identify reuse constraintsCheck log_reuse_wait_desc and recovery model; investigate long transactions, backup cadence, and availability features.
3. Confirm the trendCompare log usage over time, not only a single sample.
4. Choose the correct remedyAddress the cause, coordinate log backups when appropriate, and plan capacity. Avoid switching recovery model or shrinking as a reflex.
5. Verify recovery readinessEnsure backup chain, restore objectives, and availability requirements remain intact.

Read-only starting query

SELECT d.name, d.recovery_model_desc, d.log_reuse_wait_desc,
       mf.name AS logical_file_name,
       CAST(mf.size * 8.0 / 1024 AS decimal(18,1)) AS size_mb,
       mf.growth, mf.is_percent_growth
FROM sys.databases AS d
JOIN sys.master_files AS mf ON mf.database_id=d.database_id
WHERE mf.type_desc='LOG'
ORDER BY d.name;

How to interpret it

log_reuse_wait_desc is a diagnostic clue, not a complete root-cause explanation. The growth column uses 8-KB pages unless is_percent_growth = 1.

Access requirements

Catalog visibility and permissions vary by environment; run with an account authorized to inspect instance metadata.

Escalation checkpoint

When to use it

Investigation sequence

1. Define the objectiveRecord database criticality, recovery model, RPO, RTO, and retention requirements.
2. Review backup historyCheck last full, differential, and log backups, including status and timing.
3. Investigate failuresReview job history, storage access, capacity, credentials, network destinations, and encryption requirements.
4. Verify recoverabilityConfirm the backup chain and perform a controlled test restore in an isolated environment.
5. Document evidenceRecord restore duration, consistency checks, gaps, owners, and next drill date.

Read-only starting query

SELECT d.name AS database_name,
       MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type='I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type='L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name=d.name
GROUP BY d.name
ORDER BY d.name;

How to interpret it

msdb history may be purged, may not reflect backups taken elsewhere, and does not prove a backup can be restored. A recent backup is not equivalent to a successful restore test.

Access requirements

Requires access to msdb backup history and sys.databases; permissions depend on environment.

Escalation checkpoint