sqlserver-diagnostics

name	sqlserver-diagnostics
description	Identify SQL Server performance bottlenecks using DMV queries. Covers wait stats, slow queries, active requests, performance counters, and connection analysis.

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

-- 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 
         (wait_time_ms)  ()  (,))  pct_of_total
 sys.dm_os_wait_stats
 wait_type   (
    ,,,,
    ,,,
    ,,,
    ,,,
    ,,,,
    ,,,
    ,,,
    ,,,
    ,,,,
    ,,
    ,,,
    ,,
)
  wait_time_ms ;