MSSQL DBA Skill

---
name: MSSQL DBA Skill
version: 1.0
description: >
  Diagnose and fix a running SQL Server the way an experienced DBA does: find
  the head blocker and what it is holding, read deadlock graphs, rank waits,
  find the expensive and plan-unstable queries in Query Store, and review
  indexes (missing, unused, redundant, fragmented, stale statistics, forwarded
  heaps) and partitioning (alignment, boundaries, sliding window). Every query
  here was run against SQL Server 2022 on a lab that reproduced the problem.
applies_to: [mssql, sql-server, azure-sql, t-sql, dba, performance]
tags: [dba, blocking, deadlock, wait-stats, query-store, indexing, partitioning, statistics]
---

# MSSQL DBA Skill — v1.0

## Quick Card

> Read this card first. Load a section below only when the task needs it.

| | |
|---|---|
| **Use when** | SQL Server is slow, blocked, or deadlocking; or indexes and partitioning need a review — `quality:perf`, `quality:diagnose`, `quality:debug` |
| **Skip when** | Writing new T-SQL or procedures — `mssql_advanced_skill`. Designing a new schema — `database_skill` |
| **Inputs** | The symptom and when it started; `VIEW SERVER STATE` (server) or `VIEW DATABASE STATE` (Azure SQL DB); the database name |
| **Produces** | Diagnosis backed by DMV output, a fix script, its rollback script, and a before/after measurement |
| **Steps** | 1. Triage what runs now (§1) → 2. Blocking (§2) or deadlocks (§3) → 3. Waits (§4) → 4. Top queries (§5) → 5. Indexes (§6) → 6. Partitioning (§7) → 7. Fix with rollback, measure, record (§9) |
| **Done when** | Root cause named with evidence; fix reversible and applied in a window; the metric that was bad is measurably better |
| **Senior defaults** | Fix the head blocker, never the victims · RCSI before `NOLOCK` · missing-index DMV output is a hint — merge it, reorder it, never create it verbatim · usage stats reset on restart — check `sqlserver_start_time` before calling anything unused · partitioning buys data lifecycle, not speed · statistics before rebuilds; page fullness matters more than fragmentation % · disable before drop |
| **Load on demand** | §0 ground rules · §1 triage · §2 blocking · §3 deadlocks · §4 waits · §5 Query Store · §6 indexes · §7 partitioning · §8 config · §9 fix protocol |
| **Run report** | `html_report_skill` — adds: Evidence (query → finding) · Fix and rollback scripts · Before/after metric |
| **Pairs with** | `mssql_advanced_skill`, `database_skill`, `adr_skill` (index and partition strategy are `Database` / `Performance` decisions) |

---

## 0. Ground Rules

1. **Read before you touch.** Every query in §1–§7 is read-only. Capture its output before changing anything — it is the "before" in your before/after.
2. **Every change ships with its rollback** and a named metric that should improve.
3. **Never `KILL` blind.** Killing a session rolls back its transaction; a long transaction rolls back for as long as it ran forward. Look at §2's output first.
4. **Never run `DBCC FREEPROCCACHE`, `DBCC DROPCLEANBUFFERS`, or a server restart in production to "fix" performance.** They destroy the evidence and cause a compile storm.
5. **Run scripts with `QUOTED_IDENTIFIER ON`.** SSMS sets it; `sqlcmd` does not (use `sqlcmd -I`). The XML queries in §3 fail with error 1934 without it.
6. Versions: everything runs on SQL Server 2017+. `STRING_AGG` (§6.3) needs 2017; Query Store (§5) needs 2016; parameter-sensitive plan optimisation needs 2022 at compatibility level 160.

## 1. Triage — What Is Running Right Now

```sql
SELECT r.session_id, r.status, r.blocking_session_id, r.wait_type, r.wait_time AS wait_ms,
       r.cpu_time AS cpu_ms, r.total_elapsed_time AS elapsed_ms, r.logical_reads,
       DB_NAME(r.database_id) AS db, s.login_name, s.host_name, s.program_name,
       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 running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID AND s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;
```

