AutoGineer
Generated banner for Sqldw CLI
Generated logo for Sqldw CLI

Sqldw CLI

MicrosoftOfficialPublished by Microsoft

  • Skill
  • Tool integration
  • Data and AI
  • MIT
  • No issues found
Download ZIP

From the author

Manage Fabric Warehouse, Lakehouse SQL endpoints, and Mirrored Databases: DDL/DML, COPY INTO, read-only T-SQL, Query Insights diagnostics, and Capacity Metrics CU-spike correlation. Synapse migration target SQL belongs to synapse-migration; Fabric SQL database belongs to sqldb-cli. Triggers: query warehouse, create warehouse table, failed or canceled query, CU spike, Capacity Metrics app, custom SQL pool, Lakehouse table health.
— the description by Microsoft

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

The sqldw-cli skill provides a structured interface for interacting with Microsoft Fabric data storage items, including Warehouses, Lakehouse SQL analytics endpoints, and Mirrored Databases. It operates through three distinct modes—authoring, consumption, and operations—to handle T-SQL execution, data ingestion, and performance diagnostics. The skill relies on the fabric-sqlendpoint-execute_query MCP tool for data-plane operations and requires users to consult specific mode references to ensure correct T-SQL syntax and operational constraints.

What it does

  • Executes DDL and DML statements for schema management and data manipulation in Fabric Warehouses.
  • Supports data ingestion via COPY INTO and OPENROWSET patterns.
  • Provides read-only querying capabilities for data exploration and metadata discovery.
  • Enables performance diagnostics using Query Insights and capacity metrics correlation.
  • Facilitates Lakehouse table health monitoring and diagnostic reporting.
  • Dispatches requests to specialized modes based on user intent to ensure safe and accurate execution.

When to use it

  • When you need to create, modify, or drop tables and schemas within a Fabric Warehouse.
  • When performing bulk data ingestion from external storage into a Warehouse.
  • When querying data or metadata from a Warehouse, Lakehouse SQL endpoint, or Mirrored Database.
  • When investigating performance issues, such as slow queries or CU spikes, using diagnostic views.
  • When correlating capacity consumption with specific SQL queries or users.

Advantages

  • Centralizes management of diverse Fabric SQL-based data items under a single skill.
  • Enforces mode-specific safety boundaries to prevent accidental mutations in read-only contexts.
  • Provides clear diagnostic paths for performance tuning and capacity management.
  • Standardizes T-SQL execution through a dedicated MCP tool interface.
  • Includes mandatory telemetry headers for consistent tracking across Fabric API calls.

Usage examples

“How many rows are in the nyctlc table in SkillsTestWarehouse?”
The agent selects the consumption mode, reads the consumption reference, and executes a SELECT COUNT(*) query via the MCP tool to return the exact row count.
“Create dbo.Orders in the warehouse with OrderId, CustomerId and OrderDate.”
The agent selects the authoring mode, reads the authoring reference, executes the CREATE TABLE statement, and performs a readback query to confirm the table exists.
“Which queries are slowest, and which tables should I cluster?”
The agent selects the operations mode, reads the operations reference, and executes diagnostic queries against queryinsights to identify slow-running patterns.

Install

  1. Download the skill as a ZIP:

    https://autogineer.com/agent-skills/microsoft/sqldw-cli/download

  2. Unzip it into the cross-client skills folder of your project:

    mkdir -p .agents/skills && unzip sqldw-cli.zip -d .agents/skills

Documentation by Microsoft

Telemetry — MANDATORY. Every api.fabric.microsoft.com call must carry x-ms-fabric-skill: sqldw-cli (az rest: --headers "x-ms-fabric-skill=sqldw-cli"), including every LRO poll, fabric_lro and retry. Snippets omit it — add it anyway. This covers all three modes; the mode references inherit it.

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

  3. Skill disambiguation: use sqldw-cli for T-SQL sent to a Warehouse, a Lakehouse SQL analytics endpoint, or a Mirrored Database, including plain Lakehouse table SELECT, row-count, filtering, and aggregation requests. Target Warehouse SQL executed within an end-to-end Azure Synapse or Dedicated SQL Pool migration belongs to synapse-migration; standalone Warehouse work before or after migration returns to sqldw-cli. Any notebook-cell or PySpark DataFrame work is spark-cli; a Fabric SQL database (OLTP) is sqldb-cli.

