sqldb-operations-cli
Skill3 starsChanged 4 months agoMITView on GitHub
Diagnose SQL database in Fabric performance via sqlcmd against Query Store, DMVs, sys.dm_db_resource_stats, and Extended Events on the OLTP endpoint. Identifies the top resource-consuming, slowest, or most expensive queries and handles query-performance ranking, blocking-chain, missing-index, and plan-regression diagnostics. For routine data queries use the sqldb-consumption-cli skill; for schema changes use the sqldb-authoring-cli skill. Triggers: "query store slow query analysis sqldb top queries", "top resource-consuming queries from query store sqldb", "slowest queries sql database in Fabric query store", "most expensive queries sqldb query store", "sql database in Fabric blocked sessions head blocker chain sqlcmd", "sqldb missing index recommendation", "sqldb regressed plan instability sqlcmd", "sqldb extended events trace".
In this repository36 other files it gives its agents.
- AGENTS.mdAGENTS.md
- CLAUDE.mdCLAUDE.md
- activator-authoring-cliSkill
- activator-consumption-cliSkill
- check-updatesSkill
- databricks-migrationSkill
- dataflows-authoring-cliSkill
- dataflows-consumption-cliSkill
- dataflows-save-as-authoring-cliSkill
- e2e-medallion-architectureSkill
- eventhouse-authoring-cliSkill
- eventhouse-consumption-cliSkill
- eventstream-authoring-cliSkill
- eventstream-consumption-cliSkill
- fabriciq-ontology-authoring-cliSkill
- fabriciq-ontology-consumption-cliSkill
- fabriciqSkill
- hdinsight-migrationSkill
- mlv-operations-cliSkill
- pipeline-migrationSkill
- powerbi-report-authoringSkill
- powerbi-report-designSkill
- powerbi-report-managementSkill
- powerbi-report-planningSkill
- search-consumption-cliSkill
- semantic-model-authoringSkill
- semantic-model-consumptionSkill
- spark-authoring-cliSkill
- spark-consumption-cliSkill
- spark-operations-cliSkill
- sqldb-authoring-cliSkill
- sqldb-consumption-cliSkill
- sqldw-authoring-cliSkill
- sqldw-consumption-cliSkill
- sqldw-operations-cliSkill
- synapse-migrationSkill
The file
Show its rules and text
We split this skill into the rules it gives an agent
16 rules. About 0 are common, 12 are distinctive, and 4 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈12
Distinctive · estimate
This author's own choices. Worth a look.
4
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 68
All SQL is in query-reference.md.
- Distinctive: line 101
If sustained CPU/IO pressure → CPU Pressure or IO Pressure.
- Distinctive: line 104
If Lock-dominant → Workflow 3 (Blocking).
- Very specific: line 114
If blocking found → inspect head blocker's open_transaction_count, SQL text, program_name.
- Very specific: line 115
If head blocker is idle with open_transaction_count > 0 → application bug.
- Distinctive: line 116
If intermittent (no live rows) → Setup XE Session, wait, then Read XE Data and [Clean
- Distinctive: line 121
If nothing pending → DMV Missing Index Recommendations.
- Very specific: line 157
Persist
sys.dm_db_resource_stats: if you need > 1 hour of history. - Distinctive: line 167
Check Constraints before recommending optimizations.
- Distinctive: line 169
Run volatile detection first for intermittent slowness.
- Distinctive: line 170
Check auto-tuning before suggesting manual indexes.
- Distinctive: line 171
Clean up XE sessions: when finished.
- Distinctive: line 175
Start with high-level signals (resource trend, volatile detection) before drilling into individual queries.
- Distinctive: line 176
Use the wait category decision tree to choose between blocking, CPU, IO, or memory paths.
- Very specific: line 178
Use -i file.sql for the XE session creation block (here-doc has portability quirks).
- Distinctive: line 179
Use the SQL Database Performance Dashboard (Fabric portal) for visual context alongside CLI queries.
≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
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-updatesskill.- 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
- To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering
- 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 STATEfor DMVs;ALTER ANY EVENT SESSIONto 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_planis supported but use sparingly; auto-tuning may correct over time.
Pressure Diagnostics
- CPU Pressure (SQL) —
avg_cpu_percent ≥ 80over 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_statsretains 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 DATABASEonly (notON SERVER); usering_buffertarget. 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 rowsand≥ 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?"
- Resource Usage Overview (last 30 min) — confirm pressure.
- If sustained CPU/IO pressure → CPU Pressure or IO Pressure.
- Volatile Query Detection.
- Wait Category Analysis; follow the Root Cause Decision Tree.
- If Lock-dominant → Workflow 3 (Blocking). If CPU/IO-dominant → Top Resource Consumers and Multi-Plan Queries.
Workflow 2: "Has performance degraded recently?"
- Recently Regressed Queries (1h vs 24h).
- For each regressed query → Multi-Plan Queries to detect plan changes.
- Resource Usage Overview to check for new pressure.
- Auto-Tuning Recommendations — recent recommendations may indicate workload shift.
Workflow 3: "Diagnose blocking"
- Live Blocking (Blocked Sessions, Head Blocker, Chain).
- If blocking found → inspect head blocker's
open_transaction_count, SQL text,program_name. - If head blocker is idle with
open_transaction_count > 0→ application bug; fix client code (uncommitted transaction). - If intermittent (no live rows) → Setup XE Session, wait, then Read XE Data and Clean Up.
- Resolution patterns: reduce transaction scope; use RCSI for hot-row contention; check for missing indexes causing scans.
Workflow 4: "Should I add an index?"
- Auto-Tuning Recommendations first.
- If nothing pending → DMV Missing Index Recommendations.
- For a specific table → Missing Indexes for a Specific Table.
- Statistics Staleness Check — stale stats can produce false "missing index" symptoms.
Workflow 5: "Resource consumption baseline"
- Resource Usage Overview (last 30 min).
- Top Resource Consumers by CPU (last 24h).
- 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
DATEADDlookback windows to the user's investigation scope. - Persist
sys.dm_db_resource_statsif 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.sqlfor 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_planinstead 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 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
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

