MSSQL Performance Tuning MCP Server

A comprehensive Model Context Protocol (MCP) server for SQL Server performance tuning and optimization. This server provides AI assistants with powerful tools to diagnose and optimize MSSQL database performance through Dynamic Management Views (DMVs) and system catalogs.

🎯 Features

Performance Analysis Tools

• Slow Query Detection - Identify CPU and I/O intensive queries
• Missing Index Analysis - Find missing indexes with impact estimates
• Unused Index Detection - Identify indexes consuming resources without benefit
• Index Fragmentation - Check fragmentation levels and get maintenance recommendations
• Wait Statistics - Analyze what SQL Server is waiting on
• Blocking Detection - Identify blocking queries and lock chains
• Query Store Analysis - Historical query performance analysis
• Index Usage Statistics - Detailed index utilization metrics
• Table Size Analysis - Storage consumption by table
• Statistics Health - Identify outdated statistics
• Performance Health Check - Comprehensive database health assessment
• Index Maintenance Scripts - Auto-generate rebuild/reorganize scripts
• Execution Plan Analysis - Retrieve and analyze query execution plans

📋 Prerequisites

• Node.js 18+ with npm
• TypeScript 5.3+
• SQL Server 2016+ or Azure SQL Database
• SQL Server credentials with appropriate permissions:
  • VIEW SERVER STATE permission for DMV access
  • VIEW DATABASE STATE for database-specific queries
  • VIEW DEFINITION for object metadata

🚀 Installation

1. Install Globally

npm install -g mssql-performance-mcp

2. Configure Claude Desktop or Cursor

Add to your MCP configuration file:

Claude Desktop (macOS): ~/Library/Application Support/Claude/claude_desktop_config.json
Claude Desktop (Windows): %APPDATA%\Claude\claude_desktop_config.json
Cursor: ~/.cursor/mcp.json

{
  "mcpServers": {
    "mssql-performance-mcp": {
      "command": "npx",
      "args": [
        "-y",
        "mssql-performance-mcp"
      ]
    }
  }
}

3. Restart Claude Desktop or Cursor

That's it! The MCP server will be available immediately.

🛠️ Available Tools

1. get_slow_queries

Identify slow-running queries using sys.dm_exec_query_stats.

Parameters:

• server (required): SQL Server hostname or IP
• user (required): SQL Server username
• password (required): SQL Server password
• database (optional): Specific database name
• port (optional): Port number (default: 1433)
• top_n (optional): Number of queries to return (default: 20)
• order_by (optional): Sort metric - cpu, duration, reads, executions (default: cpu)

Example:

Get the top 10 slowest queries by CPU time from server 'sql-prod-01'

Returns:

• Query text
• Execution statistics (count, CPU time, duration)
• Logical/physical reads
• Creation and last execution times

2. get_missing_indexes

Identify missing indexes that could improve performance.

Parameters:

• server (required): SQL Server hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 1433)
• min_impact (optional): Minimum improvement measure (default: 10000)
• top_n (optional): Number of recommendations (default: 20)

Example:

Find missing indexes with high impact on database 'TestDB_5100'

Returns:

• CREATE INDEX statements ready to execute
• Impact score and usage statistics
• Equality, inequality, and included columns
• Seek/scan counts

3. get_unused_indexes

Find unused or rarely-used indexes consuming storage.

Parameters:

• server (required): SQL Server hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 1433)
• min_size_mb (optional): Minimum size to consider (default: 10 MB)

Example:

Find unused indexes larger than 50 MB

Returns:

• DROP INDEX statements
• Index size and row count
• Usage statistics (seeks, scans, updates)

4. get_index_fragmentation

Check index fragmentation using sys.dm_db_index_physical_stats.

Parameters:

• server (required): SQL Server hostname
• database (required): Database name
• user (required): Username
• password (required): Password
• port (optional): Port (default: 1433)
• min_fragmentation (optional): Minimum % to report (default: 10)
• min_pages (optional): Minimum page count (default: 1000)

Example:

Check fragmentation for database 'TestDB_5100' where fragmentation > 20%

Returns:

• Fragmentation percentages
• Maintenance recommendations (REORGANIZE vs REBUILD)
• Ready-to-execute maintenance scripts

5. get_wait_statistics

Analyze wait statistics to identify bottlenecks.

Parameters:

• server (required): SQL Server hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 1433)
• top_n (optional): Number of wait types (default: 20)

Example:

What are the top wait types causing performance issues?

Returns:

• Wait types and wait times
• Signal vs resource waits
• Actionable recommendations for each wait type

6. get_database_statistics

Check statistics health and last update times.

Parameters:

• server (required): SQL Server hostname
• database (required): Database name
• user (required): Username
• password (required): Password
• port (optional): Port (default: 1433)
• days_old (optional): Show stats older than N days (default: 7)

Example:

Show statistics older than 14 days for database 'TestDB_5100'

Returns:

• Statistics age and modification counts
• UPDATE STATISTICS scripts
• Priority recommendations

7. get_blocking_queries

Identify currently blocking queries.

Parameters:

• server (required): SQL Server hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 1433)

