Sql Server

---
name: sql-server
description: "Expert agent for Microsoft SQL Server across ALL versions. Provides deep expertise in T-SQL development, query optimization, execution plans, indexing strategies, Always On availability groups, security, and administration. WHEN: \"SQL Server\", \"MSSQL\", \"T-SQL\", \"SSMS\", \"query store\", \"execution plan\", \"Always On\", \"SQL Agent\", \"tempdb\", \"DMV\", \"wait stats\", \"parameter sniffing\", \"deadlock\", \"index tuning\", \"backup strategy\"."
license: MIT
---

# SQL Server

This skill covers Microsoft SQL Server across all supported versions (2016 through 2025). It covers:

- T-SQL language and query development
- Query optimization, execution plans, and the cardinality estimator
- Indexing strategies (clustered, nonclustered, columnstore, filtered, included columns)
- Always On Availability Groups and high availability architecture
- Security model (principals, securables, encryption, auditing)
- Instance and database administration
- Dynamic Management Views (DMVs) and diagnostics
- Backup, recovery, and disaster recovery planning

This skill spans SQL Server holistically. When a question is version-specific, see the matching file under `references/versions/`. When the version is unknown, provide general guidance and note where behavior differs across versions.

## How to Approach Tasks

When you receive a request:

1. **Classify** the request type:
   - **Troubleshooting** -- Load `references/diagnostics.md`
   - **Optimization** -- Load `references/best-practices.md`
   - **Architecture** -- Load `references/architecture.md`
   - **Administration** -- Follow the admin guidance below
   - **Development** -- Apply T-SQL expertise directly

2. **Identify version** -- Determine which SQL Server version the user is running. If unclear, ask. Version matters for feature availability, optimizer behavior, and DMV availability.

3. **Load context** -- Read the relevant reference file for deep knowledge.

4. **Analyze** -- Apply SQL Server-specific reasoning, not generic database advice.

5. **Recommend** -- Provide actionable, specific guidance with T-SQL examples.

6. **Verify** -- Suggest validation steps (queries, DMVs, execution plan checks).

## Core Expertise

### T-SQL Development

Write idiomatic T-SQL. Prefer set-based operations over cursors. Key principles:

- Use `EXISTS` over `IN` for correlated subqueries against large sets
- Avoid scalar UDFs in SELECT lists (pre-2019) -- they force row-by-row execution
- Use `MERGE` carefully -- it has known bugs with concurrent operations
- `THROW` over `RAISERROR` for new error handling code
- CTEs are not materialized -- they re-execute per reference. Use temp tables for repeated access.
- Window functions (`ROW_NUMBER`, `RANK`, `LAG/LEAD`) are generally more efficient than self-joins

### Query Optimization and Execution Plans

The query optimizer is cost-based. Understand what drives plan selection:

- **Statistics** -- Histograms on leading index columns. Auto-update threshold: ~20% of rows changed (with modification for large tables post-2016).
- **Cardinality Estimator** -- Legacy CE (compat < 120) vs. new CE (compat >= 120). New CE assumes correlation independence differently.
- **Plan caching** -- Plans cached on first compilation with sniffed parameter values. Recompile with `OPTION (RECOMPILE)` or plan guides when needed.
- **SARGability** -- Predicates must be in Search ARGument form. Wrapping columns in functions (`YEAR(date_col) = 2024`) prevents index seeks.

Key execution plan operators to watch:
- **Key Lookup** -- Indicates a nonclustered index is missing covering columns
- **Hash Match** -- Large joins without useful indexes; high memory grants
- **Table Scan / Clustered Index Scan** -- Full scans; check predicates and statistics
- **Sort** -- Memory-consuming; consider pre-sorted indexes
- **Parallelism (Gather Streams)** -- Check cost threshold for parallelism setting

### Indexing Strategies

| Index Type | Use When | Watch For |
|---|---|---|
| Clustered | Primary access path, range scans | Narrow, static, ever-increasing key preferred |
| Nonclustered | Selective point lookups, covering queries | Key lookups on wide rows; max 999 NC indexes |
| Columnstore | Analytics, aggregations, large scans | Row group quality; batch mode execution |
| Filtered | Subset queries (WHERE Status = 'Active') | Parameterized queries may not match filter |
| Included columns | Cover queries without widening key | Storage overhead; maintenance cost |

Index maintenance: rebuild at >30% fragmentation, reorganize at 10-30%. For columnstore, reorganize compresses open delta rowgroups.

### Always On Availability Groups

Architecture components: Windows Server Failover Cluster (WSFC) or cluster-less (2017+), replicas (primary + secondaries), availability databases, listener, endpoints.

Key operational concerns:
- **Synchronous vs. asynchronous commit** -- Synchronous guarantees zero data loss but adds latency. Use async for DR replicas across WANs.
- **Readable secondaries** -- Snapshot isolation under the covers. Long-running queries on secondaries generate version store in tempdb.
- **Automatic seeding** -- Eliminates manual backup/restore for new replicas (2016+).
- **Monitoring** -- `sys.dm_hadr_database_replica_states` for sync health, `log_send_queue_size`, `redo_queue_size`.

### Security Model

SQL Server uses a layered security model: Login -> User -> Schema -> Permissions.

- **Principle of least privilege** -- Use database roles, not sysadmin for applications
- **Transparent Data Encryption (TDE)** -- Encrypts data at rest. Performance impact: ~3-5%
- **Always Encrypted** -- Client-side encryption for sensitive columns. Limits queryability.
- **Row-Level Security (RLS)** -- Filter predicates per user. Added in 2016.
- **Dynamic Data Masking** -- Obfuscates data in query results. Not a security boundary -- privileged users can unmask.

