Sqldw Operations CLI

Telemetry — MANDATORY. Every api.fabric.microsoft.com call must carry x-ms-fabric-skill: sqldw-operations-cli (az rest: --headers "x-ms-fabric-skill=sqldw-operations-cli"), including every LRO poll, fabric_lro and retry. Snippets omit it — add it anyway.

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

CRITICAL NOTES

1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering
2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

SQL DW Performance & Diagnostics — CLI Skill

This skill provides performance analysis, deep diagnostics, and optimization guidance for Microsoft Fabric Data Warehouse via the fabric-sqlendpoint-execute_query MCP tool and the built-in queryinsights views. All queries are read-only.

Prerequisites

For workspace/item discovery via az rest, see COMMON-CLI.md § Fabric Control-Plane API via az rest. For legacy sqlcmd reference (fallback only), see COMMON-CLI.md § SQL / TDS Data-Plane Access.

⚠️ SQL Execution Override: For SQL data-plane execution, this skill supersedes COMMON-CLI SQL/TDS guidance. Use MCP fabric-sqlendpoint-execute_query (see Tool Stack) unless explicitly using Legacy CLI Fallback.

Monitoring-specific requirements:

• Workspace role: Admin or Member on the target workspace (required for queryinsights views)
• Warehouse must exist with recent query activity (queryinsights views retain 30 days; data appears with up to 15 min delay)

Table of Contents

Tool Stack

For installation and setup, see Prerequisites.

IMPORTANT — MCP vs sqlcmd: This skill uses the fabric-sqlendpoint-execute_query MCP tool for all T-SQL execution. Do not use COMMON-CLI SQL/TDS/sqlcmd sections for query execution.

Agent preflight — verify before first operation:

1. Confirm the fabric-sqlendpoint-execute_query tool is available in your tool list. This tool is provided by the fabric-sqlendpoint MCP server, which is registered either by installing a Fabric skills plugin (the path for end users) or via this repo's .mcp.json — other MCP clients may register it through their own configuration.
2. If no matching tool is found, the user must register the Fabric SQL Endpoint MCP server.
   • Global URL: https://api.fabric.microsoft.com/v1/mcp/dataPlane/sqlEndpoint
   • Item-scoped URL: https://api.fabric.microsoft.com/v1/mcp/dataPlane/workspaces/{workspaceId}/items/{itemId}/sqlEndpoint

MCP Tool Signature

fabric-sqlendpoint-execute_query(workspaceId, itemId, query)

Tool name may differ: execute_query is the logical operation. Depending on how the server is registered, the concrete tool name in your tool list may be prefixed (e.g. fabric-sqlendpoint-execute_query or sqlendpoint-global-execute_query). Invoke the concrete name shown in your tool list, always passing workspaceId, itemId, and query.

Returns: CSV resource (RFC 4180) with tabular results + metadata text.

Limits: 10,000 rows max | 300s timeout | 20 req/min rate limit (observed defaults, not a documented contract — the service can change them; verify against live 429/timeout/truncation responses)

Connection

Discover workspaceId and itemId

You need the workspace GUID and warehouse GUID to call fabric-sqlendpoint-execute_query:

