# Sqldw Operations Cli

> >

- **Type:** Skill
- **Install:** `agentstack add skill-microsoft-skills-for-fabric-sqldw-operations-cli`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [microsoft](https://agentstack.voostack.com/s/microsoft)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [microsoft](https://github.com/microsoft)
- **Source:** https://github.com/microsoft/skills-for-fabric/tree/main/plugins/fabric-operations/skills/sqldw-operations-cli

## Install

```sh
agentstack add skill-microsoft-skills-for-fabric-sqldw-operations-cli
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

> **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 **`sqlcmd`** and the built-in **`queryinsights`** views. All queries are read-only.

## Prerequisites

For tool installation and authentication setup, see [COMMON-CLI.md § Authentication Recipes](../../common/COMMON-CLI.md#authentication-recipes) and [COMMON-CLI.md § SQL / TDS Data-Plane Access](../../common/COMMON-CLI.md#sql--tds-data-plane-access).

**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](../../common/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](../../common/COMMON-CORE.md#fabric-topology--key-concepts) ||
| Environment URLs | [COMMON-CORE.md § Environment URLs](../../common/COMMON-CORE.md#environment-urls) ||
| Authentication & Token Acquisition | [COMMON-CORE.md § Authentication & Token Acquisition](../../common/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](../../common/COMMON-CORE.md#core-control-plane-rest-apis) | Includes pagination, LRO polling, and rate-limiting patterns |
| Capacity Management | [COMMON-CORE.md § Capacity Management](../../common/COMMON-CORE.md#capacity-management) ||
| Gotchas, Best Practices & Troubleshooting (Platform) | [COMMON-CORE.md § Gotchas, Best Practices & Troubleshooting](../../common/COMMON-CORE.md#gotchas-best-practices--troubleshooting) ||
| Tool Selection Rationale | [COMMON-CLI.md § Tool Selection Rationale](../../common/COMMON-CLI.md#tool-selection-rationale) ||
| Authentication Recipes | [COMMON-CLI.md § Authentication Recipes](../../common/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](../../common/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](../../common/COMMON-CLI.md#sql--tds-data-plane-access) | `sqlcmd` (Go) connect, query, CSV export |
| Gotchas & Troubleshooting (CLI-Specific) | [COMMON-CLI.md § Gotchas & Troubleshooting (CLI-Specific)](../../common/COMMON-CLI.md#gotchas--troubleshooting-cli-specific) | `az rest` audience, shell escaping, token expiry |
| Quick Reference | [COMMON-CLI.md § Quick Reference](../../common/COMMON-CLI.md#quick-reference) | `az rest` template + token audience/tool matrix |
| Connection Fundamentals | [SQLDW-CONSUMPTION-CORE.md § Connection Fundamentals](../../common/SQLDW-CONSUMPTION-CORE.md#connection-fundamentals) | TDS, port 1433, Entra-only, no MARS |
| Monitoring and Diagnostics | [SQLDW-CONSUMPTION-CORE.md § Monitoring and Diagnostics](../../common/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](../../common/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](../../common/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)](../../common/SQLDW-AUTHORING-CORE.md#data-ingestion-dw-only) | COPY INTO, OPENROWSET, method comparison |
| Query Reference | [query-reference.md](references/query-reference.md) | T-SQL queries, parameters, and example output for all analyses |
| Composite Recipes | [COMMON-CLI.md § Composite Recipes](../../common/COMMON-CLI.md#composite-recipes) ||
| Item-Type Capability Matrix | [SQLDW-CONSUMPTION-CORE.md § Item-Type Capability Matrix](../../common/SQLDW-CONSUMPTION-CORE.md#item-type-capability-matrix) | Warehouses only — `queryinsights` not available on SQLEP |
| Prerequisites | [SKILL.md § Prerequisites](#prerequisites) | Tools, auth, workspace role |
| Tool Stack | [SKILL.md § Tool Stack](#tool-stack) ||
| Connection | [SKILL.md § Connection](#connection) ||
| Performance Analysis | [SKILL.md § Performance Analysis](#performance-analysis) | Long-running queries, resource consumers, user insights, baselines |
| Deep Diagnostics | [SKILL.md § Deep Diagnostics](#deep-diagnostics) | Pressure windows, cache warmth, cluster keys |
| Fabric DW Constraints | [SKILL.md § Fabric DW Constraints](#fabric-dw-constraints) | **NEVER recommend unsupported features** |
| Best Practices | [SKILL.md § Best Practices](#best-practices) | Monitoring-specific guidance |
| Agentic Workflows | [SKILL.md § Agentic Workflows](#agentic-workflows) | Common investigation patterns |
| Gotchas, Rules, Troubleshooting | [SKILL.md § Gotchas, Rules, Troubleshooting](#gotchas-rules-troubleshooting) | **MUST DO / AVOID / PREFER** checklists |
| Examples | [SKILL.md § Examples](#examples) | Prompt/response pairs |

---

## Tool Stack

For installation and setup, see [Prerequisites](#prerequisites).

| Tool | Role |
|---|---|
| `sqlcmd` (Go) | Execute monitoring T-SQL queries via Entra ID auth (`-G`) |
| `az` CLI | Token acquisition, Fabric REST for endpoint discovery |
| `jq` | Parse JSON from `az rest` |

---

## Connection

For authentication recipes (interactive, service principal, CI/CD), see [COMMON-CLI.md § Authentication Recipes](../../common/COMMON-CLI.md#authentication-recipes).

### Discover the SQL Endpoint FQDN

Per [COMMON-CLI.md](../../common/COMMON-CLI.md) Discovering Connection Parameters via REST:

```bash
WS_ID=""
ITEM_ID=""

# Warehouse
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/warehouses/$ITEM_ID" \
  --query "properties.connectionString" --output tsv
```

Result: `.datawarehouse.fabric.microsoft.com`

### Connect with sqlcmd (Go)

```bash
sqlcmd -S ".datawarehouse.fabric.microsoft.com" -d "" -G \
  -Q "SELECT TOP 5 * FROM queryinsights.exec_requests_history ORDER BY total_elapsed_time_ms DESC"
```

### Reusable Connection Variables

```bash
FABRIC_SERVER=".datawarehouse.fabric.microsoft.com"
FABRIC_DB=""
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"
$SQLCMD -Q "SELECT TOP 5 * FROM queryinsights.long_running_queries ORDER BY last_run_total_elapsed_time_ms DESC"
```

```powershell
# PowerShell
$s = ".datawarehouse.fabric.microsoft.com"; $db = ""
sqlcmd -S $s -d $db -G -Q "SELECT TOP 5 * FROM queryinsights.exec_requests_history ORDER BY total_elapsed_time_ms DESC"
```

---

## Performance Analysis

All SQL queries, parameters, return fields, and response formatting are in [query-reference.md](references/query-reference.md).

### Long-Running Queries Summary

Find the slowest queries from `queryinsights.long_running_queries`. See [query-reference.md § Long-Running Queries Summary](references/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](references/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](https://learn.microsoft.com/fabric/data-warehouse/guidelines-warehouse-performance)

### Top Users Insights

Analyze user activity and query patterns. See [query-reference.md § Top Users Insights](references/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](references/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](references/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](references/query-reference.md#search-query-patterns) for SQL.

---

## Deep Diagnostics

All SQL queries for diagnostics are in [query-reference.md](references/query-reference.md).

### Analyze Long-Running Query Plans

See [query-reference.md § Long-Running Query Analysis](references/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](references/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](references/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](references/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](references/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?"

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](../../common/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](../../common/SQLDW-CONSUMPTION-CORE.md#performance-best-practices-and-troubleshooting) and the [Fabric guidelines](https://learn.microsoft.com/fabric/data-warehouse/guidelines-warehouse-performance).

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

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [microsoft](https://github.com/microsoft)
- **Source:** [microsoft/skills-for-fabric](https://github.com/microsoft/skills-for-fabric)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-microsoft-skills-for-fabric-sqldw-operations-cli
- Seller: https://agentstack.voostack.com/s/microsoft
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.