Read it as: non-zero `blocking_session_id` → §2. `LCK_M_*` waits → §2. `PAGEIOLATCH_*` → reading from disk; §5 and §6.1. `CXPACKET`/`CXCONSUMER` on one huge query → §5. `RESOURCE_SEMAPHORE` → memory grants; §5 for the query asking.

## 2. Blocking — Find the Head

Most blocking is one session at the root of a chain. The victims are symptoms.

```sql
-- Head blockers: the root of each chain, what it last ran, and how long it has sat idle
WITH r AS (
    SELECT session_id, blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0),
chain AS (
    SELECT session_id, blocking_session_id AS head, 1 AS depth FROM r
    UNION ALL
    SELECT c.session_id, r.blocking_session_id, c.depth + 1
    FROM chain AS c JOIN r ON r.session_id = c.head
    WHERE c.depth < 50),
heads AS (
    SELECT head, COUNT(DISTINCT session_id) AS sessions_blocked, MAX(depth) AS chain_depth
    FROM chain
    WHERE head NOT IN (SELECT session_id FROM r)
    GROUP BY head)
SELECT h.head AS head_blocker, h.sessions_blocked, h.chain_depth,
       s.status, s.open_transaction_count,
       DATEDIFF(second, s.last_request_end_time, SYSDATETIME()) AS idle_s,
       s.login_name, s.host_name, s.program_name,
       LEFT(t.text, 200) AS last_sql
FROM heads AS h
JOIN sys.dm_exec_sessions AS s ON s.session_id = h.head
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = h.head
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
ORDER BY h.sessions_blocked DESC;

-- What each blocked session is waiting for
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time AS wait_ms, r.wait_resource,
       DB_NAME(r.database_id) AS db,
       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 waiting_statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;
```

```sql
-- Locks held and requested, in the blocked database
SELECT l.request_session_id AS session_id, l.resource_type, l.request_mode, l.request_status,
       OBJECT_NAME(COALESCE(p.object_id, l.resource_associated_entity_id)) AS object_name,
       l.resource_description
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p ON p.hobt_id = l.resource_associated_entity_id
WHERE l.resource_database_id = DB_ID() AND l.resource_type <> 'DATABASE'
ORDER BY l.request_session_id, l.resource_type;
```

Verified on the lab: an idle session holding `BEGIN TRAN; UPDATE …` shows as `head_blocker` with `status = sleeping`, `open_transaction_count = 1`, the `UPDATE` in `last_sql`; the victim waits on `LCK_M_X` for the same `KEY`.

| Head blocker looks like | Cause | Fix |
|---|---|---|
| `sleeping`, `open_transaction_count > 0`, `idle_s` growing | App opened a transaction and never committed — often an exception path that skipped rollback | App: `SET XACT_ABORT ON`, `try/finally` rollback, no user interaction inside a transaction. Now: confirm with the owner, then `KILL` |
| `running`, long `elapsed_ms`, huge `logical_reads` | Long transaction scanning because an index is missing | §5 → §6.1; shorten the transaction; batch large updates (e.g. 5,000 rows per transaction) |
| Readers blocked by writers (`LCK_M_S`) | Read committed with locking | `ALTER DATABASE … SET READ_COMMITTED_SNAPSHOT ON` — readers stop blocking; costs tempdb version store. Needs a moment with no other connections, or `WITH ROLLBACK IMMEDIATE` in a window |
| `LCK_M_RS_*`, `LCK_M_RangeS_*` waits | Serializable isolation — .NET `TransactionScope` defaults to it | Set `IsolationLevel.ReadCommitted` explicitly |
| Object-level `X` lock on a big table | Lock escalation past ~5,000 locks | Smaller batches; index the predicate so fewer rows are locked |

`NOLOCK` is not a blocking fix: it reads uncommitted and can double-count or skip rows during page splits. `mssql_advanced_skill` §2 covers when it is acceptable.

## 3. Deadlocks — Read the Graph

Every deadlock is already captured by the built-in `system_health` session.