# 1. Find workspace ID by name (capture into WS_ID for the next call)
WS_ID=$(az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces" \
  --query "value[?displayName=='MyWorkspace'].id" --output tsv)
echo "Workspace ID: $WS_ID"

# 2. Find warehouse item ID by name
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/warehouses" \
  --query "value[?displayName=='MyWarehouse'].id" --output tsv

Execute a Monitoring Query

fabric-sqlendpoint-execute_query(
  workspaceId: "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
  itemId: "11111111-2222-3333-4444-555555555555",
  query: "SELECT TOP 5 * FROM queryinsights.long_running_queries ORDER BY last_run_total_elapsed_time_ms DESC"
)

No additional connection setup needed — authentication is handled transparently by the MCP protocol.

Performance Analysis

All SQL queries, parameters, return fields, and response formatting are in query-reference.md.

Long-Running Queries Summary

Find the slowest queries from queryinsights.long_running_queries. See query-reference.md § Long-Running Queries Summary for SQL and formatting.

Top Resource Consumers

Find CPU- and storage-heavy queries from queryinsights.exec_requests_history. See query-reference.md § Top Resource Consumers for SQL, thresholds, and formatting.

Recommendation thresholds:

• Remote scans > 1,000 MB → review data layout, consider clustering
• CPU > 5,000,000 ms → review query logic
• Elapsed > 300,000 ms → check joins, filters, statistics
• Reference: Performance guidelines

Top Users Insights

Analyze user activity and query patterns. See query-reference.md § Top Users Insights for SQL and classification logic.

Compare Recent vs Baseline

Detect performance regressions by comparing recent window against historical baseline. See query-reference.md § Compare Recent vs Baseline for SQL and formatting.

Recent Queries

Retrieve the most recently executed queries. See query-reference.md § Recent Queries for SQL.

Search Query Patterns

Search historical query patterns by table name, column, or keyword. See query-reference.md § Search Query Patterns for SQL.

Deep Diagnostics

All SQL queries for diagnostics are in query-reference.md.

Analyze Long-Running Query Plans

See query-reference.md § Long-Running Query Analysis for SQL.

Analysis guidance — when reviewing slow queries, check:

• High data_scanned_remote_storage_mb → data layout issues (run OPTIMIZE, consider clustering)
• High allocated_cpu_time_ms relative to elapsed → CPU-bound (simplify joins, reduce columns)
• High elapsed but low CPU → waiting on resources (check for pressure windows)

Analyze Pressure Window Queries

Identify SQL pool pressure events using queryinsights.sql_pool_insights and correlate with the heaviest queries running during those windows. See query-reference.md § Pressure Window Analysis for the two-step SQL.

Usage: Step 1 returns pressure windows with window_start and window_end timestamps. Substitute those actual timestamp values into Step 2's WHERE clause to find overlapping queries.

Global recommendations — based on aggregate pressure analysis:

• If SELECT pool has more pressure → read-heavy workload, suggest caching and column pruning
• If NONSELECT pool has more pressure → write-heavy, suggest batching and COPY INTO
• If total pressure > 60 min → suggest scaling capacity or staggering workloads

Analyze Query Cache Warmth

See query-reference.md § Cache Warmth Analysis for SQL.

Classification logic — for each execution, compute total_mb = remote + memory + disk:

• result_cache_hit = 1 → cached
• remote_mb / total_mb > 0.8 → cold (>80% from remote storage)
• (memory_mb + disk_mb) / total_mb > 0.8 → warm (>80% from cache)

Recommendations:

• Over 50% cold runs → Enable result set caching: ALTER DATABASE SET RESULT_SET_CACHING ON;
• Always-cold patterns → Check for GETDATE()/GETUTCDATE() or volatile functions that bust the cache key

Recommend Cluster Keys

See query-reference.md § Cluster Key Recommendations for SQL.

Key rules:

• Only WHERE predicates benefit from clustering — equality JOIN ON conditions do not
• Prefer mid-to-high cardinality columns (many distinct values)
• Maximum 4 clustering columns
• Use CTAS with WITH (CLUSTER BY (...)) — ALTER TABLE is not supported

To apply clustering — see query-reference.md § Cluster Key Recommendations for CTAS creation, sp_rename table swap, and verification SQL.

Note: Fabric does not support ALTER TABLE SET DATA_CLUSTERING_KEY or RENAME OBJECT. Always use CTAS with WITH (CLUSTER BY (...)) and sp_rename for table swaps.

Fabric DW Constraints

NEVER recommend features not supported in Fabric Data Warehouse. Always consult this list before making optimization suggestions.

Agentic Workflows

Workflow 1: "Why is my warehouse slow?"

1. Check for pressure events → Run the pressure window analysis query (last 24h)
2. Find the heaviest queries → Run top resource consumers query (last 1h)
3. Analyze slow queries → Run long-running queries analysis
4. Check cache behavior → Run cache warmth analysis (last 24h)
5. Recommend clustering → Run cluster key recommendation queries

Workflow 2: "Has performance degraded?"

1. Compare against baseline → Run recent vs baseline comparison (1h vs 7-day)
2. Identify new slow queries → Run long-running queries summary (top 5)
3. Check user patterns → Run top users insights (last 24h)

Workflow 3: "Optimize my warehouse"

1. Review best practices → See SQLDW-CONSUMPTION-CORE.md § Performance: Best Practices and Troubleshooting
2. Find optimization targets → Run top resource consumers (last 24h)
3. Recommend clustering → Run cluster key recommendation queries
4. Analyze cold-start queries → Run cache warmth analysis

Workflow 4: "What are people running?"

1. Recent activity → Run recent queries (top 10)
2. User patterns → Run top users insights (last 24h)
3. Search for specific patterns → Run query pattern search with search term

Best Practices

For comprehensive Fabric DW best practices, see SQLDW-CONSUMPTION-CORE.md § Performance: Best Practices and Troubleshooting and the Fabric guidelines.

Monitoring-specific best practices:

• Start broad, then drill down — begin with long-running queries summary and baseline comparison before deep diagnostics
• Use pressure window analysis for root-cause analysis rather than guessing at bottlenecks
• Label all agent queries with OPTION (LABEL = 'AGENTCLI_MONITOR_...') for tracing in Query Insights
• Prefer mid-to-high cardinality columns for clustering keys — low cardinality columns offer limited file-skipping benefit
• Use WHERE predicates to identify cluster key candidates — equality JOIN ON conditions do not benefit from clustering
• Always verify clustering after CTAS by querying sys.index_columns.data_clustering_ordinal
• Check cold vs warm cache before concluding a query is inherently slow — first execution may be a cold start
• Adjust time windows (DATEADD parameters) to match user's investigation scope — don't default to arbitrary windows

Gotchas, Rules, Troubleshooting

For generic CLI gotchas (connection, auth, shell escaping): see COMMON-CLI.md § Gotchas & Troubleshooting. For T-SQL/platform gotchas: see SQLDW-CONSUMPTION-CORE.md § Gotchas and Troubleshooting Reference.

MUST DO

• Always check Fabric DW Constraints before recommending optimizations
• When recommending clustering, instruct users to use CTAS with WITH (CLUSTER BY (...)) — not ALTER TABLE
• Report actual query output — do not fabricate or assume results
• Label queries with OPTION (LABEL = 'AGENTCLI_MONITOR_...') for Query Insights tracing
• Verify fabric-sqlendpoint-execute_query MCP tool availability before first operation

PREFER

• Start with high-level queries (long-running summary, baseline comparison) before drilling into diagnostics
• Use the pressure window analysis for root-cause analysis rather than guessing at bottlenecks
• Combine multiple queries in the Agentic Workflows for comprehensive investigations
• Adjust time windows (DATEADD parameters) based on what the user asks for
• Consolidate related diagnostic queries into fewer calls to respect rate limits

AVOID

• Recommending Fabric-unsupported features (nonclustered indexes, materialized views, index hints, triggers)
• Suggesting that Query Insights needs to be "enabled" or "turned on" — queryinsights views are always available; permission errors indicate insufficient workspace role (Admin or Member required)
• Running monitoring queries without confirming workspaceId and itemId
• Guessing at performance root causes without running the diagnostic queries
• Using SELECT * in monitoring queries — always select specific columns
• Using GO separators or sqlcmd meta-commands in queries (MCP tool accepts single batches only)
• Unbounded queries without TOP N — 10,000 row limit applies

TROUBLESHOOTING (Monitoring-Specific)

For generic connection/auth troubleshooting, see COMMON-CLI.md § Gotchas & Troubleshooting.

Examples

Find Slow Queries

User: "What are the slowest queries in my warehouse?"

Agent: Runs the long-running queries summary via fabric-sqlendpoint-execute_query:

fabric-sqlendpoint-execute_query(workspaceId, itemId,
  "SELECT TOP 5
      left(last_run_command, 80) AS query_preview,
      number_of_runs,
      last_run_total_elapsed_time_ms,
      median_total_elapsed_time_ms
   FROM queryinsights.long_running_queries
   ORDER BY median_total_elapsed_time_ms DESC")

The top 5 slowest queries are:

1. SELECT * FROM FactSales JOIN DimCustomer... — ran 12 times, last took 45,200 ms (median 38,500 ms)
2. SELECT region, SUM(amount)... — ran 8 times, last took 32,100 ms (median 29,000 ms) ...

Diagnose Performance Degradation

User: "Is my warehouse slower than last week?"

Agent: Runs the baseline comparison query and presents:

Performance Comparison (last 1h vs 7-day baseline):

• Elapsed time: 2,450 ms avg → 4,100 ms avg (+67.3%)
• CPU consumption: 1,200 ms → 2,800 ms (+133.3%)
• Data scanned: 450 MB → 480 MB (+6.7%)

Significant regression detected. Let me check for pressure events and analyze the heaviest queries...

Then follows up with pressure window and resource consumer queries.

Get Clustering Recommendations

User: "Which tables should I cluster and on what columns?"

Agent: Runs the cluster key recommendation queries and presents:

Recommended Clustering Keys:

Table	Recommended Columns	Row Count	Total Scanned MB
dbo.FactSales	SaleDate, Region	50M	12,500 MB
dbo.FactInventory	ProductID, WarehouseID	12M	3,200 MB

To apply clustering, use CTAS:

CREATE TABLE dbo.FactSales_clustered
WITH (CLUSTER BY (SaleDate, Region))
AS SELECT * FROM dbo.FactSales;