# Sqlserver Diagnostics

> Identify SQL Server performance bottlenecks using DMV queries. Covers wait stats, slow queries, active requests, performance counters, and connection analysis.

- Skill: `dvcrn/sqlserver-diagnostics` (Agent Skill)
- Install (CLI): `npx skillmds@latest add dvcrn/sqlserver-diagnostics`
- Raw SKILL.md: https://api.skillmd.com/api/skills/dvcrn/sqlserver-diagnostics/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: dvcrn (https://skillmd.com/u/dvcrn)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/dvcrn/sqlserver-diagnostics

---


# SQL Server Diagnostics

Use this skill when a SQL Server instance is slow, queries are timing out, or you need to identify the source of a performance problem. Start here before diving into indexes or execution plans.

---

## Recommended Workflow

```
1. Check wait stats          → scripts/wait-stats.sql
2. Find top slow queries     → scripts/top-slow-queries.sql
3. See what's running NOW    → scripts/active-queries.sql
4. Drill into a specific query → sqlserver-execution-plans/SKILL.md
5. Act on findings           → sqlserver-indexes or sqlserver-query-optimization
```

---

## 1. Wait Stats Analysis

Wait stats are the single best indicator of what SQL Server is struggling with. Every time SQL Server can't proceed, it records a wait type. High cumulative waits tell you the category of problem.

**Script:** `../scripts/wait-stats.sql`

```sql
-- Quick inline version (run from any database)
SELECT TOP 20
    wait_type,
    wait_time_ms / 1000.0                          AS wait_time_s,
    (wait_time_ms - signal_wait_time_ms) / 1000.0  AS resource_wait_s,
    signal_wait_time_ms / 1000.0                   AS signal_wait_s,
    waiting_tasks_count,
    CAST(100.0 * wait_time_ms /
         SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct_of_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'CLR_MANUAL_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE','LAZYWRITER_SLEEP',
    'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',
    'RESOURCE_QUEUE','SERVER_IDLE_CHECK','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP',
    'SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED',
    'SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP',
    'SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH',
    'WAITFOR','XE_DISPATCHER_WAIT','XE_TIMER_EVENT','DBMIRROR_EVENTS_QUEUE',
    'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    'BROKER_EVENTHANDLER','CHECKPOINT_QUEUE','DBMIRROR_WORKER_QUEUE',
    'SQLTRACE_WAIT_ENTRIES','WAIT_XTP_ONLINE_CKPT_NEW_LOG','ASYNC_NETWORK_IO'
)
ORDER BY wait_time_ms DESC;
```

### Wait Type Interpretation Guide

| Wait Type | Meaning | Typical Fix |
|-----------|---------|-------------|
| `PAGEIOLATCH_SH` / `PAGEIOLATCH_EX` | Reading/writing data pages from disk (I/O bound) | Add RAM, faster storage, better indexes to reduce I/O |
| `LCK_M_*` | Lock waits (blocking) | Find and fix blocking queries; review transaction isolation |
| `CXPACKET` / `CXCONSUMER` | Parallelism — threads waiting on each other | Review MAXDOP setting; fix skewed parallel queries |
| `SOS_SCHEDULER_YIELD` | CPU pressure — threads giving up CPU time | Query tuning, reduce CPU-intensive operations |
| `WRITELOG` | Log writes — transaction log is a bottleneck | Move log file to faster disk; reduce transaction size |
| `RESOURCE_SEMAPHORE` | Memory grants waiting — queries need more memory | Fix memory-spilling queries; increase `max server memory` |
| `ASYNC_NETWORK_IO` | Results sent to client faster than client reads | Reduce result set sizes; check client application |
| `LATCH_EX` / `LATCH_SH` | Internal memory structure contention | Usually TempDB contention — add TempDB files |
| `PAGELATCH_*` | In-memory page contention (TempDB allocation) | Add TempDB data files (8 files is common recommendation) |
| `HADR_*` | Always On Availability Groups activity | Normal in AG environments; high values = AG lag |

**Rule of thumb:** If one wait type dominates (>30% of total), that's your problem category.

---

## 2. Top Slow Queries

Identify the queries consuming the most elapsed time, CPU, or I/O since SQL Server last restarted.

**Script:** `../scripts/top-slow-queries.sql`