```sql
SET QUOTED_IDENTIFIER ON;  -- XML methods require it; sqlcmd defaults to OFF
SELECT TOP (10)
       x.ev.value('@timestamp', 'datetime2(0)') AS utc_time,
       x.ev.value('(data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(50)') AS victim_process,
       x.ev.query('data/value/deadlock') AS deadlock_graph
FROM (SELECT CAST(event_data AS xml) AS e
      FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
      WHERE object_name = 'xml_deadlock_report') AS f
CROSS APPLY f.e.nodes('event') AS x(ev)
ORDER BY utc_time DESC;
```

The file target flushes asynchronously — a deadlock from the last few seconds may not appear yet. Open `deadlock_graph` in SSMS (save as `.xdl`) to see it drawn.

In the graph: `process-list` has each session's statement (`inputbuf`) and isolation level; `resource-list` shows which lock each one **owns** and which it **waits** for. The cycle is the fix:

| Pattern | Fix |
|---|---|
| Two transactions update the same rows in opposite order | Access rows and tables in one consistent order |
| A reader's key lookup meets a writer on the clustered index | Cover the query with `INCLUDE` columns so it never touches the clustered index |
| Both scan to find rows to update | Index the `WHERE` so each locks only its own rows |
| Rare and unavoidable | Retry on error 1205 in the application, with backoff — the victim is safe to retry |

## 4. Wait Statistics — Where Time Goes

```sql
WITH w AS (
    SELECT wait_type, wait_time_ms / 1000.0 AS wait_s, signal_wait_time_ms / 1000.0 AS signal_s, waiting_tasks_count
    FROM sys.dm_os_wait_stats
    WHERE waiting_tasks_count > 0
      AND wait_type NOT LIKE N'%SLEEP%'
      AND wait_type NOT LIKE N'%IDLE%'
      AND wait_type NOT LIKE N'%QUEUE%'
      AND wait_type NOT LIKE N'XE%'
      AND wait_type NOT LIKE N'BROKER%'
      AND wait_type NOT LIKE N'PREEMPTIVE_XE%'
      AND wait_type NOT LIKE N'QDS%'
      AND wait_type NOT LIKE N'PARALLEL_REDO%'
      AND wait_type NOT IN (N'CHECKPOINT_QUEUE', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'DIRTY_PAGE_POLL',
            N'DISPATCHER_QUEUE_SEMAPHORE', N'FT_IFTSHC_MUTEX', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
            N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_EXTENSIBILITY_CLEANUP_TASK',
            N'REQUEST_FOR_DEADLOCK_SEARCH', N'SOS_WORK_DISPATCHER', N'SP_SERVER_DIAGNOSTICS_SLEEP',
            N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'WAITFOR', N'WAIT_XTP_CKPT_CLOSE',
            N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'VDI_CLIENT_OTHER', N'MEMORY_ALLOCATION_EXT',
            N'PVS_PREALLOCATE', N'SOS_SCHEDULER_YIELD_IDLE', N'WAIT_ON_SYNC_STATISTICS_REFRESH',
            N'STARTUP_DEPENDENCY_MANAGER', N'AZURE_IMDS_VERSIONS', N'CHKPT'))
SELECT TOP (12) wait_type,
       CAST(wait_s AS decimal(14, 1)) AS wait_s,
       CAST(100.0 * wait_s / SUM(wait_s) OVER () AS decimal(5, 1)) AS pct,
       CAST(signal_s AS decimal(14, 1)) AS signal_s,
       CAST(1000.0 * wait_s / waiting_tasks_count AS decimal(14, 2)) AS avg_wait_ms
FROM w
ORDER BY wait_s DESC;
```

Totals are cumulative since restart. To see *now*, snapshot twice a few minutes apart and diff. Azure SQL Database: use `sys.dm_db_wait_stats`.

| Top wait | Means | Look at |
|---|---|---|
| `LCK_M_*` | Blocking | §2 |
| `PAGEIOLATCH_SH/EX` | Reading pages from disk — scans or too little memory | §5 top reads, §6.1 missing indexes |
| `WRITELOG` | Waiting on log flush — many tiny commits, or slow log disk | Batch commits; log on fast storage |
| `CXPACKET` / `CXCONSUMER` | Parallelism — often one big scan | §5; cost threshold for parallelism (§8) |
| `SOS_SCHEDULER_YIELD`, high `signal_s` | CPU pressure | §5 top CPU |
| `RESOURCE_SEMAPHORE` | Queries waiting for memory grants | §5 — oversized sorts and hashes; stale statistics (§6.5) |
| `PAGELATCH_*` on tempdb pages | tempdb allocation contention | §8 tempdb files |
| `ASYNC_NETWORK_IO` | The client is slow to consume results | Application fetching row by row or too many rows |

