sqldb-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 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 and § 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 — read first
SQL / TDS Data-Plane Access (sqlcmd, auth)	COMMON-CLI.md § SQL / TDS Data-Plane Access
CLI Gotchas (audience, escaping, expiry)	COMMON-CLI.md § Gotchas & Troubleshooting
Endpoint Selection (use OLTP)	SQLDB-CONSUMPTION-CORE.md § Endpoint Selection
Performance and Monitoring foundations	SQLDB-CONSUMPTION-CORE.md § Performance and Monitoring
Limitations Reference (unsupported features, DMVs)	SQLDB-AUTHORING-CORE.md § Limitations Reference
Mirroring Considerations	SQLDB-AUTHORING-CORE.md § Mirroring Considerations
All diagnostic T-SQL	references/query-reference.md
Investigation Workflows	SKILL.md § Investigation Workflows
Examples	references/examples.md

For Fabric topology, capacity, and platform auth basics see 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 rather than inline setup here.

Once connected, use the diagnostic workflows below and the full T-SQL catalog in query-reference.md.

---

Diagnostic Areas

All SQL is in query-reference.md. Step-by-step orchestration in Investigation Workflows below.

Performance Investigation

• Volatile Query Detection (SQL) — CV% > 100 = blocking, plan regression, or parameter sniffing. First step for intermittent slowness.
• Wait Category Analysis (SQL) — Lock vs CPU vs IO vs Memory; follow Root Cause Decision Tree.
• Top Resource Consumers (SQL) — by duration / IO / CPU.
• Recently Regressed Queries (SQL) — last hour vs prior 24h.
• Multi-Plan Queries (SQL) — sys.sp_query_store_force_plan is supported but use sparingly; auto-tuning may correct over time.

Pressure Diagnostics

• CPU Pressure (SQL) — avg_cpu_percent ≥ 80 over 10 min = sustained; non_cpu_to_cpu_ratio > 5 = waiting on resources, not CPU-bound.
• IO Pressure (SQL) — avg_data_io_percent ≥ 80 (data); avg_log_write_percent ≥ 80 (log, often un-batched DML).
• Resource Trend (SQL) — 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) — 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) — 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) — always check first; engine auto-creates/drops indexes.
• DMV Missing Index (SQL) — 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) — defaults: ≥ 100,000 rows and ≥ 10% modification.
• Table Access Patterns (SQL) — hot tables for indexing/denormalization candidates.

---

Investigation Workflows

Step-by-step orchestration. Each step links to the corresponding query in query-reference.md.

Workflow 1: "Why is my SQL database in Fabric slow?"

1. Resource Usage Overview (last 30 min) — confirm pressure.
2. If sustained CPU/IO pressure → CPU Pressure or IO Pressure.
3. Volatile Query Detection.
4. Wait Category Analysis; follow the Root Cause Decision Tree.
5. If Lock-dominant → Workflow 3 (Blocking). If CPU/IO-dominant → Top Resource Consumers and Multi-Plan Queries.

Workflow 2: "Has performance degraded recently?"

1. Recently Regressed Queries (1h vs 24h).
2. For each regressed query → Multi-Plan Queries to detect plan changes.
3. Resource Usage Overview to check for new pressure.
4. Auto-Tuning Recommendations — recent recommendations may indicate workload shift.

Workflow 3: "Diagnose blocking"

1. 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, wait, then Read XE Data and Clean Up.
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 first.
2. If nothing pending → DMV Missing Index Recommendations.
3. For a specific table → Missing Indexes for a Specific Table.
4. Statistics Staleness Check — stale stats can produce false "missing index" symptoms.

Workflow 5: "Resource consumption baseline"

1. Resource Usage Overview (last 30 min).
2. Top Resource Consumers by CPU (last 24h).
3. Table Access Patterns — identify hot tables.

---

Fabric SQL DB Constraints (NEVER recommend)

Full list of unsupported features: 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.

• 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. Platform issues: SQLDB-CONSUMPTION-CORE.md § Gotchas.

MUST DO

• Check Constraints 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 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 [[email protected]]
Permission error on CREATE EVENT SESSION	Missing ALTER ANY EVENT SESSION	GRANT ALTER ANY EVENT SESSION TO [[email protected]]
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 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