MSSQL DBA MCP Stdio Server

The server provides context to an LLM, empowering models to complete various Database Administration tasks, such as optimizing queries, improving table health, optimizing existing indexes, and identifying missing ones. The server aims to find the best way to provide context so the LLMs can maximize their potential in completing DBA tasks. For feedback, questions, or support, please join our Discord!

Demo
Prerequisites
Configuration
- Usage with MCP Client
  - npx
Prompts
Tools
License
Contact

Demo

This example demonstrates how to optimize a slow-running query using the Query Store integration:

1. User invokes the prompt optimize-query-with-query-store with a problem description:

Problem: The customer orders report query is taking 5+ seconds to complete during peak hours.

2. MCP retrieves Query Store context:

The server identifies the query using unique substrings and fetches historical performance data:

127 executions over the past week
Average duration: 5,234ms (Critical performance)
High logical reads: 45,000+ pages per execution
Execution plan ID: 142

3. MCP gathers table and index information:

The server analyzes the involved tables:

Orders table: 2.5M rows, clustered index on OrderID
Customers table: 500K rows, missing index on CustomerRegion
Current indexes show fragmentation >30%

4. LLM analyzes and generates optimizations:

Based on the execution plan and index analysis, the LLM creates:

customer_orders_report_schema_optimizations.sql - New filtered index on Orders(CustomerID, OrderDate) INCLUDE (TotalAmount) and non-clustered index on Customers(CustomerRegion, CustomerID)
customer_orders_report_rollback_script.sql - DROP statements for safe rollback
Detailed documentation explaining why each optimization improves query performance

Result: Query execution time reduced from 5.2s to 380ms after applying the suggested indexes.

Prerequisites

Mandatory: Create a user with these permissions:

USE [master];
GO

CREATE LOGIN [dbai] WITH PASSWORD = 'YourStrongPasswordHere';
GO

GRANT VIEW SERVER STATE TO [dbai];
GO

USE [YourDatabase];
GO

CREATE USER [dbai] FOR LOGIN [dbai];
GO

GRANT VIEW DEFINITION TO [dbai];
GO

Optional: Enable Query Store

Although optional, enabling Query Store is highly recommended to get the maximum value from the MCP server. Query Store provides historical query performance data that enables advanced optimization capabilities and performance troubleshooting.

-- Check if Query Store is enabled
DECLARE @QueryStoreEnabled bit;
DECLARE @DatabaseName sysname = DB_NAME();

SELECT @QueryStoreEnabled = is_query_store_on
FROM sys.databases
WHERE name = @DatabaseName;

IF @QueryStoreEnabled <> 1
BEGIN
    PRINT 'Query Store is not enabled. Enabling now...';
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @Version INT = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT);
    SET @SQL = N'
        ALTER DATABASE [' + @DatabaseName + N'] SET QUERY_STORE = ON
        (
            OPERATION_MODE = READ_WRITE,
            CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
            DATA_FLUSH_INTERVAL_SECONDS = 900,
            INTERVAL_LENGTH_MINUTES = 5,
            MAX_STORAGE_SIZE_MB = 1000,
            QUERY_CAPTURE_MODE = AUTO,
            SIZE_BASED_CLEANUP_MODE = AUTO,'
            + CASE WHEN @Version >= 14 THEN 'WAIT_STATS_CAPTURE_MODE = ON' ELSE '' END + '
        );';
    EXEC sys.sp_executesql @SQL;
    PRINT 'Query Store has been enabled successfully.';
END;
-- Stored Procedure to immediately write query store data to disk
IF NOT EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'usp_FlushQueryStore' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    EXEC('CREATE PROCEDURE dbo.usp_FlushQueryStore AS BEGIN SELECT 1 END');
END;
GO

ALTER PROCEDURE dbo.usp_FlushQueryStore
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    EXEC sp_query_store_flush_db;
END;
GO

-- Grant execute permission to the user
GRANT EXECUTE ON dbo.usp_FlushQueryStore TO [dbai];
GO

-- Always show final configuration
SELECT
    actual_state_desc AS [Current State],
    readonly_reason AS [Readonly Reason],
    desired_state_desc AS [Desired State],
    current_storage_size_mb AS [Current Size (MB)],
    max_storage_size_mb AS [Max Size (MB)],
    flush_interval_seconds AS [Flush Interval (seconds)],
    interval_length_minutes AS [Stats Interval (minutes)],
    stale_query_threshold_days AS [Stale Query Threshold (days)],
    size_based_cleanup_mode_desc AS [Cleanup Mode],
    query_capture_mode_desc AS [Capture Mode],
    max_plans_per_query AS [Max Plans Per Query],
    wait_stats_capture_mode_desc AS [Wait Stats Capture],
    (
        SELECT CAST(DATEDIFF(DAY, MIN(start_time), SYSUTCDATETIME()) AS INT)
        FROM sys.query_store_runtime_stats_interval
    ) AS [Approx Enabled (Days)]
FROM sys.database_query_store_options;

Configuration

Usage with MCP Client

There is a partial list of existing clients at modelcontextprotocol.io. Consult their documentation to install the MCP server.

Currently we support only SQL Server Authenitcation method.

Example setup for Claude Code: Add this to your claude_desktop_config.json:

npx

{
  "mcpServers": {
    "mssql-dba": {
      "command": "npx",
      "args": ["@dba-i/mssql-dba"],
      "env": {
        // Required Parameters
        "DB_HOST": "host",
        "DB_PORT": "1433",
        "DB_USER": "dbai",
        "DB_PASSWORD": "password",
        "DB_NAME": "database name",
        // Optional Parameters
        "TRUST_SERVER_CERTIFICATE": "true",
        "ENCRYPT": "false",
        "MAX_POOL": "10",
        "MIN_POOL": "0",
        "IDLE": "30000"
      }
    }
  }
}