### Wait Statistics Reference

Wait stats are the primary entry point for performance troubleshooting:

| Wait Type | Meaning | Investigation |
|---|---|---|
| `CXPACKET` / `CXCONSUMER` | Parallelism waits | Check MAXDOP, cost threshold; look for skewed parallel plans |
| `PAGEIOLATCH_SH/EX` | Reading pages from disk | Memory pressure, missing indexes, or large scans |
| `LCK_M_S/X/U/IX/IS` | Lock contention | Blocking chains; long transactions; isolation level |
| `WRITELOG` | Transaction log writes | Log disk latency; too-frequent commits; AG sync |
| `SOS_SCHEDULER_YIELD` | CPU pressure | High CPU queries; excessive parallelism; compilation |
| `ASYNC_NETWORK_IO` | Waiting for client to consume results | Application not reading results fast enough |
| `RESOURCE_SEMAPHORE` | Waiting for memory grant | Large sorts/hashes; memory grant feedback (2017+) |

Diagnostic query:
```sql
SELECT wait_type, wait_time_ms, waiting_tasks_count,
       wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','SQLTRACE_BUFFER_FLUSH',
    'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','LAZYWRITER_SLEEP',
    'CHECKPOINT_QUEUE','WAITFOR','XE_TIMER_EVENT',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','LOGMGR_QUEUE',
    'DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    'SP_SERVER_DIAGNOSTICS_SLEEP','XE_DISPATCHER_WAIT',
    'DISPATCHER_QUEUE_SEMAPHORE','WAIT_FOR_RESULTS'
)
ORDER BY wait_time_ms DESC;
```

### Parameter Sniffing

Parameter sniffing is when the optimizer compiles a plan based on the first parameter values it sees. This is a feature, not a bug -- but it becomes a problem when data distribution is skewed.

**Detection:**
```sql
-- Find plans with high variance in execution times
SELECT q.query_id, qt.query_sql_text,
       rs.avg_duration, rs.min_duration, rs.max_duration,
       rs.count_executions
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.max_duration > rs.avg_duration * 10
ORDER BY rs.count_executions DESC;
```

**Mitigation strategies (least to most invasive):**
1. **Query Store hints** (2022+) -- Force plan without code changes
2. **PSP optimization** (2022+) -- Multiple plans for different parameter ranges
3. **OPTIMIZE FOR UNKNOWN** -- Uses average density instead of sniffed values
4. **OPTION (RECOMPILE)** -- Fresh plan every execution. CPU cost per call.
5. **Plan guides** -- Force hints without changing application code
6. **Local variables** -- Masks parameter values from optimizer (loses sniffing benefit entirely)

### Common Pitfalls

**1. NOLOCK everywhere**
`WITH (NOLOCK)` / `READ UNCOMMITTED` can read uncommitted data, double-count rows, skip rows, or read corrupted pages during page splits. It is not "just faster reads." Use `READ COMMITTED SNAPSHOT` for non-blocking reads with consistency.

**2. Implicit conversions**
When comparing columns with different data types, SQL Server inserts implicit conversions. This destroys SARGability:
```sql
-- BAD: varchar column compared with nvarchar parameter
WHERE varchar_column = @nvarchar_param  -- scans entire index
-- FIX: match data types
WHERE varchar_column = CAST(@nvarchar_param AS VARCHAR(100))
```
Check for these in execution plans (yellow warning triangles) or:
```sql
SELECT * FROM sys.dm_exec_query_plan_stats  -- look for CONVERT_IMPLICIT
```

**3. Cursor overuse**
Cursors process row-by-row. Rewrite as set-based operations. If a cursor is truly needed, use `FAST_FORWARD` (read-only, forward-only) for best performance.

**4. Over-indexing**
Every index must be maintained on every INSERT/UPDATE/DELETE. Monitor unused indexes:
```sql
SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0
ORDER BY s.user_updates DESC;
```

**5. Auto-shrink**
Never enable `AUTO_SHRINK`. It causes massive fragmentation, burns CPU, and the file will just grow again. If you must reclaim space, shrink manually during a maintenance window, then rebuild indexes.

**6. Ignoring tempdb configuration**
Tempdb contention (PFS/GAM/SGAM) causes `PAGELATCH` waits. Configure one data file per logical CPU core (up to 8), equally sized, with trace flag 1118 (pre-2016) or mixed extents disabled (2016+).

## Version-specific guidance

For version-specific expertise, see:

- `references/versions/2016.md` -- Query Store, temporal tables, Always Encrypted, RLS
- `references/versions/2017.md` -- Linux support, adaptive query processing, graph DB
- `references/versions/2019.md` -- Intelligent Query Processing, ADR, Big Data Clusters
- `references/versions/2022.md` -- PSP optimization, ledger tables, contained AG
- `references/versions/2025.md` -- Native vectors, RegEx, JSON index, optimized locking

## Reference Files

Load these when you need deep knowledge for a specific area:

- `references/architecture.md` -- Storage engine internals, buffer pool, memory architecture, query processing pipeline. Read for "how does X work" questions.
- `references/diagnostics.md` -- Wait stats workflow, DMV reference, Query Store, Extended Events. Read when troubleshooting performance or errors.
- `references/best-practices.md` -- Instance configuration, backup strategy, index maintenance, security hardening, monitoring. Read for design and operations questions.