Serveur MCP sql-querystore

mcp-name: io.github.deepeshd87/mcp-sql-querystore

mcp-sql-querystore

Read-only MCP server exposing SQL Server Query Store diagnostics to LLM agents.

Six read-only diagnostic tools over Query Store, DMVs, and execution plans. The tools have been validated against a live SQL Server instance and are covered by a unit + integration test suite. Still validate against a non-prod instance of your own before pointing it at production, especially on SQL Server versions other than those noted under caveats.

Quickstart

1. Provision a read-only login. Run provisioning/create_readonly_login.sql against your instance (edit names first). This login's permissions are the read-only guarantee — see the security model below.

2. Install. pip install -e . in a virtual environment. ODBC Driver 18 for SQL Server must be installed on the host.

3. Store the password outside the repo. Put it in a plain-text file somewhere the repo can't reach (not under the project folder):

   # Windows PowerShell, UTF-8, password only, no quotes/newline
   New-Item -ItemType Directory -Force C:\Users\you\secrets | Out-Null
   Set-Content -NoNewline -Encoding utf8 C:\Users\you\secrets\mcp_sql.pwd 'your-password'
   

   Or skip the password entirely with integrated auth (MCP_SQL_TRUSTED=yes) — preferred for CJIS/PCI. See Secret handling below for all options.

4. Configure your MCP client. Copy the sql-querystore block from claude_desktop_config.example.json into your real Claude Desktop config (Windows: %APPDATA%\Claude\claude_desktop_config.json), then replace the placeholder paths, server name, and MCP_SQL_PWD_FILE. Set MCP_SQL_TRUST_CERT=yes only for a self-signed/local cert; leave it no against instances with proper certificates.

5. Restart your MCP client and confirm the server shows as running.

Never commit your real config or your password file. .gitignore already excludes *.pwd, .env, and claude_desktop_config.json.

Security model (read this first)

The read-only guarantee comes from the SQL login's permissions, not from any code in this repo:

• Provision a dedicated login with VIEW DATABASE STATE (and VIEW SERVER STATE only if you use server-scoped DMVs) and nothing else — no db_datareader, no SELECT on user tables. See provisioning/create_readonly_login.sql.
• The keyword screen in db.py and the fixed SELECT-only query text are defense-in-depth, not the primary control.
• ApplicationIntent=ReadOnly in the connection string only routes to a readable secondary in an availability group. On a standalone instance it does not make the session read-only. Do not rely on it for safety.
• Credentials never belong in code. The simplest setup uses the MCP_SQL_CONNECTION_STRING env var, but for CJIS/PCI environments prefer integrated auth or a file/secret-store-sourced password — see the Secret handling section below.
• Every query is recorded via the audit logger — see Audit logging below. In a regulated environment, route that logger to a durable file or SIEM.

Setup

ODBC Driver 18 for SQL Server must be installed on the host. Install the package, then configure the connection via environment variables (see Secret handling for all options). The recommended form keeps the password in a file, not inline:

pip install -e .

# PowerShell — connection assembled from parts, password read from a file
$env:MCP_SQL_SERVER      = "yourhost\INSTANCE"
$env:MCP_SQL_DATABASE    = "master"
$env:MCP_SQL_UID         = "mcp_readonly"
$env:MCP_SQL_PWD_FILE    = "C:\path\to\your\secret.pwd"
$env:MCP_SQL_TRUST_CERT  = "no"   # "yes" only for a self-signed/local cert

python -m mcp_sql_querystore.server

Or use integrated auth with no stored password at all (MCP_SQL_TRUSTED=yes). A full MCP_SQL_CONNECTION_STRING is also accepted for simple cases — see Secret handling.

Register it with your MCP client (e.g. Claude Desktop) as an stdio server invoking python -m mcp_sql_querystore.server; see claude_desktop_config.example.json.

Tools

All tools are read-only and take a database_name (except sweep_regressions, which can sweep all databases). Each returns JSON, or a structured error dict on failure rather than raising.