Fabric Warehouse and SQL Endpoints — CLI Skill

This one skill owns Fabric Warehouse, Lakehouse SQL analytics endpoints and Mirrored Databases: T-SQL authoring and ingestion, read-only querying, and warehouse performance diagnostics.

It is a mode dispatcher and contains NO procedures. Pick the mode that matches the request from the table below, then read the matching references/<mode>.md file end to end with your file-reading tool BEFORE issuing a single command. That file holds the T-SQL surface area, DDL constraints, query templates and gotchas; acting without it produces invalid T-SQL and wrong results.

Mode selection

ModeUse when the request ...Example triggersRead this first
authoringchanges warehouse state: table DDL, DML, ingestion, transactions, procedures, schema evolution, time travelcreate warehouse table, COPY INTO, OPENROWSET, INSERT/UPDATE/DELETE, warehouse MERGE, CTAS, sp_rename, create T-SQL procedure, warehouse time travelreferences/authoring.md
consumptionreads data or metadata: SELECT, row counts, filtering, aggregation, schema/object discovery, CSV exportquery warehouse, count rows lakehouse, SELECT lakehouse, show tables, describe warehouse schema, export SQL datareferences/consumption.md
operationsdiagnoses performance, failures, capacity consumption, SQL pool usage, or health through queryinsights and supported endpoint diagnosticsfailed or canceled warehouse queries, CU spike, Capacity Metrics app, expensive SQL users, custom SQL pool recommendation, queryinsights CPU, pressure events, cache warmth, Lakehouse tables needing attentionreferences/operations.md

Operations reference index

Read references/operations.md first for any operations request, then open the matching leaf directly from this index. Do not chain from links inside a leaf; for composite requests, follow scenarios.md only to the additional leaves that this index links directly.

RequestRead this leaf reference
composite operations scenarios such as why is my warehouse slow, performance degradation, optimization, or what are people runningreferences/operations/scenarios.md
slow-query summaries, top users, recent queries, pattern search, runtime profiles, cluster-key candidatesreferences/operations/query-reference.md
failed or canceled requests, error codes, recurring non-successful query shapesreferences/operations/failure-analysis.md
SQL pool pressure windows and overlapping requestsreferences/operations/pool-pressure.md
CPU concentration, regressions, repeated expensive queries, cache interpretationreferences/operations/resource-consumers.md
Lakehouse Delta file health and tables needing maintenancereferences/operations/lakehouse-health.md
Capacity Metrics CU spike, costly item, SQL query/user correlationreferences/operations/capacity-metrics-correlation.md — start at section 1
custom SQL pool recommendation for an already identified SQL endpointreferences/operations/capacity-metrics-correlation.md — skip FabricIQ and start at section 6
Mode boundary rule

Classify by intent, not by endpoint — all three modes issue the same execute_query call.

  • A schema-discovery SELECT run to plan a CREATE TABLE belongs to authoring, even though it only reads.

  • A SELECT that answers the user's question is consumption.

  • A query against queryinsights.* or a supported diagnostic such as Lakehouse sys.sp_get_table_health_metrics is operations; a SELECT against user tables is not, however slow it is.

consumption and operations are read-only. If a request genuinely spans modes, handle them one at a time and read each reference before you start that part. If the mode is ambiguous after reading this table, ask one short clarifying question instead of guessing.

Terminal write — the step you must not skip

Reading the reference and drafting the T-SQL is NOT completing the task. If you did not send the statement, nothing changed — say so explicitly rather than reporting success.

