SQL Server Skills

SQL Server Skills

Comprehensive SQL Server skill for AI agents. Covers performance diagnostics, index analysis, execution plan interpretation, query optimization, schema management, backup/restore, and monitoring — all via sqlcmd and T-SQL DMVs.

Requirements

• sqlcmd — Microsoft Download
• SQL Server 2016+ — All DMV queries target compatibility level 130+
• Permissions — VIEW SERVER STATE for most DMV queries; sysadmin or db_owner for some operations

Quick Connect

# Windows Authentication (domain-joined machine)
sqlcmd -S "$SQL_SERVER" -E

# SQL Authentication
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d "$SQL_DATABASE"

# Named instance + specific database
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d "$SQL_DATABASE"

# Run a diagnostic script
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d master -i scripts/top-slow-queries.sql

# Run with output to file
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d master -i scripts/wait-stats.sql -o results.txt -s "," -W

Skill Organization

Decision Tree — What Are You Trying To Do?

Is the server slow or a query timing out?
├── I don't know WHERE the bottleneck is → sqlserver-diagnostics
│   └── Start with wait-stats.sql, then top-slow-queries.sql
│
├── I have a specific slow query → sqlserver-execution-plans
│   └── Capture the plan, identify bad operators
│
├── I suspect missing or broken indexes → sqlserver-indexes
│   └── Run missing-indexes.sql + index-fragmentation.sql
│
└── I want to rewrite/fix bad T-SQL code → sqlserver-query-optimization
    └── Check anti-patterns: cursors, non-SARGable, DELETE+INSERT loops

Is there a blocking/locking issue?
└── sqlserver-monitoring (blocking-analysis.sql)

Do I need to change the schema?
└── sqlserver-schema

Do I need to backup or restore a database?
└── sqlserver-backup

Do I need to check SQL Agent jobs or the error log?
└── sqlserver-monitoring

Common Workflows

Workflow 1: Server Is Slow — Start Here

# Step 1: What is SQL Server waiting on?
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d master -i scripts/wait-stats.sql

# Step 2: Which queries are consuming the most resources?
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d master -i scripts/top-slow-queries.sql

# Step 3: What's running right now?
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d master -i scripts/active-queries.sql

Then read sqlserver-diagnostics/SKILL.md to interpret results.

Workflow 2: Optimize a Specific Query

-- Step 1: Capture I/O and time stats
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- paste your query here
GO

-- Step 2: Get XML execution plan
SET STATISTICS XML ON;
GO
-- paste your query here
GO
SET STATISTICS XML OFF;

Then read sqlserver-execution-plans/SKILL.md to interpret the plan.

Workflow 3: Monthly Index Maintenance

# Find missing indexes (sorted by impact score)
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d "$SQL_DATABASE" -i scripts/missing-indexes.sql

# Check fragmentation
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d "$SQL_DATABASE" -i scripts/index-fragmentation.sql

# Find unused indexes costing write overhead
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d "$SQL_DATABASE" -i scripts/unused-indexes.sql

See sqlserver-indexes/SKILL.md for interpretation and the rebuild/reorganize decision.

Workflow 4: Investigate Blocking

# Run blocking analysis
sqlcmd -S "$SQL_SERVER" -U "$SQL_USER" -P "$SQL_PASSWORD" -d master -i scripts/blocking-analysis.sql

See sqlserver-monitoring/SKILL.md for deadlock investigation and KILL guidance.

Sub-Skill Quick Reference

• sqlserver-diagnostics/SKILL.md — DMV-based bottleneck analysis (most important starting point)
• sqlserver-indexes/SKILL.md — Full index lifecycle: find, fix, maintain, drop
• sqlserver-execution-plans/SKILL.md — Read plans, spot table scans, fix key lookups
• sqlserver-query-optimization/SKILL.md — Stored proc rewrites, anti-patterns, hints
• sqlserver-schema/SKILL.md — DDL patterns, migrations, data type guidance
• sqlserver-backup/SKILL.md — Backup/restore commands and history queries
• sqlserver-monitoring/SKILL.md — Jobs, error log, blocking, deadlocks