## 5. Expensive and Unstable Queries — Query Store

Turn it on if it is off (`ALTER DATABASE … SET QUERY_STORE = ON`); from SQL Server 2022 it is on for new databases.

```sql
-- Top queries by CPU, last 24 hours (change ORDER BY for duration or reads)
SELECT TOP (10) q.query_id, p.plan_id,
       SUM(rs.count_executions) AS execs,
       CAST(SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS decimal(18, 1)) AS total_cpu_ms,
       CAST(SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS decimal(18, 1)) AS total_duration_ms,
       CAST(SUM(rs.avg_logical_io_reads * rs.count_executions) AS bigint) AS total_logical_reads,
       LEFT(qt.query_sql_text, 90) AS query_text
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS iv ON iv.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE iv.start_time >= DATEADD(hour, -24, SYSDATETIMEOFFSET())
GROUP BY q.query_id, p.plan_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

-- Queries with more than one plan, worst spread first — parameter-sensitivity candidates
SELECT q.query_id, COUNT(DISTINCT p.plan_id) AS plans,
       CAST(MIN(rs.avg_duration) / 1000.0 AS decimal(18, 2)) AS best_avg_ms,
       CAST(MAX(rs.avg_duration) / 1000.0 AS decimal(18, 2)) AS worst_avg_ms,
       MAX(CAST(p.is_forced_plan AS int)) AS has_forced_plan,
       LEFT(MAX(qt.query_sql_text), 90) AS query_text
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
GROUP BY q.query_id
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY MAX(rs.avg_duration) / NULLIF(MIN(rs.avg_duration), 0) DESC;
```

**Parameter sniffing** — one query, a fast plan and a slow plan, chosen by whichever parameter compiled first. Options, least invasive first:

1. **Force the good plan** in Query Store: `EXEC sys.sp_query_store_force_plan @query_id = …, @plan_id = …;` — reversible with `sp_query_store_unforce_plan`.
2. **Compatibility level 160** on SQL Server 2022 enables parameter-sensitive plan optimisation (several cached plans per query).
3. `OPTION (OPTIMIZE FOR (@p UNKNOWN))` for a stable average plan, or `OPTION (RECOMPILE)` for rare, expensive, highly skewed queries — it compiles every execution.

## 6. Indexes

### 6.1 Missing indexes — hints, not orders

```sql
SELECT TOP (20)
       CAST(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS decimal(18, 0)) AS improvement,
       OBJECT_NAME(d.object_id, d.database_id) AS table_name,
       d.equality_columns, d.inequality_columns, d.included_columns,
       s.user_seeks, s.user_scans, s.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS s
JOIN sys.dm_db_missing_index_groups AS g ON g.index_group_handle = s.group_handle
JOIN sys.dm_db_missing_index_details AS d ON d.index_handle = g.index_handle
WHERE d.database_id = DB_ID()
ORDER BY improvement DESC;
```

Turn a hint into an index:

1. **Key order is yours to decide.** The DMV lists columns in table order, not selectivity order. On the lab it suggested `(OrderDate, Amount)` for `WHERE Amount BETWEEN 500 AND 510 AND OrderDate >= …`; the narrow `Amount` range should lead.
2. **Equality columns first, then at most one range column; the rest go in `INCLUDE`.**
3. **Merge** suggestions for the same table into as few indexes as possible, and check existing indexes first — widening one beats adding another.
4. Every index slows every write to its table. Past ~5–7 nonclustered indexes on an OLTP table, justify each one.

### 6.2 Unused and write-heavy indexes