ModeTerminal write
authoringThe DDL/DML itself, sent through execute_query. Follow it with a readback in a second call (SELECT ... FROM INFORMATION_SCHEMA.TABLES after CREATE, SELECT COUNT(*) after DML) and report the object you created or changed under the name the user asked for. Only a Warehouse accepts table DDL/DML — see the mode reference for what a Lakehouse SQL endpoint and a Mirrored Database allow.
consumptionnone — this mode is read-only
operationsnone — read-only, but you must still run the diagnostic statements: every figure comes from a SELECT or documented read-only diagnostic procedure executed in the turn you report it, cited inline with its source view or procedure, never carried forward from an earlier turn. The only allowed diagnostic EXEC is sys.sp_get_table_health_metrics on a Lakehouse SQL analytics endpoint. Never execute ALTER, CREATE or DROP yourself, even when the diagnosis is certain.
consumption and operations reporting

Neither read-only mode has a terminal write, so its deliverable is the answer itself. Run the query against the live endpoint and report the real rows — a summary of the reference does not answer the request.

In operations, name the source view, catalog, or procedure each figure came from right next to it (for example 2,140 ms (queryinsights.long_running_queries) or 268 files (sys.sp_get_table_health_metrics)), including when the answer is zero rows. Re-run the diagnostic in the turn you report it rather than restating an earlier turn's output. Never fabricate, assume or infer diagnostic numbers.

Shared essentials (all modes)

Every mode reaches the data plane the same way. Resolve the workspace and item first, then send T-SQL through the MCP tool.

Execution surface — fabric-sqlendpoint-execute_query

All T-SQL runs through the fabric-sqlendpoint-execute_query MCP tool. For SQL data-plane execution this skill supersedes the COMMON-CLI SQL/TDS guidance — use the MCP tool, not sqlcmd, unless you are explicitly on the documented Legacy CLI Fallback path (see the mode reference). az rest stays the right tool for control-plane discovery.

fabric-sqlendpoint-execute_query(workspaceId, itemId, query)

  • Preflight, before the first operation of any mode: confirm a tool whose name ends in execute_query is in your tool list. It comes from the fabric-sqlendpoint MCP server, registered by a Fabric skills plugin or this repo's .mcp.json. The concrete name may be prefixed (fabric-sqlendpoint-execute_query, sqlendpoint-global-execute_query) — invoke the name you actually see. If none is present, say so, then fall back to the Legacy CLI Fallback (TDS client) documented in the mode reference; tell the user they can register the server for the primary path — see mcp-setup/. Exception: the Capacity Metrics correlation workflow is intentionally limited to the existing FabricIQ and SQL Endpoint MCP surfaces; if SQL Endpoint MCP is unavailable, report that phase as blocked and do not use the legacy fallback.

  • itemId is a GUID, never an FQDN or -d <DatabaseName>. For a Warehouse or a Mirrored Database use the item id; for a Lakehouse use properties.sqlEndpointProperties.id, not the Lakehouse item id.

  • One T-SQL batch per call. No GO separators, no sqlcmd meta-commands (:setvar, :r, -i). Split multi-batch work into separate calls. Only the last result set comes back.

  • Results cap at 10,000 rows and queries time out at 300s, with a 20 requests/min rate limit. Use TOP N, WHERE or aggregation; exactly 10,000 rows means the result was truncated. These are observed defaults, not a documented contract.

Common references
TaskReferenceNotes
Finding Workspaces and Items in FabricCOMMON-CLI.mdMandatory — read before resolving any workspace or item id
Fabric Topology & Key ConceptsCOMMON-CORE.mdItem types, workspaces, capacities
Environment URLsCOMMON-CORE.mdSovereign / non-public cloud hosts
Authentication & Token AcquisitionCOMMON-CORE.mdWrong audience = 401; read before any auth issue
Authentication RecipesCOMMON-CLI.mdaz login flows and token acquisition
Fabric Control-Plane API via az restCOMMON-CLI.mdAlways pass --resource; pagination and LRO helpers
Core Control-Plane REST APIsCOMMON-CORE.mdPagination, LRO polling, rate limiting
Gotchas & TroubleshootingCOMMON-CLI.mdaz rest audience, shell escaping, token expiry

Rules

