Sqldw Operations CLI

> 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

| Task | Reference | Notes |

|---|---|---|

| Finding Workspaces and Items in Fabric | COMMON-CLI.md § Finding Workspaces and Items in Fabric | Mandatory — *READ link first* [needed for finding workspace id by its name or item id by its name, item type, and workspace id] |

| Fabric Topology & Key Concepts | COMMON-CORE.md § Fabric Topology & Key Concepts ||

| Environment URLs | COMMON-CORE.md § Environment URLs ||

| Authentication & Token Acquisition | COMMON-CORE.md § Authentication & Token Acquisition | Wrong audience = 401; read before any auth issue |

| Core Control-Plane REST APIs | COMMON-CORE.md § Core Control-Plane REST APIs | Includes pagination, LRO polling, and rate-limiting patterns |

| Capacity Management | COMMON-CORE.md § Capacity Management ||

| Gotchas, Best Practices & Troubleshooting (Platform) | COMMON-CORE.md § Gotchas, Best Practices & Troubleshooting ||

| Tool Selection Rationale | COMMON-CLI.md § Tool Selection Rationale ||

| Authentication Recipes | COMMON-CLI.md § Authentication Recipes | az login flows and token acquisition |

| Fabric Control-Plane API via az rest | COMMON-CLI.md § Fabric Control-Plane API via az rest | Always pass --resource; includes pagination and LRO helpers |

| SQL / TDS Data-Plane Access | COMMON-CLI.md § SQL / TDS Data-Plane Access | Legacy sqlcmd reference (MCP is primary — see Tool Stack) |

| Gotchas & Troubleshooting (CLI-Specific) | COMMON-CLI.md § Gotchas & Troubleshooting (CLI-Specific) | az rest audience, shell escaping, token expiry |

| Quick Reference | COMMON-CLI.md § Quick Reference | az rest template + token audience/tool matrix |

| Connection Fundamentals | SQLDW-CONSUMPTION-CORE.md § Connection Fundamentals | TDS, port 1433, Entra-only, no MARS |

| Monitoring and Diagnostics | SQLDW-CONSUMPTION-CORE.md § Monitoring and Diagnostics | Query labels; DMVs (live) + queryinsights.* (30-day history) |

| Performance: Best Practices and Troubleshooting | SQLDW-CONSUMPTION-CORE.md § Performance: Best Practices and Troubleshooting | Statistics, caching, clustering, query tips |

| Gotchas and Troubleshooting (Consumption) | SQLDW-CONSUMPTION-CORE.md § Gotchas and Troubleshooting Reference | 18 numbered issues with cause + resolution |

| Data Ingestion (DW Only) | SQLDW-AUTHORING-CORE.md § Data Ingestion (DW Only) | COPY INTO, OPENROWSET, method comparison |

| Query Reference | query-reference.md | T-SQL queries, parameters, and example output for all analyses |

| Composite Recipes | COMMON-CLI.md § Composite Recipes ||

| Item-Type Capability Matrix | SQLDW-CONSUMPTION-CORE.md § Item-Type Capability Matrix | Warehouses only — queryinsights not available on SQLEP |

| Prerequisites | SKILL.md § Prerequisites | Tools, auth, workspace role |

| Tool Stack | SKILL.md § Tool Stack ||

| Connection | SKILL.md § Connection ||

| Performance Analysis | SKILL.md § Performance Analysis | Long-running queries, resource consumers, user insights, baselines |

| Deep Diagnostics | SKILL.md § Deep Diagnostics | Pressure windows, cache warmth, cluster keys |

| Fabric DW Constraints | SKILL.md § Fabric DW Constraints | NEVER recommend unsupported features |

| Best Practices | SKILL.md § Best Practices | Monitoring-specific guidance |

| Agentic Workflows | SKILL.md § Agentic Workflows | Common investigation patterns |

| Gotchas, Rules, Troubleshooting | SKILL.md § Gotchas, Rules, Troubleshooting | MUST DO / AVOID / PREFER checklists |

| Examples | SKILL.md § Examples | Prompt/response pairs |

Tool Stack

For installation and setup, see Prerequisites.

| Tool | Role |

|---|---|

| fabric-sqlendpoint-execute_query MCP tool | Primary: Execute monitoring T-SQL queries against Fabric SQL Endpoints. Auth handled by MCP protocol. |

| az CLI | Token acquisition, Fabric REST for workspace/item discovery |

| jq | Parse JSON from az rest |

> 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.

| Parameter | Type | Description |

|-----------|------|-------------|

| workspaceId | string (UUID) | The workspace GUID containing the target Warehouse |

| itemId | string (UUID) | The GUID of the Warehouse item |

| query | string | T-SQL query text (single batch — no GO separators) |

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.

| Do NOT Recommend | Why | Recommend Instead |

|------------------|-----|-------------------|

| Nonclustered indexes | Not supported | V-Order, column pruning, predicate pushdown |

| Materialized views | Not supported | Standard views or result set caching |

| Index hints (FORCESEEK/FORCESCAN) | Not supported | Simplify query structure |

| Multi-column statistics | Not supported | Single-column statistics on key columns |

| ALTER TABLE SET DATA_CLUSTERING_KEY | Not supported | CTAS with WITH (CLUSTER BY (...)) |

| RENAME OBJECT | Not supported | EXEC sp_rename 'schema.old', 'new' |

| Change isolation level | Snapshot only | Fabric uses snapshot isolation exclusively |

| CREATE USER | Not supported | Manage users via Fabric workspace |

| Triggers | Not supported | Application logic or Fabric pipelines |

| Recursive CTEs | Not supported | Iterative approach |

| "Enable Query Insights" setting | Query Insights is always on — there is no setting | If access is denied, the user needs Admin or Member workspace role |

Agentic Workflows

Workflow 1: "Why is my warehouse slow?"

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

Workflow 2: "Has performance degraded?"

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

Workflow 3: "Optimize my warehouse"

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

Workflow 4: "What are people running?"

• Recent activity → Run recent queries (top 10)
• User patterns → Run top users insights (last 24h)
• 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.

| Symptom | Cause | Fix |

|---|---|---|

| Invalid object name 'queryinsights...' | New warehouse < 2 min old | Wait ~2 minutes |

| Permission errors on queryinsights.* | Insufficient workspace role | Requires Admin or Member role |

| No data in queryinsights views | No recent query activity or < 15 min delay | Wait 15 min after query completion |

| No rows but data exists | RLS filtering | Check USER_NAME(), verify RLS policies |

| fabric-sqlendpoint-execute_query tool not available | MCP server not registered | User must add Fabric SQL Endpoint MCP server |

| HTTP 429 rate limit | Too many calls in 1 min | Wait 60s; consolidate queries |

| Query timeout (300s) | Complex diagnostics | Narrow time window with tighter DATEADD |

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:

> `sql

> CREATE TABLE dbo.FactSales_clustered

> WITH (CLUSTER BY (SaleDate, Region))

> AS SELECT * FROM dbo.FactSales;

> `