The First Hour on a Server Nobody Can Explain

A slow SQL Server needs a clear investigation order when nobody can explain its history. First confirm the instance is reachable, then check blocking, waits, plans, and the resources underneath them.

Confirm That You Reached the Right Engine

Ask for the application symptom and its start time before opening a dozen windows. Find out whether connections fail or requests connect and then stall. Those are different incidents. Keep a short timeline of observations and changes as you work.

SELECT
    @@SERVERNAME AS server_name,
    SERVERPROPERTY('ProductVersion') AS version,
    SERVERPROPERTY('Edition') AS edition,
    SYSDATETIMEOFFSET() AS captured_at,
    sqlserver_start_time
FROM sys.dm_os_sys_info;

If the connection itself fails, inspect the Windows service and the exact connection error. Check the configured instance name and network path with the infrastructure owner. Don’t change SQL settings on another working instance because its name happens to look familiar.

A successful query proves the engine answered that request. It doesn’t prove the application can authenticate or reach its database. Test the application path separately when needed. Record whether your diagnostic account has wider permissions than the application’s normal account.

Look for Blocking Before Blaming Capacity

SELECT
    r.session_id, r.blocking_session_id,
    r.wait_type, r.wait_resource,
    s.login_name, s.program_name,
    s.open_transaction_count
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.blocking_session_id
WHERE r.blocking_session_id > 0;

Follow positive blocking session IDs to the head of the chain. A sleeping session can hold an open transaction and block active work. Ask which application owns it. The blocked request can be perfectly reasonable while another session prevents it from moving.

Don’t kill the first blocker without understanding its work and recovery cost. Rollback can take time and hold resources. Capture the evidence and coordinate with the incident owner. The aim is to restore service without creating a second unexplained failure.

See What Active Work Is Waiting For

SELECT
    session_id, status, command,
    wait_type, wait_time, last_wait_type,
    cpu_time, total_elapsed_time,
    logical_reads, reads, writes
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID
ORDER BY total_elapsed_time DESC;

This shows current requests rather than a history of everything that ran. Repeat a focused snapshot when the symptom changes. A long elapsed duration with little CPU suggests a different investigation from sustained CPU work. Read the wait meaning before naming a cause.

Parallel requests can have several tasks with different waits. The request row doesn’t expose every worker’s condition. Use task-level evidence when the first snapshot isn’t enough. Avoid treating one wait name as a diagnosis independent of the query and workload.

Use an approved diagnostic account with the documented permissions. SQL Server 2022 and later changed permissions for several performance views. An incomplete view of other sessions can make a busy server look quiet.

Find the Plan Worth Reviewing

SELECT TOP (10)
    qs.query_hash, qs.execution_count,
    qs.total_worker_time / 1000.0 AS total_cpu_ms,
    qs.total_logical_reads,
    t.text AS batch_text,
    qs.plan_handle
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.total_worker_time DESC;

Cached totals help identify repeated expensive work, but they aren’t limited to the current incident. Their history follows the cached plan’s lifetime. Compare with Query Store when it is available. Preserve query text carefully because it can contain sensitive literals.

Inspect the candidate’s plan for a question you can test, such as an unexpected scan or poor estimate. Don’t add an index solely because a graphical hint suggested one. Consider writes, existing indexes, and the actual application path before changing the schema.

Check Storage With Engine Context

SELECT
    DB_NAME(v.database_id) AS database_name,
    f.type_desc,
    v.num_of_reads, v.io_stall_read_ms,
    v.num_of_writes, v.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS v
JOIN sys.master_files AS f
    ON f.database_id = v.database_id
   AND f.file_id = v.file_id;

Take interval samples to distinguish current IO behavior from older activity. Compare data and log files separately. Bring the file-level evidence to the storage owner alongside host measurements. A lifetime average or a disk utilization screenshot isn’t enough to blame a device.

Check available disk space and recent growth events as a separate concern. An almost-full volume can threaten the next write even when current latency looks acceptable. Preserve error log messages that describe failed IO or allocation rather than paraphrasing them from memory.

Finish the Hour With a Testable Explanation

Summarize what you know, what remains uncertain, and which action follows from the evidence. Keep the affected application and time window visible. A clear partial explanation is more useful than five simultaneous changes followed by a quieter server.

Include the names of the saved captures and the commands that produced them. Someone joining the incident should be able to repeat a check without reconstructing your session. Mark collection gaps explicitly instead of filling them with assumptions.

Check recent deployments and maintenance against the timeline before testing a change. Establish a comparison and a way back. I want the next hour to begin with fewer unknowns and a clear account of which changes helped.

The first hour is not a race to change settings, it is a chance to turn symptoms into a testable explanation.

This post was rewritten from scratch in September 2026. The original, published on 2010-01-26, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.