MUST

  • Select exactly one mode from the table above before doing anything else.

  • Read references/<mode>.md end to end, as your FIRST tool call, before the first command of that mode. Read it ONCE, in a single full read: do not re-open it, do not grep it again, and do not page through it. You already have it.

  • For a focused operations request, read its one matching leaf after references/operations.md. For a composite request, read references/operations/scenarios.md, then each focused leaf named by that scenario; read every file once in full.

  • Resolve workspace and item ids by listing and filtering, never by guessing a GUID.

  • Execute T-SQL through fabric-sqlendpoint-execute_query whenever that tool is available; drop to the Legacy CLI Fallback documented in the mode reference only when it is not.

  • Announce a mode switch explicitly when the request crosses a boundary.

  • Treat the reference as instructions, never as the deliverable. After reading it, RUN the documented statements against the live endpoint and report the real results. Quoting what the reference says instead of executing it does not answer the request.

  • Produce every artefact the user asked for, under the name they used, and keep its heading even when the finding is "none", "zero rows" or "not applicable".

PREFER

  • The narrowest mode that satisfies the request.

  • Reading exactly one mode reference plus the minimum directly indexed operations leaves required by the selected focused or composite scenario.

  • Reporting the mode you chose in your first response so the user can correct you.

  • Labelling queries with OPTION (LABEL = '...') so the run is traceable in Query Insights.

  • Consolidating related statements into fewer calls — the rate limit is per identity, not per query.

AVOID

  • Acting from this dispatcher alone — it intentionally omits the T-SQL surface area, the DDL constraints and the diagnostic query shapes.

  • Answering with a summary of the reference instead of executing it.

  • Re-reading or re-grepping a reference you already loaded; it costs turns and tokens.

  • Mutating anything while in a read-only mode (consumption, operations).

  • Unbounded SELECT * — it silently truncates at 10,000 rows.

  • Loading a different skill for work this skill already owns (see CRITICAL NOTES 3).

Examples

User requestModeReference to read
"How many rows are in the nyctlc table in SkillsTestWarehouse?"consumptionreferences/consumption.md
"Create dbo.Orders in the warehouse with OrderId, CustomerId and OrderDate."authoringreferences/authoring.md
"Load Files/nyctlc_sample.csv from OneLake into the warehouse with COPY INTO."authoringreferences/authoring.md
"Which queries are slowest, and which tables should I cluster?"operationsreferences/operations.md
"Show me the average trip distance per vendor from the lakehouse SQL endpoint."consumptionreferences/consumption.md

Files

references/operations/query-reference.md

Query Reference — sqldw-cli operations mode

Detailed T-SQL queries for all monitoring and diagnostic analyses. All queries target the built-in queryinsights schema and system DMVs available in Fabric Data Warehouse.

Execution method: All queries in this file are executed via the fabric-sqlendpoint-execute_query MCP tool:

fabric-sqlendpoint-execute_query(workspaceId, itemId, "<query from this file>")

No GO separators or sqlcmd flags are needed — just pass the T-SQL text directly.

Data source: queryinsights views retain 30 days of history. Data appears with up to 15 minutes delay after query completion.

Performance Analysis Queries

MCP parameter substitution: The queries below are ready-to-use literals with defaults already applied (e.g. SELECT TOP 5, DATEADD(HOUR, -1, ...)); the parameter table beneath each query shows which value to change. The MCP fabric-sqlendpoint-execute_query tool does not support sqlcmd-style external parameter substitution (no -v, :setvar, or separate parameter binding), so to parameterize a query either edit the literal value in place before calling or declare the variables inside the batch with ordinary T-SQL: DECLARE @limit int = 5; DECLARE @hours int = 24; at the start of the query. In-batch T-SQL variables are fully supported; only external/sqlcmd-style parameterization is not.

In the parameter tables below, N is a positive integer and the leading minus is already part of the template — e.g. with the template DATEADD(HOUR, -N, ...) and N = 24 you get DATEADD(HOUR, -24, ...).

Long-Running Queries Summary

Purpose: Find the slowest queries from queryinsights.long_running_queries.

-- Ready-to-use (defaults applied):
SELECT TOP 5
    last_run_command,
    last_run_total_elapsed_time_ms,
    median_total_elapsed_time_ms,
    number_of_runs
FROM queryinsights.long_running_queries
ORDER BY last_run_total_elapsed_time_ms DESC;
ParameterDefaultDescription
TOP N5Max queries to return (replace the number)