```sql
SELECT OBJECT_SCHEMA_NAME(i.object_id) + N'.' + OBJECT_NAME(i.object_id) AS table_name,
       i.name AS index_name,
       ISNULL(u.user_seeks + u.user_scans + u.user_lookups, 0) AS reads,
       ISNULL(u.user_updates, 0) AS writes,
       (SELECT SUM(ps.used_page_count) * 8 / 1024 FROM sys.dm_db_partition_stats AS ps
         WHERE ps.object_id = i.object_id AND ps.index_id = i.index_id) AS size_mb,
       (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS counting_since
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS u
       ON u.database_id = DB_ID() AND u.object_id = i.object_id AND u.index_id = i.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.index_id > 1 AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0
  AND (ISNULL(u.user_seeks + u.user_scans + u.user_lookups, 0) = 0
       OR ISNULL(u.user_updates, 0) > 10 * ISNULL(u.user_seeks + u.user_scans + u.user_lookups, 0))
ORDER BY writes DESC;
```

- **Counters reset on restart** (and on database detach/offline, and index rebuild on older versions). `counting_since` a week ago is not evidence for a month-end report index.
- **An index can look "read" only by the statement maintaining it.** On the lab, `IX_Orders_Status` showed one scan — by the `UPDATE` that also wrote it.
- **Disable, then drop later.** `ALTER INDEX … DISABLE` keeps the definition; rollback is `ALTER INDEX … REBUILD`. Script the `CREATE INDEX` before dropping.

### 6.3 Duplicate and left-prefix redundant indexes

```sql
WITH k AS (
    SELECT i.object_id, i.index_id, i.name, i.is_unique,
           STRING_AGG(CAST(c.name AS nvarchar(max)) + CASE ic.is_descending_key WHEN 1 THEN N' DESC' ELSE N'' END, N', ')
               WITHIN GROUP (ORDER BY ic.key_ordinal) AS keys
    FROM sys.indexes AS i
    JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal > 0
    JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.index_id > 1
    GROUP BY i.object_id, i.index_id, i.name, i.is_unique)
SELECT OBJECT_NAME(a.object_id) AS table_name,
       a.name AS redundant_index, a.keys AS redundant_keys,
       b.name AS covered_by, b.keys AS covering_keys
FROM k AS a
JOIN k AS b ON b.object_id = a.object_id AND b.index_id <> a.index_id
WHERE a.is_unique = 0
  AND (   (b.keys = a.keys AND a.index_id > b.index_id)
       OR LEFT(b.keys, LEN(a.keys) + 2) = a.keys + N', ');
```

`(CustomerId)` is redundant next to `(CustomerId, OrderDate)`. Before dropping, check `INCLUDE` columns: the survivor must include everything the redundant one did, or queries that relied on it start doing key lookups.

### 6.4 Fragmentation, page fullness, forwarded records

```sql
SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name, ips.partition_number,
       ips.page_count,
       CAST(ips.avg_fragmentation_in_percent AS decimal(5, 1)) AS frag_pct,
       CAST(ips.avg_page_space_used_in_percent AS decimal(5, 1)) AS page_full_pct,
       ips.forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count >= 1000 AND ips.index_level = 0
ORDER BY ips.avg_fragmentation_in_percent DESC;
```

`SAMPLED` reads part of every index — run it off-peak on large databases, or use `'LIMITED'` (fragmentation only; page fullness comes back NULL).

- **Page fullness is the cost that matters.** On the lab, single-row inserts with a random `NEWID()` key left the clustered index 99% fragmented and **60% full** — 40% of every page read from disk and cached in memory was empty. Fragmentation % alone matters little on SSD.
- **Fix the cause, not only the symptom.** Random keys: use `NEWSEQUENTIALID()` or a sequential `bigint`, or a fill factor that leaves room. Rebuilding a random-key index restores it only until the next day of inserts.
- **Maintenance thresholds** for indexes ≥ 1,000 pages: `REORGANIZE` at 5–30%, `REBUILD` above 30% — `WITH (ONLINE = ON, RESUMABLE = ON)` on Enterprise and Azure SQL.
- **Heaps with `forwarded_record_count`** — the lab heap had 6,666 after rows grew — cost an extra read per forwarded row. Give the table a clustered index; `ALTER TABLE … REBUILD` removes forwards only until rows grow again.