Prompts

Optimize Query with Query Store

Name: optimize-query-with-query-store
Description: Analyze historical query runs and optimize problematic queries using Query Store data.
Input:
- problem: Description of the performance problem related to the query.
Behavior:
- Analyzes Query Store statistics to identify patterns and insights into performance problems.
- Retrieves execution plans for problematic query runs.
- Examines table-level information and existing indexes to support optimization recommendations.
- Generates schema optimization scripts with rollback steps.
- Documents all optimizations with explanations.
- Returns CSV-formatted query statistics for easy analysis.

Optimize Query

Name: optimize-query
Description: Optimize SQL queries for better performance.
Input:
- query: The SQL query to optimize.
Behavior:
- Identifies tables involved in the query and fetches comprehensive schema information.
- Analyzes existing indexes and identifies potential redundancies or missing indexes.
- Suggests schema-level optimizations and highlights query inefficiencies.
- Generates optimized query files and schema optimization scripts with detailed documentation.

Optimize Indexes

Name: optimize-indexes
Description: Optimize indexes on specified tables.
Input:
- tableNames: A string containing the names of tables to optimize.
Behavior:
- Fetches information about existing and missing indexes for the specified tables.
- Identifies inefficient indexes and suggests removing them.
- Provides options for index consolidation where possible.
- Analyzes missing indexes and includes them if necessary, while avoiding redundant indexes.
- Generates index optimization scripts with detailed documentation.
- Creates rollback scripts and index maintenance stored procedures.

Tools

The MCP server exposes the following tools organized by scope:

Query Store Tools

Get Query Stats from Query Store

Name: get-query-stats-from-query-store
Description: Retrieve performance statistics for a specific query from the Query Store.
Input:
- filters (array of strings): Array of substrings to uniquely identify the query in Query Store.
Output:
- CSV-formatted query statistics including:
  - Execution plan IDs
  - Duration metrics (average, min, max, standard deviation)
  - CPU time metrics
  - Logical and physical I/O statistics
  - Memory grant information
  - Degree of parallelism
  - Row count statistics
  - Performance assessments (Good, Moderate, Slow, Critical)
  - Overall status indicators

Get Execution Plan from Query Store

Name: get-execution-plan-from-query-store
Description: Retrieve the execution plan for a specific plan ID from Query Store.
Input:
- planId (number): The plan ID to retrieve the execution plan for.
Output:
- JSON representation of the execution plan in ShowPlanXML format.

Table-Level Tools

Get Tables Info

Name: get-tables-info
Description: Get the metadata about specified tables.
Input:
- tableNames (array of strings): Names of the tables to retrieve metadata for.
Output:
- JSON metadata about the specified tables.

Get Tables Index Health

Name: get-tables-index-health
Description: Assess index health for specified tables.
Input:
- tableNames (array of strings): Names of the tables to assess.
Output:
- JSON with index health information for the specified tables.

Get Tables Missing Indexes

Name: get-tables-missing-indexes
Description: Identify missing indexes for specified tables.
Input:
- tableNames (array of strings): Names of the tables to check for missing indexes.
Output:
- JSON with missing indexes.

Server-Level Tools

Get Server Version

Name: get-server-version
Description: Retrieve information about the SQL Server instance such as version, current update level, edition, and licensing details.
Input:
- No input parameters required.
Output:
- JSON with comprehensive SQL Server instance version and product level.

Get Server Timezone

Name: get-server-timezone
Description: Retrieve the current timezone settings of the SQL Server instance.
Input:
- No input parameters required.
Output:
- JSON with timezone information including server date/time values, UTC values, timezone offsets, and OS timezone configuration.

Database-Level Tools

Get All Missing Indexes

Name: get-all-missing-indexes
Description: Retrieve all missing index recommendations for the current database.
Input:
- No input parameters required.
Output:
- CSV-formatted missing index recommendations including:
  - Table name and row count
  - Read/write workload metrics
  - Equality, inequality, and included columns
  - Impact scores and user seeks/scans
  - Estimated improvement percentages
  - Average user impact
  - Create index statements

Get Top Cumulative Waits

Name: get-top-cumulative-waits
Description: Retrieve the top cumulative waits for the current database.
Input:
- No input parameters required.
Output:
- CSV-formatted wait statistics including:
  - Wait type
  - Total wait time (seconds)
  - Resource wait time (seconds)
  - Signal wait time (seconds)
  - Wait count
  - Percentage of total waits
  - Average wait time (milliseconds)
  - Running percentage

Check Buffer Hit Ratio

Name: checkBufferHitRatio
Description: Retrieve the buffer cache hit ratio for the current database.
Input:
- No input parameters required.
Output:
- JSON with buffer cache hit ratio percentage (higher values indicate better memory utilization).

Get Database Collation

Name: get-db-collation
Description: Retrieve the collation setting for the current database.
Input:
- No input parameters required.
Output:
- JSON with the database's collation information.

Get Collation Mismatches

Name: get-collation-mismatches
Description: Retrieve the columns with collation settings that differ from the database default.
Input:
- No input parameters required.
Output:
- JSON with information about columns that have collation mismatches with the database default.

License

This project is licensed under the Elastic License 2.0 (ELv2).

Contact

For feedback, questions or support, please join our Discord.

mssql-dba

Documentation