warehouse-performance

Warehouse-Wide Performance Investigation & Tuning

Investigates the performance of an entire Fabric Data Warehouse using the fabric-dw CLI and MCP tools, then proposes (and optionally applies) tuning actions. This is the warehouse-wide counterpart to query-optimizer, which diagnoses a single query.

Inputs

Gather these from the user (via $ARGUMENTS or natural language) before starting:

• workspace: workspace name or GUID
• warehouse: warehouse name or GUID. May be a Fabric Data Warehouse (DWH) or a SQL Analytics Endpoint; some steps are DWH-only (noted per step)
• window (optional): a time range for the query-hotspot views (--since / --until, ISO-8601)

The CLI binary is fdw (also installed as fabric-dw). All MCP tool names below are exposed by the fabric-dw MCP server.

Read-only steps (1–4 reads) are safe to run unprompted. Every mutating action (step 3 toggle, step 4 create/update/delete/enable/disable) is gated behind explicit user confirmation; see Guardrails.

When NOT to use this skill

If the user wants to diagnose or rewrite ONE specific query (execution plan, statistics on that query's tables, or clustering), hand off to /query-optimizer. This skill stops at warehouse-wide hotspots and emits each query's query_hash so the user can feed a specific query to query-optimizer.

Workflow

Step 1 - Find query hotspots (DWH + SQL Analytics Endpoint)

Identify the warehouse's most expensive and most frequent workloads. These views read Query Insights and work on both DWH and SQL Analytics Endpoints.

fdw queries long-running <workspace>/<warehouse> --limit 20   # server-ordered by median elapsed time DESC
fdw queries frequent     <workspace>/<warehouse> --limit 20   # server-ordered by run count DESC
fdw sql-pools insights    <workspace>/<warehouse>             # resource-pressure events (beta/preview, see step 4)

MCP equivalents: list_long_running_queries, list_frequent_queries, list_sql_pool_insights.

• There is no --order-by flag: each view is ordered server-side (long-running by median total elapsed time DESC; frequent by number of runs DESC). "Top N" is simply --limit N on the already-sorted view. --limit defaults to 100 and is capped at 10000.
• --since / --until (ISO-8601) optionally bound the time window.
• Each row carries a query_hash; record it for the synthesis report so the user can hand a specific query to /query-optimizer.

Optional drill-down once a suspect appears:

fdw queries history  <workspace>/<warehouse> --limit 50   # individual executions, server-ordered by submit time DESC
fdw queries sessions <workspace>/<warehouse> --limit 50   # session history, server-ordered by session start time DESC

MCP equivalents: list_request_history, list_session_history.

Graceful degradation: if a Query Insights view returns empty, is unavailable, or fails with permission denied, note it (Query Insights needs Contributor or higher on the workspace) and continue with the remaining steps. Do not abort the whole investigation.

Step 2 - Audit statistics health (read both; DDL is DWH-only)

Missing or stale statistics are a common cause of poor plan choices across many queries. Reading statistics works on both DWH and SQL Analytics Endpoints; only the DDL fixes (create/update/delete) require a DWH.

fdw statistics list <workspace>/<warehouse>                                       # enumerate statistics objects
fdw statistics show <workspace>/<warehouse> <schema.table> <stat_name> --histogram # header + histogram steps

MCP equivalents: list_statistics, show_statistics.

• Flag columns that lack statistics, and statistics whose last-updated date / sample rate suggests staleness. This is a heuristic; flag it, never assert that a statistic is definitely stale.
• Fabric supports single-column statistics only. Defer the actual create_statistics / update_statistics (DWH-only DDL) to query-optimizer for a specific query, or surface them as recommendations here for the user to confirm (see Guardrails).

Step 3 - Check result-set caching (read both; toggle DWH-only)

Result-set caching can cut elapsed time for repeated identical queries.

fdw settings show <workspace>/<warehouse>   # reads current settings (DWH and SQL Analytics Endpoint)

MCP equivalent: get_warehouse_settings.

If caching is off and the frequent view (step 1) shows repeated identical queries, recommend enabling it. Toggling is mutating, DWH-only, and must be confirmed first (see Guardrails):

fdw settings result-set-caching <workspace>/<warehouse> on    # ALTER DATABASE ... SET RESULT_SET_CACHING ON

MCP equivalent: set_result_set_caching. SQL Analytics Endpoints are rejected with an error for this toggle.

Do not confuse three distinct things:

1. Result-set caching: settings result-set-caching / set_result_set_caching (this step).
2. The local name→UUID lookup cache: fdw cache clear / clear_cache. This only erases cached workspace/item name-to-UUID mappings on the client; it has nothing to do with query result caching. Never suggest cache clear to influence query performance.
3. Cache cooldown: see the observe-only gap below.

Step 4 - Review and tune SQL pool configuration (workspace-scoped, beta/preview, admin)

SQL Pools is a beta / preview feature. It is workspace-scoped (configuration applies to the workspace, not a single warehouse) and requires the workspace admin role. The underlying API may change before general availability. State this to the user once, up front.

Read the current configuration first:

fdw sql-pools status <workspace>                # whether custom SQL pools are enabled (enabled/disabled)
fdw sql-pools list   <workspace>                # custom pools, or the default 50/50 baseline if none exist
fdw sql-pools show   <workspace> --name <pool>  # one pool's detail

MCP equivalents: get_sql_pools_status, list_sql_pools, get_sql_pool.

When no custom pools exist, list shows the default autonomous 50/50 split: one pool at 50% maxResourcePercentage for SELECT (read/analytics) and one at 50% for non-SELECT (DML/DDL/ETL/ingestion). Treat this as the baseline.

If step 1's sql-pools insights shows resource pressure concentrated on one workload type, the actionable levers are (all mutating; confirm first, see Guardrails):

# Carve out a read-optimized pool routed by application name (the only routing key available, see gaps below)
fdw sql-pools create <workspace> --name reads-pool --max-percent 60 --optimize-for-reads \
  --classifier-type "Application Name" --classifier-value "PowerBI" --classifier-value "dbt"

fdw sql-pools update  <workspace> --name reads-pool --max-percent 70   # adjust a lever on an existing pool
fdw sql-pools delete  <workspace> --name reads-pool                     # remove a custom pool
fdw sql-pools enable  <workspace>   # enable custom SQL pools (preserves pool config; default state with no custom pools is autonomous WLM)
fdw sql-pools disable <workspace>   # disable custom SQL pools (preserves pool config; re-enabling restores it)

MCP equivalents: create_sql_pool, update_sql_pool, delete_sql_pool, enable_sql_pools, disable_sql_pools.

Tuning levers: --max-percent (the pool's maxResourcePercentage, 1–100), --optimize-for-reads/--no-optimize-for-reads, and the application-name classifier (--classifier-type "Application Name" with one or more repeatable --classifier-value).

Step 5 - Synthesize a prioritized report

Pull the steps together into a single report the user can act on:

1. Top cost drivers: from long-running and frequent, with each query's query_hash. Point the user to /query-optimizer <workspace>/<warehouse> (plus the query text) for per-query diagnosis.
2. Resource-pressure events: what sql-pools insights surfaced, and which workload (SELECT vs non-SELECT) is under pressure.
3. Statistics health: tables/columns flagged as missing or likely-stale (heuristic).
4. Caching recommendation: whether result-set caching is on, and whether the frequent-query pattern justifies enabling it (DWH-only).
5. SQL pool tuning suggestions: concrete create/update levers vs. the 50/50 baseline (beta, workspace admin).

List every proposed mutating action explicitly and ask the user which (if any) to apply before touching anything.

Observe-only gaps (do NOT fake these)

There is no API for the following. Document them honestly; never present them as available capabilities.

• Statement-type routing: there is no API to route a SQL pool by statement type (e.g. "send all SELECTs here"). The only routing key is the application-name classifier shown in step 4. Recommend application-name classifiers; do not claim statement-type routing exists. Tracked in issue #596.
• Cache cooldown: there is no ALTER DATABASE / REST option to configure a result-set cache cooldown. Do not invent a command for it. This is distinct from the result-set caching toggle (step 3) and from the local lookup cache (cache clear). Tracked in issue #595.

Guardrails

• Reads are free; writes require explicit confirmation. Steps 1–4's read commands (queries *, sql-pools insights/get/list/show, statistics list/show, settings show) are safe to run unprompted. Every mutating action must be confirmed by the user first: settings result-set-caching (toggle), statistics DDL (create/update/delete), and sql-pools create/update/delete/enable/disable. The MCP server enforces its own write-guard (FABRIC_MCP_ALLOW_WRITES / FABRIC_MCP_ALLOW_DESTRUCTIVE), but it does not pop a user dialog. Asking is the agent's responsibility. List the exact action and ask before proceeding.
• DWH vs SQL Analytics Endpoint. queries *, statistics list/show, settings show, and the sql-pools reads work on both. The result-set-caching toggle and statistics DDL are DWH-only: SQL Analytics Endpoints are rejected with an error; surface that as a limitation rather than retrying.
• SQL Pools is beta/preview, workspace-scoped, and requires workspace admin: state this before recommending any sql-pools change, and note the API may change before GA.
• Graceful degradation. If Query Insights views are unavailable or permission-denied (needs Contributor+), note it and continue. Do not abort the investigation.
• Heuristics stay heuristics. Frame missing/stale statistics and pressure interpretations as flags to investigate, not certainties.
• Don't confuse the caches. Result-set caching, the local name→UUID lookup cache (cache clear), and cache cooldown (#595) are three different things; keep them distinct in every recommendation.
• Hand single queries to /query-optimizer. This skill diagnoses warehouse-wide; per-query plan/statistics/clustering analysis belongs to query-optimizer.