```sql
-- Quick inline version
SELECT TOP 25
    qs.total_elapsed_time / qs.execution_count / 1000.0   AS avg_elapsed_ms,
    qs.total_elapsed_time / 1000.0                         AS total_elapsed_ms,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count            AS avg_logical_reads,
    qs.total_worker_time / qs.execution_count / 1000.0     AS avg_cpu_ms,
    qs.total_logical_reads                                 AS total_logical_reads,
    DB_NAME(qt.dbid)                                       AS database_name,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2)+1)           AS query_text,
    qs.query_hash
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_ms DESC;
```

**Key columns:**
- `avg_elapsed_ms` — wall-clock time per execution (most useful for user-facing queries)
- `avg_logical_reads` — data pages read from buffer pool (index problems show up here)
- `avg_cpu_ms` — CPU time per execution (CPU-bound queries)
- `execution_count` — high count + high avg = highest total impact

---

## 3. Currently Running Queries

See what's actively executing right now, including wait info and blocking.

**Script:** `../scripts/active-queries.sql`

```sql
-- Quick inline version
SELECT
    r.session_id,
    s.login_name,
    r.status,
    r.wait_type,
    r.wait_time / 1000.0        AS wait_time_s,
    r.cpu_time / 1000.0         AS cpu_time_s,
    r.logical_reads,
    r.total_elapsed_time / 1000.0 AS elapsed_time_s,
    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 current_statement,
    s.host_name,
    s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id != @@SPID
  AND s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;
```

**What to look for:**
- `blocking_session_id > 0` → that session is being blocked (see monitoring/SKILL.md)
- `wait_type = 'PAGEIOLATCH_*'` → query doing heavy I/O right now
- `status = 'suspended'` + long `wait_time_s` → stuck on a resource

---

## 4. Performance Counters

Key performance counters via DMV (no need for PerfMon):

```sql
SELECT
    counter_name,
    instance_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Page life expectancy',        -- Buffer pool health (want > 300s, ideally > 1000s)
    'Batch Requests/sec',          -- Server load
    'SQL Compilations/sec',        -- High = lots of ad-hoc queries or plan cache pressure
    'SQL Re-Compilations/sec',     -- High = unstable plans, check sp_recompile usage
    'Page reads/sec',              -- High = I/O pressure
    'Page writes/sec',
    'Lazy writes/sec',             -- High = buffer pool too small
    'Memory Grants Pending',       -- > 0 regularly = memory pressure for query execution
    'User Connections'
)
  AND instance_name IN ('', 'SQLServer:Buffer Manager')
ORDER BY counter_name;
```

**Key thresholds:**
- **Page Life Expectancy (PLE)** < 300s: buffer pool is too small — data is cycling out before reuse
- **Memory Grants Pending** > 0: queries waiting for memory grants — potential sort/hash spills
- **Lazy Writes/sec** > 20: SQL Server is flushing dirty pages too aggressively — add RAM
- **SQL Re-Compilations/sec** high: check for `WITH RECOMPILE` stored procs or temp table schema changes

---

## 5. Connection Counts

```sql
-- Active connections by database and login
SELECT
    DB_NAME(dbid)   AS database_name,
    loginame,
    COUNT(*)        AS connection_count
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
ORDER BY connection_count DESC;

-- Total connection count
SELECT COUNT(*) AS total_connections
FROM sys.dm_exec_connections;
```

---

## 6. Memory Usage

```sql
-- Buffer pool usage by database
SELECT
    DB_NAME(database_id)     AS database_name,
    COUNT(*) * 8 / 1024.0    AS buffer_pool_mb
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4  -- exclude system databases
GROUP BY database_id
ORDER BY buffer_pool_mb DESC;

-- Total SQL Server memory usage
SELECT
    physical_memory_in_use_kb / 1024    AS memory_used_mb,
    page_fault_count
FROM sys.dm_os_process_memory;
```

---

## Diagnostic Checklist

When a performance complaint comes in:

- [ ] Run `wait-stats.sql` — what's the dominant wait type?
- [ ] Run `top-slow-queries.sql` — any obvious offenders (high avg_elapsed_ms)?
- [ ] Run `active-queries.sql` — any current blocking or long-running queries?
- [ ] Check PLE via performance counters — is the buffer pool healthy?
- [ ] Check `Memory Grants Pending` — are queries spilling to disk?
- [ ] If blocking found → go to `sqlserver-monitoring/SKILL.md`
- [ ] If slow query found → go to `sqlserver-execution-plans/SKILL.md`
- [ ] If missing indexes suggested → go to `sqlserver-indexes/SKILL.md`