### 6.5 Statistics

```sql
SELECT TOP (20) OBJECT_NAME(s.object_id) AS table_name, s.name AS stat_name,
       sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter,
       CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS decimal(7, 1)) AS pct_modified
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 AND sp.modification_counter > 0
ORDER BY pct_modified DESC;
```

Stale statistics cause bad row estimates, which cause bad plans, spills, and memory-grant waits. After a large load, `UPDATE STATISTICS dbo.T WITH FULLSCAN` on the columns queries filter by is often the whole fix — try it before any rebuild. `rows_sampled` far below `rows` on a skewed column is its own problem: sample more.

### 6.6 Clustered key review

Narrow, unique, static, ever-increasing. An `int`/`bigint` identity fits all four. Ever-increasing keys concentrate inserts on the last page; under heavy concurrent inserts (`PAGELATCH_EX` on one page) use `OPTIMIZE_FOR_SEQUENTIAL_KEY = ON` (SQL Server 2019+).

## 7. Partitioning

### 7.1 When it helps, and when it does not

| Partitioning helps | It does not |
|---|---|
| **Data lifecycle**: load or archive a month instantly with `SWITCH`, `TRUNCATE TABLE … WITH (PARTITIONS (n))` | Making queries faster. Only queries that filter on the partition column skip partitions; every other query gets slower, because each partition is searched separately |
| Maintenance per partition: rebuild only the current month | Tables under ~50 GB that have no archiving need |
| Different filegroups / storage tiers per age | Fixing a missing index |

If the goal is query speed, the answer is almost always an index (§6.1). Record the decision to partition as an ADR (`adr_skill`, type `Database`).

### 7.2 Designing it

- **Partition column**: the date the data ages by, which the big queries also filter on.
- **`RANGE RIGHT` for dates** — each boundary is the first day of its partition, which is how people think.
- **Unique indexes, including the primary key, must contain the partition column** — typically `(SaleDate, SaleId)`.
- **Keep an empty partition at both ends.** `SPLIT` and `MERGE` move no data only when the partitions involved are empty; on a partition with rows they move data under a schema lock and log every row.
- **Every index aligned**: built `ON` the partition scheme. One non-aligned index blocks `SWITCH` for the whole table.

```sql
CREATE PARTITION FUNCTION pf_month (date) AS RANGE RIGHT
    FOR VALUES ('2026-07-01', '2026-08-01', '2026-09-01', '2026-10-01');
CREATE PARTITION SCHEME ps_month AS PARTITION pf_month ALL TO ([PRIMARY]);

CREATE TABLE dbo.Sales (
    SaleId   bigint        NOT NULL,
    SaleDate date          NOT NULL,
    Amount   decimal(12,2) NOT NULL,
    CONSTRAINT PK_Sales PRIMARY KEY CLUSTERED (SaleDate, SaleId) ON ps_month (SaleDate));

CREATE INDEX IX_Sales_Amount ON dbo.Sales (Amount) ON ps_month (SaleDate);  -- aligned
```

### 7.3 Diagnosing an existing design

```sql
-- Inventory: boundaries and rows per partition
SELECT OBJECT_NAME(p.object_id) AS table_name, i.name AS index_name, pf.name AS function_name,
       CASE pf.boundary_value_on_right WHEN 1 THEN 'RANGE RIGHT' ELSE 'RANGE LEFT' END AS range_type,
       p.partition_number, lo.value AS lower_boundary, hi.value AS upper_boundary, p.rows
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS ps ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
LEFT JOIN sys.partition_range_values AS lo ON lo.function_id = pf.function_id AND lo.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS hi ON hi.function_id = pf.function_id AND hi.boundary_id = p.partition_number
WHERE i.index_id IN (0, 1)
ORDER BY table_name, p.partition_number;

-- Non-aligned indexes on partitioned tables
SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, ds.name AS stored_on, ds.type_desc
FROM sys.indexes AS i
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
WHERE i.index_id > 1
  AND ds.type <> 'PS'
  AND EXISTS (SELECT 1 FROM sys.indexes AS base
              JOIN sys.data_spaces AS bds ON bds.data_space_id = base.data_space_id
              WHERE base.object_id = i.object_id AND base.index_id IN (0, 1) AND bds.type = 'PS');

-- Rows in the last partition (RANGE RIGHT: must be 0 before the next SPLIT)
SELECT OBJECT_NAME(p.object_id) AS table_name, p.partition_number AS last_partition, p.rows AS rows_in_last
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id AND ds.type = 'PS'
WHERE i.index_id IN (0, 1)
  AND p.partition_number = (SELECT MAX(p2.partition_number) FROM sys.partitions AS p2
                            WHERE p2.object_id = p.object_id AND p2.index_id = p.index_id);
```