Example:

Show me all blocking queries right now

Returns:

• Blocked and blocker session details
• Query texts for both blocked and blocking sessions
• Wait duration and types

8. get_query_store_top_queries

Get historical query performance from Query Store.

Parameters:

• server (required): SQL Server hostname
• database (required): Database name
• user (required): Username
• password (required): Password
• port (optional): Port (default: 1433)
• hours_back (optional): Hours of history (default: 24)
• top_n (optional): Number of queries (default: 20)
• order_by (optional): Metric - cpu, duration, logical_reads, executions

Example:

Get top CPU queries from Query Store for the last 48 hours

Returns:

• Query text and execution statistics
• Aggregated metrics over time period
• First and last execution times

9. get_index_usage_stats

Detailed index usage statistics.

Parameters:

• server (required): SQL Server hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 1433)
• schema (optional): Filter by schema
• table (optional): Filter by table

Example:

Show index usage for table 'Items' in schema 'Item'

Returns:

• Seeks, scans, lookups, updates
• Read/write ratios
• Last access times
• Index sizes

10. get_table_sizes

Get table sizes and row counts.

Parameters:

• server (required): SQL Server hostname
• database (required): Database name
• user (required): Username
• password (required): Password
• port (optional): Port (default: 1433)
• top_n (optional): Number of tables (default: 50)

Example:

Show me the 20 largest tables in database 'TestDB_5100'

Returns:

• Table sizes (data, indexes, total)
• Row counts
• Index counts per table

11. get_performance_health_check

Comprehensive database health assessment.

Parameters:

• server (required): SQL Server hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 1433)

Example:

Run a full performance health check on my database

Returns:

• Top CPU consumers
• Wait statistics summary
• Missing indexes count
• Fragmentation summary
• Database size
• Server uptime

12. generate_index_maintenance_script

Generate index maintenance script based on fragmentation.

Parameters:

• server (required): SQL Server hostname
• database (required): Database name
• user (required): Username
• password (required): Password
• port (optional): Port (default: 1433)
• reorganize_threshold (optional): Fragmentation % for reorganize (default: 10)
• rebuild_threshold (optional): Fragmentation % for rebuild (default: 30)
• online (optional): Use ONLINE rebuild (default: true)

Example:

Generate index maintenance script for database 'TestDB_5100' with online rebuilds

Returns:

• Complete T-SQL maintenance script
• Commands based on fragmentation levels
• Count of indexes requiring maintenance

13. get_execution_plan

Get and analyze execution plans for queries, stored procedures, or Query Store queries.

Parameters:

• server (required): SQL Server hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 1433)
• query_text (optional): Search for plan by query text (partial match)
• stored_procedure (optional): Get plan for a stored procedure (format: schema.procedurename)
• query_id (optional): Get plan from Query Store by query_id
• plan_handle (optional): Get plan by specific plan_handle
• include_actual_plan (optional): Include full XML plan (default: false)
• save_xml_to_file (optional): Path to save execution plan XML file (prevents crashes from large XMLs)
• max_xml_size_kb (optional): Maximum XML size in KB to return inline (default: 100KB). Larger plans auto-save to file

Example:

Get the execution plan for stored procedure 'dbo.GetCustomerOrders'

Analyze the execution plan for queries containing 'SELECT * FROM Orders'

Get execution plan for Query Store query_id 12345 with full XML

Returns:

• Execution plan source (Query Store, Plan Cache, Procedure Cache)
• Query text or procedure name
• Execution statistics (CPU, duration, reads, writes)
• Plan Analysis:
  • Warnings (cartesian products, memory spills, missing statistics)
  • Expensive operators (top 5 by cost)
  • Missing indexes with impact scores
  • Key lookups count
  • Table scans detected
  • Index scans that could be seeks
  • Parallelism usage
  • Row estimate information
• Recommendations:
  • Index creation suggestions
  • Query optimization tips
  • Statistics update recommendations
  • Memory and configuration suggestions
• Optional: Full execution plan XML

Analysis Features:

• Detects cartesian products from missing JOIN predicates
• Identifies memory spills to tempdb
• Finds missing indexes with estimated impact
• Detects key lookups that slow queries
• Identifies table scans on large tables
• Analyzes parallelism usage
• Extracts cost information for operators
• Provides actionable recommendations

Handling Large Execution Plans:

Complex queries (especially those with many CTEs, subqueries, or joins) can generate very large execution plan XMLs (100KB - several MB) that may crash your environment. To prevent this:

1. Automatic File Saving: Plans larger than 100KB are automatically saved to .sqlplan files

   Get execution plan for stored procedure 'Item.ItemList'

   Result: execution_plan_proc_Item_ItemList_1673456789.sqlplan created in current directory

2. Manual File Path: Specify where to save the XML

   {
     "stored_procedure": "Item.ItemList",
     "save_xml_to_file": "/path/to/itemlist_plan.sqlplan"
   }

3. Adjust Size Threshold: Change the auto-save threshold

   {
     "stored_procedure": "Item.ItemList",
     "max_xml_size_kb": 50  // Auto-save if larger than 50KB
   }