Return fields:

FieldTypeDescription
last_run_commandstringSQL query text
last_run_total_elapsed_time_msintegerLast execution time (ms)
median_total_elapsed_time_msintegerMedian execution time across all runs (ms)
number_of_runsintegerTotal executions

Response formatting — present each result as:

Query '{first 50 chars}...' ran {number_of_runs} times, last took {last_run_total_elapsed_time_ms} ms (median {median_total_elapsed_time_ms} ms).

Most Frequently Run Queries

Purpose: Rank the most-repeated queries from queryinsights.frequently_run_queries to spot hot paths worth caching or tuning.

-- Ready-to-use (defaults applied):
SELECT TOP 10
    query_hash,
    number_of_runs,
    avg_total_elapsed_time_ms,
    last_run_command
FROM queryinsights.frequently_run_queries
ORDER BY number_of_runs DESC;
ParameterDefaultDescription
TOP N10Max queries to return (replace the number)

Return fields:

FieldTypeDescription
query_hashvarchar(200)Hash identifying the normalized query shape
number_of_runsintegerHow many times the query ran
avg_total_elapsed_time_msintegerAverage execution time across runs (ms)
last_run_commandstringSQL query text of the most recent run
Top Resource Consumers

Purpose: Identify CPU- and storage-heavy queries with performance recommendations.

-- Ready-to-use (defaults: top 5, last 1 hour):
SELECT TOP 5
    command,
    total_elapsed_time_ms,
    allocated_cpu_time_ms,
    data_scanned_remote_storage_mb,
    data_scanned_memory_mb,
    data_scanned_disk_mb
FROM queryinsights.exec_requests_history
WHERE start_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY allocated_cpu_time_ms DESC;
ParameterDefaultDescription
TOP N5Max results (replace the number)
DATEADD(HOUR, -N, ...)1Time window in hours (replace N)

Return fields:

FieldTypeDescription
commandstringSQL query text
total_elapsed_time_msintegerExecution time (ms)
allocated_cpu_time_msintegerCPU time consumed (ms)
data_scanned_remote_storage_mbfloatData from OneLake (cold)
data_scanned_memory_mbfloatData from memory cache
data_scanned_disk_mbfloatData from disk cache

Recommendation thresholds:

ConditionRecommendation
Remote scans > 1,000 MBReview data layout; consider OPTIMIZE/clustering
CPU > 5,000,000 msReview query logic; reduce joins/aggregations
Elapsed > 300,000 ms (5 min)Check joins, filters, and statistics
Top Users Insights

Purpose: Analyze user activity and query patterns using a ranked CTE.

-- Ready-to-use (defaults: top 5, last 24 hours, min 1 query):
WITH UserStats AS (
    SELECT
        COALESCE(login_name, 'Unknown User') AS user_name,
        COUNT(*) AS total_queries,
        AVG(total_elapsed_time_ms) AS avg_elapsed_time_ms,
        MAX(total_elapsed_time_ms) AS max_elapsed_time_ms,
        AVG(allocated_cpu_time_ms) AS avg_cpu_time_ms,
        SUM(allocated_cpu_time_ms) AS total_cpu_time_ms,
        AVG(data_scanned_remote_storage_mb + data_scanned_memory_mb + data_scanned_disk_mb) AS avg_data_scanned_mb,
        SUM(data_scanned_remote_storage_mb + data_scanned_memory_mb + data_scanned_disk_mb) AS total_data_scanned_mb,
        COUNT(CASE WHEN status = 'Failed' THEN 1 END) AS failed_queries,
        COUNT(DISTINCT CONVERT(DATE, start_time)) AS active_days,
        MIN(start_time) AS first_query_time,
        MAX(start_time) AS last_query_time
    FROM queryinsights.exec_requests_history
    WHERE start_time > DATEADD(HOUR, -24, GETUTCDATE())
    GROUP BY login_name
    HAVING COUNT(*) >= 1
),
RankedUsers AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY total_queries DESC, total_cpu_time_ms DESC) AS user_rank
    FROM UserStats
)
SELECT TOP 5
    user_name, total_queries, avg_elapsed_time_ms, max_elapsed_time_ms,
    avg_cpu_time_ms, total_cpu_time_ms, avg_data_scanned_mb,
    total_data_scanned_mb, failed_queries, active_days,
    first_query_time, last_query_time, user_rank
