Guide MS SQL DBA

An expert agent for Microsoft SQL Server database administration covering performance tuning, index optimization, backup strategies, security hardening, and high availability configurations for production SQL Server environments.

When to Use This Agent

Choose MS SQL DBA Guide when:

• Tuning SQL Server query performance and optimizing execution plans
• Designing index strategies for high-transaction workloads
• Configuring backup, restore, and disaster recovery procedures
• Setting up Always On availability groups or failover clustering
• Troubleshooting deadlocks, blocking, and resource contention issues

Consider alternatives when:

• Working with PostgreSQL or MySQL (use database-specific agents)
• Designing application-level data access patterns (use a backend dev agent)
• Building data warehouses on non-SQL Server platforms (use a data engineering agent)

Quick Start

Copy
# .claude/agents/guide-ms-sql-dba.yml
name: MS SQL DBA Guide
model: claude-sonnet-4-20250514
tools:
  - Read
  - Write
  - Bash
  - Glob
  - Grep
prompt: |
  You are a Microsoft SQL Server DBA expert. Optimize database
  performance, design backup strategies, configure high availability,
  and troubleshoot production issues. Always verify the mssql
  extension is available before running database operations.

Example invocation:

Copy
claude --agent guide-ms-sql-dba "Analyze the slow query on the
  Orders table - it's taking 15 seconds for a simple date range
  filter. Check the execution plan and recommend index changes."

Core Concepts

Performance Tuning Workflow

Step	Action	Tools
Identify	Find slow queries from DMVs	sys.dm_exec_query_stats
Analyze	Review execution plans	SET STATISTICS IO/TIME
Diagnose	Check indexes, statistics, waits	sys.dm_db_index_usage_stats
Optimize	Add indexes, rewrite queries	CREATE INDEX, query hints
Validate	Compare before/after metrics	Execution plan diff
Monitor	Track regression over time	Query Store

Index Strategy Matrix

Copy
-- Covering index for frequent query pattern
CREATE NONCLUSTERED INDEX IX_Orders_DateCustomer
ON Orders (OrderDate, CustomerID)
INCLUDE (TotalAmount, Status)
WHERE Status != 'Cancelled'  -- Filtered index

-- Columnstore for analytics workload
CREATE NONCLUSTERED COLUMNSTORE INDEX CCI_Orders_Analytics
ON Orders (OrderDate, ProductID, TotalAmount, Quantity)

Index Type	Best For	Overhead
Clustered	Range scans, primary key lookups	Table reorganization
Nonclustered	Point lookups, covering queries	Write overhead per index
Filtered	Subset queries with WHERE clause	Lower maintenance
Columnstore	Aggregation, analytics queries	Batch mode processing
Full-text	Text search within columns	Separate catalog

Backup Strategy

Full Backup (weekly) → Differential (daily) → Transaction Log (every 15 min)

Recovery Point Objective (RPO): 15 minutes max data loss
Recovery Time Objective (RTO): < 1 hour to restore

Configuration

Parameter	Description	Default
compatibility_level	SQL Server version compatibility	Latest
max_dop	Maximum degree of parallelism	CPU count / 2
cost_threshold	Cost threshold for parallelism	50
max_memory_mb	Maximum server memory	Total - 4GB for OS
backup_compression	Enable backup compression	true
query_store	Enable Query Store	ON
tempdb_files	Number of TempDB data files	CPU core count (max 8)

Best Practices

1. Enable Query Store on every production database. Query Store captures execution plans and runtime statistics over time, letting you identify plan regressions without third-party tools. When a query suddenly slows down, Query Store shows you which plan changed and lets you force the previous good plan while you investigate. It's built-in, low-overhead, and invaluable for troubleshooting.

2. Size TempDB correctly from the start. Create one TempDB data file per CPU core (up to 8), all the same size, with autogrowth set to the same increment. Pre-size files to avoid autogrowth during production workloads. Place TempDB on the fastest available storage. TempDB contention from incorrect sizing causes system-wide blocking that's easily preventable.

3. Use filtered indexes for queries on data subsets. When queries consistently filter on a specific condition (active orders, recent records, non-null values), a filtered index covers those queries with a fraction of the storage and maintenance cost of a full index. The index only contains rows matching the filter, making it smaller, faster to scan, and cheaper to maintain during writes.

4. Test backup restores regularly, not just backup jobs. A backup you can't restore is worthless. Schedule monthly restore tests to a non-production server. Verify the restore completes successfully, the data is intact, and the recovery time meets your RTO. Document the exact restore procedure so any team member can execute it under pressure during an actual outage.

5. Monitor wait statistics to find the real bottleneck. Don't guess whether the problem is CPU, disk, memory, or locking—wait statistics tell you exactly what SQL Server is waiting on. Query sys.dm_os_wait_stats to find the dominant wait types. CXPACKET waits suggest parallelism issues, PAGEIOLATCH waits indicate disk pressure, LCK waits point to blocking problems. Fix the top wait type before anything else.

Common Issues

Queries slow down after statistics become stale. SQL Server uses statistics to build execution plans. When statistics don't reflect current data distribution, the optimizer chooses poor plans. Enable AUTO_UPDATE_STATISTICS_ASYNC to update statistics without blocking queries. For large tables where auto-update triggers too late, schedule manual statistics updates during maintenance windows with FULLSCAN for critical indexes.

Deadlocks between concurrent transactions. Deadlocks occur when transactions hold locks in conflicting orders. First, capture the deadlock graph from the system_health extended event session. Then redesign transactions to access tables in a consistent order. Use READ COMMITTED SNAPSHOT isolation to reduce reader-writer conflicts. Keep transactions as short as possible and avoid user interaction within transaction boundaries.

TempDB contention during high-concurrency workloads. When many sessions create and drop temp tables simultaneously, allocation page contention (PFS, GAM, SGAM pages) causes system-wide slowdowns. Fix this by creating multiple TempDB data files of equal size (one per core, up to 8), enabling trace flag 1118 (for versions before 2016), and avoiding unnecessary temp table creation in frequently executed queries.