For `RANGE LEFT`, boundaries are inclusive upper bounds; read the two boundary columns accordingly.

| Finding | Consequence | Fix |
|---|---|---|
| Non-aligned index | `SWITCH` fails with error 7733 | `CREATE INDEX … WITH (DROP_EXISTING = ON) ON ps_month (SaleDate)` |
| Rows in the last partition | Next `SPLIT` moves data under a schema lock | Add boundaries ahead of the data — keep the last partition empty |
| One partition holds most rows | No lifecycle benefit | Boundaries do not match how data ages; redesign |
| Queries do not filter on the partition column | Every query searches every partition | Index for those queries; reconsider partitioning |

Check partition elimination in the actual plan: *Actual Partitions Accessed* on the seek or scan operator.

### 7.4 The sliding window

```sql
-- 1. Before the month starts: add next boundary while the last partition is still empty
ALTER PARTITION SCHEME ps_month NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_month() SPLIT RANGE ('2026-11-01');

-- 2. Archive the oldest month: target is empty, same columns and clustered key, same filegroup
ALTER TABLE dbo.Sales SWITCH PARTITION 2 TO dbo.Sales_Archive;

-- 3. Remove the now-empty boundary
ALTER PARTITION FUNCTION pf_month() MERGE RANGE ('2026-07-01');
```

Verified on the lab: after aligning the index, `SWITCH` moved 20,512 rows as a metadata change; the target needed no matching nonclustered index; a second `SWITCH` into the non-empty target failed with error 4905. `TRUNCATE TABLE dbo.Sales WITH (PARTITIONS (2))` deletes one partition when no archive copy is needed.

## 8. Configuration Quick Checks

| Setting | Healthy default | Check with |
|---|---|---|
| tempdb data files | Equal size, one per core up to 8 | `SELECT name, size FROM tempdb.sys.database_files` |
| `max degree of parallelism` | ≤ 8, cores per NUMA node | `sys.configurations` |
| `cost threshold for parallelism` | 30–50, not the default 5 | `sys.configurations` |
| `max server memory` | Set, leaving the OS headroom | `sys.configurations` |
| Query Store | On, `READ_WRITE` | `sys.database_query_store_options` |
| Auto create / update statistics | On | `sys.databases` |
| Compatibility level | Current, after testing | `sys.databases` |

## 9. Fix Protocol

1. **Evidence** — save the §1–§7 output that shows the problem.
2. **Hypothesis** — one cause, stated as a mechanism: "`UPDATE` scans `Orders` because `Status` is not indexed, holding locks for 4 s".
3. **Change script + rollback script**, reviewed together.
4. **Apply in a window**; online options where the edition allows.
5. **Measure the same metric** that was bad — wait time, duration, reads, blocked sessions.
6. **Record** — an ADR for index or partition strategy (`adr_skill`); the run report (`html_report_skill`) for the incident.

## 10. Checklist

✅ Evidence captured before any change
✅ Blocking traced to the head blocker; victims left alone
✅ Deadlock graph read; cycle identified; access order or index fixed
✅ Waits interpreted from a diff, not lifetime totals
✅ Missing-index hints reordered, merged, and checked against existing indexes
✅ "Unused" backed by counters covering a full business cycle
✅ Redundant indexes checked for `INCLUDE` coverage before dropping
✅ Page fullness and the key design addressed, not just fragmentation %
✅ Statistics updated before any rebuild was considered
✅ Partitioning justified by data lifecycle; every index aligned; empty edge partitions kept
✅ Every change has a rollback and a before/after measurement