FROM RankedUsers
ORDER BY user_rank;
ParameterDefaultDescription
TOP N5Max users to analyze (replace the number)
DATEADD(HOUR, -N, ...)24Time window in hours (replace N)
HAVING COUNT(*) >= N1Minimum query count for inclusion (replace N)

Return fields:

FieldTypeDescription
user_namestringLogin name (or 'Unknown User')
total_queriesintegerTotal query count
avg_elapsed_time_msfloatAverage execution time
max_elapsed_time_msintegerSlowest single query
avg_cpu_time_msfloatAverage CPU per query
total_cpu_time_msbigintTotal CPU consumed
avg_data_scanned_mbfloatAvg data scanned per query
total_data_scanned_mbfloatTotal data scanned
failed_queriesintegerCount of failed queries
active_daysintegerDistinct days with activity
first_query_timedatetimeFirst query timestamp
last_query_timedatetimeMost recent query timestamp
user_rankintegerRank by total_queries then CPU

Response formatting:

  • User summary: "{user_name} executed {total_queries} queries over {active_days} days, with {success_rate}% success rate."

  • Query pattern: Classify as "Long-running analytical" (avg > 60s), "High-frequency operational" (>100/day), "Data-intensive" (avg > 1GB scanned), or "Mixed"

  • CPU intensity: Low (<1s), Medium (1–10s), High (>10s avg)

Recent Warehouse Activity

Purpose: Explain who is running which query shapes, through which applications and SQL pools, while excluding prior agent monitoring statements.

-- Ready-to-use (defaults: top 100, last 24 hours):
SELECT TOP 100
    start_time,
    login_name,
    program_name,
    statement_type,
    query_hash,
    sql_pool_name,
    status,
    total_elapsed_time_ms,
    allocated_cpu_time_ms,
    command
FROM queryinsights.exec_requests_history
WHERE start_time >= DATEADD(HOUR, -24, GETUTCDATE())
  AND command NOT LIKE '%queryinsights%'
  AND (label IS NULL OR label NOT LIKE 'AGENTCLI_MONITOR_%')
ORDER BY start_time DESC
OPTION (LABEL = 'AGENTCLI_MONITOR_ACTIVITY');
ParameterDefaultDescription
TOP N100Max requests to return (replace the number)
DATEADD(HOUR, -N, ...)24Time window in hours (replace N)

Group the results by login_name, program_name, statement_type, query_hash, and sql_pool_name. Report execution count and resource concentration separately; do not identify a user or application as expensive from request count alone.

Compare Recent vs Baseline

Purpose: Detect performance regressions by comparing recent window against historical baseline.

-- Ready-to-use (defaults: recent = 1 hour, baseline = 7 days back):
WITH recent AS (
    SELECT
        AVG(total_elapsed_time_ms) AS avg_recent_elapsed_ms,
        AVG(allocated_cpu_time_ms) AS avg_recent_cpu_ms,
        AVG(data_scanned_remote_storage_mb + data_scanned_memory_mb + data_scanned_disk_mb) AS avg_recent_data_scanned_mb
    FROM queryinsights.exec_requests_history
    WHERE start_time > DATEADD(HOUR, -1, GETUTCDATE())
      AND status = 'Succeeded'
),
baseline AS (
    SELECT
        AVG(total_elapsed_time_ms) AS avg_baseline_elapsed_ms,
        AVG(allocated_cpu_time_ms) AS avg_baseline_cpu_ms,
        AVG(data_scanned_remote_storage_mb + data_scanned_memory_mb + data_scanned_disk_mb) AS avg_baseline_data_scanned_mb
    FROM queryinsights.exec_requests_history
    WHERE start_time > DATEADD(DAY, -7, GETUTCDATE())
      AND start_time <= DATEADD(HOUR, -1, GETUTCDATE())
      AND status = 'Succeeded'
)
SELECT
    r.avg_recent_elapsed_ms,
    b.avg_baseline_elapsed_ms,
    r.avg_recent_cpu_ms,
    b.avg_baseline_cpu_ms,
    r.avg_recent_data_scanned_mb,
    b.avg_baseline_data_scanned_mb