4. Open Saved Plans:

   • SQL Server Management Studio (SSMS): File → Open → File, select .sqlplan file
   • Azure Data Studio: Right-click → Open File
   • Both tools provide graphical execution plan visualization

Best Practices:

• For complex stored procedures, always use save_xml_to_file to prevent crashes
• Default 100KB threshold works well for most environments
• Saved .sqlplan files can be opened in SSMS/Azure Data Studio for visual analysis
• The tool still returns full plan analysis and recommendations even when XML is saved to file

📝 Usage Examples

Example 1: Diagnose Slow Performance

I need to diagnose slow performance on server 'sql-prod-01', database 'TestDB_5100'.
Can you check:
1. The top 10 slowest queries
2. Wait statistics
3. Missing indexes
4. Index fragmentation issues

Example 2: Optimize a Specific Database

Please analyze database 'TestDB_54625' on server 'sql-aws-rds.amazonaws.com':
- Find unused indexes larger than 100 MB
- Check for outdated statistics (older than 30 days)
- Get a full health check report

Example 3: Generate Maintenance Plan

For database 'TestDB_5100':
1. Check index fragmentation
2. Generate a maintenance script for reorganize/rebuild
3. Use ONLINE operations where possible

Example 4: Historical Analysis

Using Query Store, show me the top 20 CPU-consuming queries 
from the last 7 days for database 'TestDB_Production'

Example 5: Analyze Query Execution Plans

For database 'TestDB_5100':
1. Get the execution plan for stored procedure 'dbo.ItemListByMeetingSubmission'
2. Analyze what operations are expensive
3. Check for missing indexes or table scans
4. Provide optimization recommendations

I have a slow query that searches Orders by CustomerID. 
Can you get its execution plan and tell me if there are any issues?

🔐 Security Considerations

1. Credentials: Never hardcode credentials. Always pass them as parameters.
2. Permissions: Use SQL Server logins with minimal required permissions.
3. Encryption: Enable TLS/SSL encryption for connections (encrypt: true).
4. Production: Test on non-production environments first.
5. Read-Only: This server only reads data (except when you execute generated scripts manually).

📊 Best Practices

Performance Tuning Workflow

1. Start with Health Check

   Run a performance health check to get an overview

2. Identify Bottlenecks

   Check wait statistics to understand what SQL Server is waiting on

3. Find Slow Queries

   Get slow queries ordered by CPU time

4. Check Indexes

   - Look for missing indexes with high impact
   - Identify unused indexes
   - Check fragmentation levels

5. Verify Statistics

   Check for outdated statistics

6. Generate Maintenance

   Create index maintenance scripts based on findings

Interpretation Guide

Wait Types:

• PAGEIOLATCH_*: Disk I/O issues - consider more memory or faster storage
• WRITELOG: Transaction log waits - check log disk performance
• CXPACKET: Parallelism - may need MAXDOP adjustment
• LCK_M_*: Locking issues - check for blocking queries
• SOS_SCHEDULER_YIELD: CPU pressure

Index Recommendations:

• Improvement Measure > 100,000: High priority
• Improvement Measure > 10,000: Medium priority
• Improvement Measure < 10,000: Low priority

Fragmentation:

• < 10%: No action needed
• 10-30%: REORGANIZE recommended
• > 30%: REBUILD recommended

🐛 Troubleshooting

Connection Issues

Error: Login failed for user 'username'

Solution: Verify credentials and ensure SQL Server authentication is enabled.

Error: Cannot connect to server

Solution: Check firewall rules, server name, and port number.

Permission Errors

Error: The SELECT permission was denied on the object 'sys.dm_exec_query_stats'

Solution: Grant VIEW SERVER STATE permission:

GRANT VIEW SERVER STATE TO [username];

Query Store Not Available

Query Store is not enabled for this database

Solution: Enable Query Store:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

🔄 Development

Build and Watch

# Build once
npm run build

# Watch for changes
npm run watch

# Run directly
npm start

Testing

Connect to your test SQL Server instance and try queries through Claude:

Connect to server 'localhost', database 'TestDB', user 'sa', and get the top 5 slow queries

📚 References

• SQL Server Dynamic Management Views
• Query Store
• Index Maintenance
• MCP Protocol Specification

🤝 Contributing

Contributions welcome! Areas for enhancement:

• Additional DMV queries
• More optimization recommendations
• Azure SQL-specific optimizations
• Automated tuning suggestions
• Performance baselines and trends

📄 License

MIT License - see LICENSE file for details

🎯 Roadmap

• [ ] Add baseline performance tracking
• [ ] Implement automatic recommendation engine
• [ ] Add support for AlwaysOn Availability Groups
• [ ] Create performance trend analysis
• [x] Add query plan analysis ✅ (v1.1.0)
• [ ] Implement automatic index recommendations
• [ ] Add tempdb monitoring
• [ ] Create execution plan visualization support
• [ ] Add support for analyzing actual execution plans vs estimated
• [ ] Add plan comparison tools for query regression analysis

Built with ❤️ for SQL Server DBAs and Performance Engineers