• get_regressed_queries — compares a recent window against an earlier baseline window per query and flags those worse by at least regression_threshold. A real baseline-vs-recent comparison, not a top-CPU list.
• get_query_execution_plan — returns compiled plans for a query_id with a compact JSON summary (missing indexes, warnings incl. implicit conversions, key lookups) and optional raw XML.
• analyze_parameter_sniffing — finds queries with multiple compiled plans and ranks them by the ratio of slowest to fastest plan mean duration — the classic parameter-sniffing signature.
• get_missing_index_impact — scans recent plans containing missing-index recommendations, parses the impact score from the plan XML, and aggregates duplicate recommendations across queries, ranked by impact then recurrence.
• get_wait_stats — aggregates query wait time by wait category over a window, showing why queries are slow (CPU, blocking/locks, IO, memory) rather than which. De-duplicates flushed vs in-memory rows per Microsoft guidance.
• sweep_regressions — runs regression detection across all Query Store-enabled online databases (or an explicit database_names list) and returns the worst per database, ranked. One failing database does not abort the sweep; its error is collected and reported.

Example prompts

Once the server is connected to your MCP client, you drive the tools in plain language. Name the target database in the prompt (except sweep_regressions, which can scan all of them). Replace YourDB with your database name.

Wait stats — why queries are slow

• "What are the top wait categories in YourDB over the last week?"
• "Is YourDB waiting on CPU, memory, or IO?"
• "Show me wait stats for YourDB over the last 24 hours."

Execution plans

• "Get the execution plan for query_id 10 in YourDB and summarize it."
• "Does query_id 13 in YourDB have missing index recommendations?"
• "Are there implicit conversion warnings in query 12's plan in YourDB?"

Regression analysis

• "Check YourDB for CPU regressions over the last 24 hours."
• "Which queries in YourDB regressed by more than 30%?"
• "Find duration regressions in YourDB, ignoring anything with fewer than 10 executions."

Parameter sniffing

• "Check YourDB for parameter sniffing."
• "Which queries in YourDB have unstable plans?"

Missing indexes

• "What missing indexes does YourDB need most?"
• "Show me the top 10 index recommendations for YourDB by impact."

Multi-database sweep (no database name needed)

• "Sweep all my databases for CPU regressions."
• "Which database has the worst regressions this week?"

Combined — chaining tools in one turn

• "Find the biggest CPU regression in YourDB, pull its plan, and tell me why it might have regressed."
• "YourDB feels slow — diagnose it." (wait stats → regressions → plans)
• "Full performance triage of YourDB: wait stats, top regressions, and missing indexes."

Known caveats / TODO

• Version differences. Query Store column names assume SQL Server 2019+/2022 and Azure SQL MI. Verify against 2016/2017 if you target those.
• Regression semantics. Current logic uses execution-weighted averages. You may prefer percentile-based comparison (Query Store doesn't store percentiles directly, so that needs *_stdev columns and assumptions).
• Not time-windowed: analyze_parameter_sniffing aggregates across all Query Store history; on busy databases consider adding a recent_hours filter like the other tools have.
• Remaining hardening: connection retry with backoff, and version-aware column handling for mixed 2016/2017/2019/2022 fleets.

Secret handling

The connection string is resolved in this order, so the password need not sit in plaintext config:

1. MCP_SQL_CONNECTION_STRING — the full string (simplest; back-compat).
2. MCP_SQL_CONNECTION_STRING_FILE — path to a file holding the full string (Docker/K8s secret-mount style).
3. Assembled from parts: MCP_SQL_SERVER (+ MCP_SQL_DATABASE, MCP_SQL_DRIVER, MCP_SQL_ENCRYPT, MCP_SQL_TRUST_CERT, MCP_SQL_EXTRA). Auth is either:
   • Integrated (preferred for CJIS/PCI — no password stored): MCP_SQL_TRUSTED=yes.
   • SQL auth: MCP_SQL_UID plus the password from MCP_SQL_PWD_FILE (a vault-mounted file), MCP_SQL_PWD_ENV (name of another env var), or MCP_SQL_PWD (direct; least preferred).

Timeouts: MCP_SQL_CONNECT_TIMEOUT (default 10s) and MCP_SQL_QUERY_TIMEOUT (default 30s, 0 disables).

Audit logging

Every query attempt is logged via the mcp_sql_querystore.audit logger: tool, database, a 12-char hash of the SQL (not the text), row count, elapsed ms, and outcome. Connection strings, SQL text, and parameter values are never logged. Configure a handler for that logger to route the audit trail to a file or SIEM.

Testing

Unit tests (no database, safe in CI):

pip install -e ".[test]"
pytest

Integration tests (real instance, opt-in):

# set a working connection (any form above), then:
$env:MCP_SQL_TEST_DATABASE = "RAG"
$env:MCP_SQL_RUN_INTEGRATION = "1"
pytest tests/test_integration.py -v