FROM recent r
CROSS JOIN baseline b;
ParameterDefaultDescription
DATEADD(HOUR, -N, ...) in recent CTE1Size of the recent analysis window (hours)
DATEADD(DAY, -N, ...) in baseline CTE7Days back for baseline comparison

Return fields:

FieldDescription
avg_recent_elapsed_msRecent window average elapsed time
avg_baseline_elapsed_msBaseline period average elapsed time
avg_recent_cpu_msRecent window average CPU time
avg_baseline_cpu_msBaseline period average CPU time
avg_recent_data_scanned_mbRecent window average data scanned
avg_baseline_data_scanned_mbBaseline period average data scanned

Response formatting — compute percent change for each metric. Flag regressions > 20% with a warning.

Recent Queries

Purpose: Retrieve the most recently executed queries.

-- Ready-to-use (default: 10 most recent):
SELECT TOP 10
    distributed_statement_id,
    login_name,
    command,
    start_time,
    total_elapsed_time_ms,
    status
FROM queryinsights.exec_requests_history
ORDER BY start_time DESC;
ParameterDefaultDescription
TOP N10Max queries to return (replace the number)
Search Query Patterns

Purpose: Search historical query patterns by table name, column, or keyword.

-- Ready-to-use (replace 'FactSales' with your search term):
SELECT TOP 10
    query_hash,
    COUNT(*) AS execution_count,
    AVG(total_elapsed_time_ms) AS avg_elapsed_ms,
    MIN(total_elapsed_time_ms) AS min_elapsed_ms,
    MAX(total_elapsed_time_ms) AS max_elapsed_ms,
    MAX(command) AS sample_command
FROM queryinsights.exec_requests_history
WHERE command LIKE '%FactSales%'
  AND command NOT LIKE '%queryinsights%'
GROUP BY query_hash
HAVING COUNT(*) >= 1
ORDER BY execution_count DESC;
ParameterDefaultDescription
LIKE '%...%'(required)Replace FactSales with your search term
HAVING COUNT(*) >= N1Minimum pattern execution count
TOP N10Max patterns to return

Deep Diagnostics Queries

Long-Running Query Analysis

Purpose: Identify queries exceeding a duration threshold for deeper investigation.

-- Ready-to-use (defaults: top 3, min 30 seconds):
SELECT TOP 3
    distributed_statement_id,
    command,
    total_elapsed_time_ms,
    allocated_cpu_time_ms,
    data_scanned_remote_storage_mb,
    data_scanned_memory_mb,
    result_cache_hit
FROM queryinsights.exec_requests_history
WHERE total_elapsed_time_ms >= 30000
  AND status = 'Succeeded'
ORDER BY total_elapsed_time_ms DESC;
ParameterDefaultDescription
TOP N3Max queries to analyze (replace the number)
>= N (WHERE clause)30000Minimum duration threshold in ms

Analysis checklist:

  • High data_scanned_remote_storage_mb → data layout issues

  • High allocated_cpu_time_ms relative to elapsed → CPU-bound query

  • High elapsed but low CPU → resource contention (check pressure windows)

  • result_cache_hit = 0 on repeated queries → no result-cache creation or use; check eligibility before calling it a miss

Pressure Window Analysis

The event-based pressure query is now isolated in the directly indexed references/operations/pool-pressure.md leaf. Use that version; it calculates LEAD across all state events and matches overlapping requests by both interval and sql_pool_name.

Cache Warmth Analysis

Use the directly indexed references/operations/resource-consumers.md leaf. It preserves the distinct meanings of result_cache_hit = 1 (entry creation) and result_cache_hit = 2 (cache hit), handles zero-scan rows before ratios, and records the current result-set-caching limitation.

Cluster Key Recommendations

Purpose: Use a two-phase approach to identify tables that would benefit from data clustering.

