# Sqldb Operations Cli

> >

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

## Install

```sh
agentstack add skill-alifadl009-microsoft-fabric-claude-skill-sqldb-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 Database in Fabric — Operations & Diagnostics CLI Skill

Deep performance diagnostics for **SQL database in Fabric** via **`sqlcmd`** against Query Store, DMVs, `sys.dm_db_resource_stats`, and Extended Events. All analytical queries are read-only; the optional XE session creation is dropped at the end of the investigation.

## Prerequisites

- Tools and auth: see [COMMON-CLI.md § Authentication Recipes](../../common/COMMON-CLI.md#authentication-recipes) and [§ SQL / TDS Data-Plane Access](../../common/COMMON-CLI.md#sql--tds-data-plane-access).
- Permissions: `VIEW DATABASE STATE` for DMVs; `ALTER ANY EVENT SESSION` to create XE sessions.
- Connect to the **SQL database (OLTP) endpoint** — the SQL analytics endpoint has no Query Store/DMVs.

## Table of Contents

| Topic | Reference |
|---|---|
| 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) — **read first** |
| SQL / TDS Data-Plane Access (sqlcmd, auth) | [COMMON-CLI.md § SQL / TDS Data-Plane Access](../../common/COMMON-CLI.md#sql--tds-data-plane-access) |
| CLI Gotchas (audience, escaping, expiry) | [COMMON-CLI.md § Gotchas & Troubleshooting](../../common/COMMON-CLI.md#gotchas--troubleshooting-cli-specific) |
| Endpoint Selection (use OLTP) | [SQLDB-CONSUMPTION-CORE.md § Endpoint Selection](../../common/SQLDB-CONSUMPTION-CORE.md#endpoint-selection) |
| Performance and Monitoring foundations | [SQLDB-CONSUMPTION-CORE.md § Performance and Monitoring](../../common/SQLDB-CONSUMPTION-CORE.md#performance-and-monitoring) |
| Limitations Reference (unsupported features, DMVs) | [SQLDB-AUTHORING-CORE.md § Limitations Reference](../../common/SQLDB-AUTHORING-CORE.md#limitations-reference) |
| Mirroring Considerations | [SQLDB-AUTHORING-CORE.md § Mirroring Considerations](../../common/SQLDB-AUTHORING-CORE.md#mirroring-considerations) |
| **All diagnostic T-SQL** | [references/query-reference.md](references/query-reference.md) |
| **Investigation Workflows** | [SKILL.md § Investigation Workflows](#investigation-workflows) |
| **Examples** | [references/examples.md](references/examples.md) |

For Fabric topology, capacity, and platform auth basics see [COMMON-CORE.md](../../common/COMMON-CORE.md).

---

## Connection

Diagnostics run against the **SQL database (OLTP) endpoint**. For endpoint discovery, authentication, and `sqlcmd` connection guidance, use the shared CLI instructions in [COMMON-CLI.md](../../common/COMMON-CLI.md) rather than inline setup here.

Once connected, use the diagnostic workflows below and the full T-SQL catalog in [query-reference.md](references/query-reference.md).

---

## Diagnostic Areas

All SQL is in [query-reference.md](references/query-reference.md). Step-by-step orchestration in [Investigation Workflows](#investigation-workflows) below.

### Performance Investigation
- **Volatile Query Detection** ([SQL](references/query-reference.md#volatile-query-detection-coefficient-of-variation)) — CV% > 100 = blocking, plan regression, or parameter sniffing. **First** step for intermittent slowness.
- **Wait Category Analysis** ([SQL](references/query-reference.md#wait-category-analysis)) — Lock vs CPU vs IO vs Memory; follow Root Cause Decision Tree.
- **Top Resource Consumers** ([SQL](references/query-reference.md#top-resource-consuming-queries--by-duration-last-hour)) — by duration / IO / CPU.
- **Recently Regressed Queries** ([SQL](references/query-reference.md#recently-regressed-queries)) — last hour vs prior 24h.
- **Multi-Plan Queries** ([SQL](references/query-reference.md#multi-plan-queries-plan-instability)) — `sys.sp_query_store_force_plan` is supported but use sparingly; auto-tuning may correct over time.

### Pressure Diagnostics
- **CPU Pressure** ([SQL](references/query-reference.md#cpu-pressure-investigation)) — `avg_cpu_percent ≥ 80` over 10 min = sustained; `non_cpu_to_cpu_ratio > 5` = waiting on resources, not CPU-bound.
- **IO Pressure** ([SQL](references/query-reference.md#io-pressure-investigation)) — `avg_data_io_percent ≥ 80` (data); `avg_log_write_percent ≥ 80` (log, often un-batched DML).
- **Resource Trend** ([SQL](references/query-reference.md#resource-usage-overview)) — `sys.dm_db_resource_stats` retains only **1 hour** of 15-second samples; persist or use Query Store for longer windows.

### Blocking Diagnostics
> Optimized locking is on by default — no traditional lock escalation. Most blocking comes from long-running transactions, app-side held transactions, or hot-row contention.
- **Live Blocking** ([SQL](references/query-reference.md#live-blocking)) — blocked sessions, head blocker, chain. If head blocker is **idle with `open_transaction_count > 0`** → application is holding an open transaction; fix client code.
- **Intermittent Blocking** ([SQL](references/query-reference.md#blocking--setup-extended-events-session)) — XE session create / read / clean-up. **`ON DATABASE` only** (not `ON SERVER`); use `ring_buffer` target. Always clean up.

### Index and Statistics Health
- **Auto-Tuning** ([SQL](references/query-reference.md#auto-tuning-recommendations-check-first)) — **always check first**; engine auto-creates/drops indexes.
- **DMV Missing Index** ([SQL](references/query-reference.md#dmv-missing-index-recommendations)) — only if auto-tuning has nothing pending. Rank by `index_advantage = user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)`. DMV stats reset on restart.
- **Statistics Staleness** ([SQL](references/query-reference.md#statistics-staleness-check)) — defaults: `≥ 100,000 rows` and `≥ 10%` modification.
- **Table Access Patterns** ([SQL](references/query-reference.md#table-access-patterns)) — hot tables for indexing/denormalization candidates.

---

## Investigation Workflows

Step-by-step orchestration. Each step links to the corresponding query in [query-reference.md](references/query-reference.md).

### Workflow 1: "Why is my SQL database in Fabric slow?"
1. [Resource Usage Overview](references/query-reference.md#resource-usage-overview) (last 30 min) — confirm pressure.
2. If sustained CPU/IO pressure → [CPU Pressure](references/query-reference.md#cpu-pressure-investigation) or [IO Pressure](references/query-reference.md#io-pressure-investigation).
3. [Volatile Query Detection](references/query-reference.md#volatile-query-detection-coefficient-of-variation).
4. [Wait Category Analysis](references/query-reference.md#wait-category-analysis); follow the Root Cause Decision Tree.
5. If Lock-dominant → Workflow 3 (Blocking). If CPU/IO-dominant → [Top Resource Consumers](references/query-reference.md#top-resource-consuming-queries--by-duration-last-hour) and [Multi-Plan Queries](references/query-reference.md#multi-plan-queries-plan-instability).

### Workflow 2: "Has performance degraded recently?"
1. [Recently Regressed Queries](references/query-reference.md#recently-regressed-queries) (1h vs 24h).
2. For each regressed query → [Multi-Plan Queries](references/query-reference.md#multi-plan-queries-plan-instability) to detect plan changes.
3. [Resource Usage Overview](references/query-reference.md#resource-usage-overview) to check for new pressure.
4. [Auto-Tuning Recommendations](references/query-reference.md#auto-tuning-recommendations-check-first) — recent recommendations may indicate workload shift.

### Workflow 3: "Diagnose blocking"
1. [Live Blocking](references/query-reference.md#live-blocking) (Blocked Sessions, Head Blocker, Chain).
2. If blocking found → inspect head blocker's `open_transaction_count`, SQL text, `program_name`.
3. If head blocker is **idle with `open_transaction_count > 0`** → application bug; fix client code (uncommitted transaction).
4. If intermittent (no live rows) → [Setup XE Session](references/query-reference.md#blocking--setup-extended-events-session), wait, then [Read XE Data](references/query-reference.md#read-xe-session-data) and [Clean Up](references/query-reference.md#clean-up-xe-session).
5. Resolution patterns: reduce transaction scope; use RCSI for hot-row contention; check for missing indexes causing scans.

### Workflow 4: "Should I add an index?"
1. [Auto-Tuning Recommendations](references/query-reference.md#auto-tuning-recommendations-check-first) **first**.
2. If nothing pending → [DMV Missing Index Recommendations](references/query-reference.md#dmv-missing-index-recommendations).
3. For a specific table → [Missing Indexes for a Specific Table](references/query-reference.md#missing-indexes-for-a-specific-table).
4. [Statistics Staleness Check](references/query-reference.md#statistics-staleness-check) — stale stats can produce false "missing index" symptoms.

### Workflow 5: "Resource consumption baseline"
1. [Resource Usage Overview](references/query-reference.md#resource-usage-overview) (last 30 min).
2. [Top Resource Consumers](references/query-reference.md#top-resource-consuming-queries--by-duration-last-hour) by CPU (last 24h).
3. [Table Access Patterns](references/query-reference.md#table-access-patterns) — identify hot tables.

---

## Fabric SQL DB Constraints (NEVER recommend)

Full list of unsupported features: [SQLDB-AUTHORING-CORE.md § Limitations Reference](../../common/SQLDB-AUTHORING-CORE.md#limitations-reference). Operations-critical items:

| Do NOT Recommend | Why | Recommend Instead |
|---|---|---|
| Server-scoped DMVs (`sys.dm_os_*`, `sys.configurations`) | Not exposed | `sys.dm_db_resource_stats`, Query Store views |
| `EXECUTE AS` for security testing | Not supported | Connect as the actual user identity |
| `CREATE EVENT SESSION ... ON SERVER`, file-target XE | Database-scoped only | `... ON DATABASE` with `ring_buffer` target |
| Trace flags / `DBCC TRACEON` | Not supported | Re-architect query or use Query Store hints |
| Manual lock escalation tuning | Optimized locking eliminates escalation | Address root cause (long transactions, hot rows) |
| SQL analytics endpoint for diagnostics | DMVs/Query Store don't exist there | Connect to the SQL database (OLTP) endpoint |
| Aggressive `sp_query_store_force_plan` | Masks root cause | Fix stats / parameter sniffing first; force only as a stop-gap |

---

## Best Practices

For consumption foundations see [SQLDB-CONSUMPTION-CORE.md § Performance and Monitoring](../../common/SQLDB-CONSUMPTION-CORE.md#performance-and-monitoring).

- **Volatile detection first** — narrows scope quickly for intermittent slowness.
- **Use the OLTP endpoint** — the analytics endpoint has no Query Store/DMVs.
- **Trust auto-tuning** — only override after a recommendation has been pending for a representative period.
- **Always clean up XE sessions** at the end of an investigation.
- **Adjust `DATEADD` lookback windows** to the user's investigation scope.
- **Persist `sys.dm_db_resource_stats`** if you need > 1 hour of history.
- **High CV% over time** → structural fix needed (RCSI, parameterization, index strategy), not plan forcing.

---

## Gotchas, Rules, Troubleshooting

CLI/auth issues: [COMMON-CLI.md § Gotchas](../../common/COMMON-CLI.md#gotchas--troubleshooting-cli-specific). Platform issues: [SQLDB-CONSUMPTION-CORE.md § Gotchas](../../common/SQLDB-CONSUMPTION-CORE.md#gotchas-and-troubleshooting-reference).

### MUST DO
- Check [Constraints](#fabric-sql-db-constraints-never-recommend) before recommending optimizations.
- Connect to the **SQL database (OLTP) endpoint** — never analytics.
- Run **volatile detection first** for intermittent slowness.
- Check **auto-tuning** before suggesting manual indexes.
- **Clean up XE sessions** when finished.
- Report actual query output — do not fabricate.

### PREFER
- Start with high-level signals (resource trend, volatile detection) before drilling into individual queries.
- Use the **wait category** decision tree to choose between blocking, CPU, IO, or memory paths.
- Combine queries via the [Investigation Workflows](#investigation-workflows) for end-to-end investigations.
- Use `-i file.sql` for the XE session creation block (here-doc has portability quirks).
- Use the SQL Database **Performance Dashboard** (Fabric portal) for visual context alongside CLI queries.
- Set `SET NOCOUNT ON;` at the top of multi-statement scripts to keep CSV output clean.

### AVOID
- Recommending Fabric-unsupported features (CDC, Always Encrypted, in-memory, ledger, server-scoped DMVs, file-target XE).
- Running diagnostics on the **SQL analytics endpoint**.
- Manually creating indexes without checking auto-tuning first.
- Leaving XE sessions running after an investigation.
- Forcing plans via `sp_query_store_force_plan` instead of fixing root cause.
- Recommending lock-escalation tuning (optimized locking eliminates escalation).

### Troubleshooting

| Symptom | Cause | Fix |
|---|---|---|
| `Invalid object name 'sys.query_store_*'` | Querying analytics endpoint | Connect to OLTP endpoint |
| Volatile detection returns no rows | Lookback too short / no recent activity | Expand `DATEADD(MINUTE, -60, ...)` to `-1440` |
| `sys.dm_db_resource_stats` empty | Lookback exceeds 1-hour retention | Reduce window; or use Query Store |
| Permission error on DMVs | Missing `VIEW DATABASE STATE` | `GRANT VIEW DATABASE STATE TO [user@tenant.com]` |
| Permission error on `CREATE EVENT SESSION` | Missing `ALTER ANY EVENT SESSION` | `GRANT ALTER ANY EVENT SESSION TO [user@tenant.com]` |
| XE session captures nothing | LIKE filter too narrow / session in `STOP` state | Check `sys.dm_xe_database_sessions.state`; widen filter |
| Multi-plan query has no obvious bad plan | Parameter sniffing | `OPTION (RECOMPILE)` or `OPTIMIZE FOR` hint |

---

## Examples

See [references/examples.md](references/examples.md) for full prompt/response patterns covering:
- **Diagnose intermittent slowness** — volatile query detection → wait analysis
- **Diagnose live blocking** — head blocker with idle open transaction
- **Recommend an index** — auto-tuning check → DMV ranking → DDL suggestion

## Source & license

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

- **Author:** [alifadl009](https://github.com/alifadl009)
- **Source:** [alifadl009/microsoft-fabric-claude-skill](https://github.com/alifadl009/microsoft-fabric-claude-skill)
- **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-alifadl009-microsoft-fabric-claude-skill-sqldb-operations-cli
- Seller: https://agentstack.voostack.com/s/alifadl009
- 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%.