mcp-sql-querystore
A read-only MCP server that exposes six diagnostic tools for SQL Server Query Store, letting an LLM agent investigate query performance, wait times, and execution plans without any write access.
Find regressions (
get_regressed_queries) — compare a recent window against an earlier baseline per query and flag those worse by a chosen threshold, using cpu_time, duration, or logical_reads.Inspect execution plans (
get_query_execution_plan) — pull compiled plans for aquery_idwith a compact JSON summary (missing indexes, warnings like implicit conversions, key lookups), optionally with raw XML.Detect parameter sniffing (
analyze_parameter_sniffing) — surface queries with multiple compiled plans, ranked by slowest-to-fastest plan mean duration ratio.Prioritize missing indexes (
get_missing_index_impact) — scan recent plans, parse the optimizer's impact scores, and aggregate duplicate recommendations across queries.Diagnose why queries are slow (
get_wait_stats) — aggregate wait time by category (CPU, blocking/locks, IO, memory) over a time window.Triage a whole instance (
sweep_regressions) — run regression detection across all Query Store-enabled online databases and report the worst per database, tolerating individual database failures.Chain tools in one turn — e.g. full triage combining wait stats, top regressions, and missing indexes in a single prompt, with all results returned as JSON (or structured error dicts rather than exceptions).
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@mcp-sql-querystore find regressed queries in the last 24 hours in SalesDB"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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
Provision a read-only login. Run
provisioning/create_readonly_login.sqlagainst your instance (edit names first). This login's permissions are the read-only guarantee — see the security model below.Install.
pip install -e .in a virtual environment. ODBC Driver 18 for SQL Server must be installed on the host.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.Configure your MCP client. Copy the
sql-querystoreblock fromclaude_desktop_config.example.jsoninto your real Claude Desktop config (Windows:%APPDATA%\Claude\claude_desktop_config.json), then replace the placeholder paths, server name, andMCP_SQL_PWD_FILE. SetMCP_SQL_TRUST_CERT=yesonly for a self-signed/local cert; leave itnoagainst instances with proper certificates.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.
Related MCP server: DB Analyzer MCP Server
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(andVIEW SERVER STATEonly if you use server-scoped DMVs) and nothing else — nodb_datareader, noSELECTon user tables. Seeprovisioning/create_readonly_login.sql.The keyword screen in
db.pyand the fixed SELECT-only query text are defense-in-depth, not the primary control.ApplicationIntent=ReadOnlyin 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_STRINGenv 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.serverOr 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_idwith 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_nameslist) 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
*_stdevcolumns and assumptions).Not time-windowed:
analyze_parameter_sniffingaggregates across all Query Store history; on busy databases consider adding arecent_hoursfilter 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:
MCP_SQL_CONNECTION_STRING— the full string (simplest; back-compat).MCP_SQL_CONNECTION_STRING_FILE— path to a file holding the full string (Docker/K8s secret-mount style).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_UIDplus the password fromMCP_SQL_PWD_FILE(a vault-mounted file),MCP_SQL_PWD_ENV(name of another env var), orMCP_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]"
pytestIntegration 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
Available Tools
6 toolsanalyze_parameter_sniffingB
Detect queries whose runtime varies widely across multiple compiled plans — the classic parameter-sniffing signature. Ranks by the ratio of slowest to fastest plan mean duration.
Inputs: database_name (required), top_n, min_plan_count.
get_missing_index_impactA
Aggregate missing-index recommendations found in Query Store plans, ranked by the optimizer's estimated impact score. Groups duplicate recommendations across queries.
Inputs: database_name (required), top_n, plan_scan_limit.
get_query_execution_planB
Fetch execution plan(s) for a Query Store query_id and return a compact summary (missing indexes, warnings, lookups) plus optional raw XML.
Inputs: query_id (required), database_name (required), include_xml.
get_regressed_queriesB
Detect queries whose performance regressed by comparing a recent period against an earlier baseline period from Query Store.
Inputs: database_name (required), top_n, metric, recent_hours, +3 more.
get_wait_statsA
Aggregate query wait time by wait category over a time window — shows WHY queries are slow (CPU, blocking/locks, IO, memory, etc.) rather than which are slow. Ranked by total wait time.
Inputs: database_name (required), top_n, recent_hours.
sweep_regressionsA
Run regression detection across ALL online databases that have Query Store enabled, and return the worst regressions found per database. Use this to triage a whole instance instead of one database at a time.
Inputs: metric, recent_hours, top_n_per_db, baseline_hours, +3 more.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 6 tools
Each tool targets a clearly distinct diagnostic angle: regression detection, execution plans, parameter sniffing, missing-index impact, wait stats, and an instance-wide sweep. The only near-overlap (get_regressed_queries vs sweep_regressions) is explicitly resolved by the descriptions distinguishing single-database vs all-database scope.
All six names follow a consistent snake_case verb_noun pattern (get_*, analyze_*, sweep_*). The verb variation is meaningful rather than chaotic, and the object of each name clearly signals its purpose.
Six tools is well within the ideal range and each one occupies a distinct slot in the Query Store diagnostic workflow. No tool feels redundant or filler.
The surface covers the core Query Store diagnostics lifecycle: regression detection, plan inspection, parameter sniffing, missing indexes, wait stats, and instance-wide triage. Minor gaps remain (e.g., retrieving query text or top-resource queries) but core agent workflows are fully supported.
Maintenance
Related MCP Connectors
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Database evidence for AI agents: plans, workload, topology. SQL Server, Postgres, MySQL, Cassandra.
SQL validation, formatting, parsing, metadata extraction, linting, and security analysis.
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
Related MCP Servers
- AlicenseAqualityDmaintenanceProvides read-only SQL Server health and diagnostic tools using DMVs, enabling users to query server health, active queries with blocking, and missing indexes through natural language.31MIT
- FlicenseCqualityDmaintenanceEnables AI assistants to analyze and query SQL Server databases, including schema discovery, health checks, and data retrieval.10-
- FlicenseNot gradedqualityDmaintenanceEnables AI agents to inspect schemas, analyze performance, check security, and troubleshoot SQL Server 2019+ databases through a safe, controlled interface.-
- AlicenseAqualityDmaintenanceEnables AI agents to interact with SQL Server databases for debugging and data issue resolution, providing tools for query execution, schema inspection, and diagnostics.13MIT