Phase 1 (data-volume): Aggregate query_hash groups to find the highest total data scanned.

SELECT
    query_hash,
    COUNT(*) AS exec_count,
    AVG(total_elapsed_time_ms) AS avg_elapsed_ms,
    AVG(allocated_cpu_time_ms) AS avg_cpu_ms,
    SUM(CAST(data_scanned_remote_storage_mb AS BIGINT)) AS total_remote_mb,
    AVG(data_scanned_remote_storage_mb) AS avg_remote_mb,
    MAX(command) AS sample_command
FROM queryinsights.exec_requests_history
WHERE status = 'Succeeded'
  AND command IS NOT NULL
  AND LEN(command) > 20
  AND start_time > DATEADD(HOUR, -168, GETUTCDATE())  -- 7 days
  AND command NOT LIKE '%queryinsights%'
  AND command NOT LIKE '%INFORMATION_SCHEMA%'
  AND command NOT LIKE '%sys.%'
GROUP BY query_hash
HAVING COUNT(*) >= 3
ORDER BY SUM(CAST(data_scanned_remote_storage_mb AS BIGINT)) DESC;

Phase 2 (predicate-parsing): Examine sample_command for each hash to identify:

  1. Tables in FROM / JOIN clauses (handles 3-part, 2-part, and 1-part names)

  2. Columns in WHERE predicates (range filters, IN, BETWEEN, comparisons) — these are cluster key candidates

  3. Score columns by: predicate usage count + cardinality + data type suitability

Important: Equality JOIN ON conditions do NOT benefit from data clustering. Only WHERE filter predicates benefit.

Cardinality guidance — prefer mid-to-high cardinality columns:

  • High cardinality (many distinct values, e.g., date, ID) → best candidates — enables efficient file skipping

  • Low cardinality (few distinct values, e.g., gender, region) → poor candidates — limited pruning benefit

Fallback heuristic — when predicates can't be parsed, use column metadata:

  • Date/time columns with mid-to-high cardinality → strong cluster key candidates (score 50)

  • Columns ending in _sk, _id, _key, Date, Time → moderate candidates (score 30)

  • Integer columns → weak candidates (score 15)

Row count verification — use sys.partitions with COUNT fallback:

-- Try sys.partitions first
SELECT SUM(p.rows) AS row_count
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0, 1)
WHERE s.name = 'dbo' AND t.name = 'FactSales';

-- If 0, use COUNT fallback
SELECT SUM(CAST(1 AS BIGINT)) AS rc FROM [dbo].[FactSales];

Ranking: Primary = total data scanned (I/O impact). Secondary = query_count * row_count.

To apply clustering — use CTAS (ALTER TABLE not supported in Fabric):

-- Step 1: Create clustered copy
CREATE TABLE dbo.FactSales_clustered
WITH (CLUSTER BY (SaleDate, Region))
AS SELECT * FROM dbo.FactSales;

Post-CTAS table swap — replace the original table using sp_rename:

-- Step 2: Rename original table
EXEC sp_rename 'dbo.FactSales', 'FactSales_old';

-- Step 3: Rename clustered table to original name
EXEC sp_rename 'dbo.FactSales_clustered', 'FactSales';

-- Step 4: Verify, then drop old table
-- DROP TABLE IF EXISTS dbo.FactSales_old;

Verify clustering columns after swap:

SELECT
    t.name AS table_name,
    c.name AS column_name,
    ic.data_clustering_ordinal AS clustering_ordinal
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.data_clustering_ordinal > 0 AND t.name = 'FactSales'
ORDER BY ic.data_clustering_ordinal;

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.

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Needs: Needs an MCP server

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Published by
Microsoft
Publisher
Microsoft (Official)
Source
microsoft/skills-for-fabric · skills/sqldw-cli · commit f8fac11
Licence
MIT License (redistribution allowed)
  • LICENSE (added by AutoGineer from LICENSE)
  • ATTRIBUTION.md (written by AutoGineer)
Synced
Also distributed in
microsoft/skills-for-fabric

AutoGineer is not affiliated with or endorsed by Microsoft or Microsoft.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download sqldw-cli.zip