Mssql Dba

---
name: mssql-dba
description: SQL Server is slow, blocked, or deadlocking; or indexes and partitioning need a review — `quality:perf`, `quality:diagnose`, `quality:debug`
---

# 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