Sqldw 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 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 and 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

---

Tool Stack

For installation and setup, see Prerequisites.

---

Connection

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

Discover the SQL Endpoint FQDN

Per COMMON-CLI.md Discovering Connection Parameters via REST:

WS_ID="<workspaceId>"
ITEM_ID="<warehouseId>"

# 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: <uniqueId>.datawarehouse.fabric.microsoft.com

Connect with sqlcmd (Go)

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

Reusable Connection Variables

FABRIC_SERVER="<endpoint>.datawarehouse.fabric.microsoft.com"
FABRIC_DB="<DatabaseName>"
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
$s = "<endpoint>.datawarehouse.fabric.microsoft.com"; $db = "<DatabaseName>"
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.

Long-Running Queries Summary

Find the slowest queries from queryinsights.long_running_queries. See 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 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

Top Users Insights

Analyze user activity and query patterns. See 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 for SQL and formatting.

Recent Queries

Retrieve the most recently executed queries. See 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 for SQL.

---

Deep Diagnostics

All SQL queries for diagnostics are in query-reference.md.

Analyze Long-Running Query Plans

See 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 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 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 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 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.

---

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 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 and the Fabric guidelines.

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 guessing at bottlenecks
• Label all agent queries with OPTION (LABEL = 'AGENTCLI_MONITOR_...') for tracing in Query Insights
• Prefer mid-to-high cardinality columns for clustering keys — low cardinality columns offer limited file-skipping benefit
• Use `WHERE` predicates to identify cluster key candidates — equality JOIN ON conditions do not benefit from clustering
• Always verify clustering after CTAS by querying sys.index_columns.data_clustering_ordinal
• Check cold vs warm cache before concluding a query is inherently slow — first execution may be a cold start
• Adjust time windows (DATEADD parameters) to match user's investigation scope — don't default to arbitrary windows

---

Gotchas, Rules, Troubleshooting

For generic CLI gotchas (connection, auth, shell escaping): see COMMON-CLI.md § Gotchas & Troubleshooting. For T-SQL/platform gotchas: see SQLDW-CONSUMPTION-CORE.md § Gotchas and Troubleshooting Reference.

MUST DO

• Always check Fabric DW Constraints before recommending optimizations
• When recommending clustering, instruct users to use CTAS with WITH (CLUSTER BY (...)) — not ALTER TABLE
• Report actual query output — do not fabricate or assume results
• Label queries with OPTION (LABEL = 'AGENTCLI_MONITOR_...') for Query Insights tracing

PREFER

• Start with high-level queries (long-running summary, baseline comparison) before drilling into diagnostics
• Use the pressure window analysis for root-cause analysis rather than guessing at bottlenecks
• Combine multiple queries in the Agentic Workflows for comprehensive investigations
• Adjust time windows (DATEADD parameters) based on what the user asks for

AVOID

• Recommending Fabric-unsupported features (nonclustered indexes, materialized views, index hints, triggers)
• Suggesting that Query Insights needs to be "enabled" or "turned on" — queryinsights views are always available; permission errors indicate insufficient workspace role (Admin or Member required)
• Running monitoring queries without confirming connection parameters (server, database)
• Guessing at performance root causes without running the diagnostic queries
• Using SELECT * in monitoring queries — always select specific columns

TROUBLESHOOTING (Monitoring-Specific)

For generic connection/auth troubleshooting, see COMMON-CLI.md § Gotchas & Troubleshooting.

---

Examples

Find Slow Queries

User: "What are the slowest queries in my warehouse?"

Agent: Runs the long-running queries summary via sqlcmd:

sqlcmd -S "myserver.datawarehouse.fabric.microsoft.com" -d "MyWarehouse" -G -Q "
SELECT TOP 5
    left(last_run_command, 80) AS query_preview,
    number_of_runs,
    last_run_total_elapsed_time_ms,
    median_total_elapsed_time_ms
FROM queryinsights.long_running_queries
ORDER BY median_total_elapsed_time_ms DESC
"

The top 5 slowest queries are:

1. SELECT * FROM FactSales JOIN DimCustomer... — ran 12 times, last took 45,200 ms (median 38,500 ms)

2. SELECT region, SUM(amount)... — ran 8 times, last took 32,100 ms (median 29,000 ms)

...

Diagnose Performance Degradation

User: "Is my warehouse slower than last week?"

Agent: Runs the baseline comparison query and presents:

Performance Comparison (last 1h vs 7-day baseline):

- Elapsed time: 2,450 ms avg → 4,100 ms avg (+67.3%)

- CPU consumption: 1,200 ms → 2,800 ms (+133.3%)

- Data scanned: 450 MB → 480 MB (+6.7%)

>

Significant regression detected. Let me check for pressure events and analyze the heaviest queries...

Then follows up with pressure window and resource consumer queries.

Get Clustering Recommendations

User: "Which tables should I cluster and on what columns?"

Agent: Runs the cluster key recommendation queries and presents:

Recommended Clustering Keys:

| Table | Recommended Columns | Row Count | Total Scanned MB |

|-------|-------------------|-----------|-----------------|

| dbo.FactSales | SaleDate, Region | 50M | 12,500 MB |

| dbo.FactInventory | ProductID, WarehouseID | 12M | 3,200 MB |

>

To apply clustering, use CTAS:

```sql

CREATE TABLE dbo.FactSales_clustered

WITH (CLUSTER BY (SaleDate, Region))

AS SELECT * FROM dbo.FactSales;

```

Query Reference — sqldw-operations-cli

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.

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

---

Performance Analysis Queries

Long-Running Queries Summary

Purpose: Find the slowest queries from queryinsights.long_running_queries.

SELECT TOP @limit
    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;

Return fields:

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).

---

Top Resource Consumers

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

SELECT TOP @limit
    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, -@hours, GETUTCDATE())
ORDER BY allocated_cpu_time_ms DESC;

Return fields:

Recommendation thresholds:

---

Top Users Insights

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

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, -@hours, GETUTCDATE())
    GROUP BY login_name
    HAVING COUNT(*) >= @min_queries
),
RankedUsers AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY total_queries DESC, total_cpu_time_ms DESC) AS user_rank
    FROM UserStats
)
SELECT TOP @limit
    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;

Return fields:

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)

---

Compare Recent vs Baseline

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

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, -@hours, 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, -@days_back, GETUTCDATE())
      AND start_time <= DATEADD(HOUR, -@hours, 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;

Return fields:

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

---

Recent Queries

Purpose: Retrieve the most recently executed queries.

SELECT TOP @limit
    distributed_statement_id,
    login_name,
    command,
    start_time,
    total_elapsed_time_ms,
    status
FROM queryinsights.exec_requests_history
ORDER BY start_time DESC;

---

Search Query Patterns

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

SELECT TOP @limit
    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 '%' + @search_term + '%'
  AND command NOT LIKE '%queryinsights%'
GROUP BY query_hash
HAVING COUNT(*) >= @min_execution_count
ORDER BY execution_count DESC;

---

Deep Diagnostics Queries

Long-Running Query Analysis

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

SELECT TOP @limit
    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 >= @min_duration_ms
  AND status = 'Succeeded'
ORDER BY total_elapsed_time_ms DESC;

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 → cache not effective

---

Pressure Window Analysis

Purpose: Identify SQL pool pressure events from queryinsights.sql_pool_insights and find the heaviest queries running during those windows.

Step 1 — Find pressure windows by consolidating consecutive pressure events:

WITH PressureEvents AS (
    SELECT
        timestamp,
        sql_pool_name,
        max_resource_percentage,
        is_pool_under_pressure,
        current_workspace_capacity,
        LAG(timestamp) OVER (PARTITION BY sql_pool_name ORDER BY timestamp) AS prev_timestamp
    FROM queryinsights.sql_pool_insights
    WHERE timestamp > DATEADD(HOUR, -@hours, GETUTCDATE())
      AND is_pool_under_pressure = 1
),
WindowBoundaries AS (
    SELECT
        timestamp, sql_pool_name, max_resource_percentage, current_workspace_capacity,
        CASE
            WHEN prev_timestamp IS NULL
              OR DATEDIFF(SECOND, prev_timestamp, timestamp) > 120
            THEN 1
            ELSE 0
        END AS is_window_start
    FROM PressureEvents
),
WindowGroups AS (
    SELECT
        timestamp, sql_pool_name, max_resource_percentage, current_workspace_capacity,
        SUM(is_window_start) OVER (
            PARTITION BY sql_pool_name ORDER BY timestamp ROWS UNBOUNDED PRECEDING
        ) AS window_id
    FROM WindowBoundaries
)
SELECT
    sql_pool_name,
    window_id,
    MIN(timestamp) AS window_start,
    MAX(timestamp) AS window_end,
    DATEDIFF(SECOND, MIN(timestamp), MAX(timestamp)) AS duration_seconds,
    COUNT(*) AS pressure_data_points,
    MAX(max_resource_percentage) AS peak_resource_pct,
    MAX(current_workspace_capacity) AS capacity_sku
FROM WindowGroups
GROUP BY sql_pool_name, window_id
ORDER BY MIN(timestamp) DESC;

Step 2 — For each pressure window, find overlapping queries (substitute actual window_start/window_end timestamps from Step 1):

-- Replace '<window_start>' and '<window_end>' with actual timestamps from Step 1
SELECT TOP @top_n
    command,
    login_name,
    start_time,
    end_time,
    total_elapsed_time_ms,
    allocated_cpu_time_ms,
    data_scanned_remote_storage_mb,
    data_scanned_memory_mb,
    data_scanned_disk_mb,
    status,
    result_cache_hit,
    query_hash
FROM queryinsights.exec_requests_history
WHERE start_time <= '<window_end>'
  AND end_time >= '<window_start>'
  AND command IS NOT NULL
  AND LEN(command) > 20
ORDER BY allocated_cpu_time_ms DESC;

Response formatting — for each overlapping query, classify problems:

• CPU > 5M ms → "Extremely high CPU", >1M → "Very high CPU", >100K → "High CPU"
• Remote storage > 1,000 MB → "Heavy remote storage scans (cold data)"
• Total data > 5 GB → "Massive data scan", > 1 GB → "Large data scan"
• Elapsed > 5 min → "Very long running", > 1 min → "Long running"
• result_cache_hit = 0 and elapsed > 30s → "Cache miss on slow query"
• SELECT * detected → "Uses SELECT * (missing column pruning)"

Global recommendations — based on aggregate 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

---

Cache Warmth Analysis

Purpose: Compare cold (remote storage) vs warm (memory/disk) reads for repeated queries using a CTE with hash-level grouping.

WITH hash_stats AS (
    SELECT
        query_hash,
        COUNT(*) AS run_count
    FROM queryinsights.exec_requests_history
    WHERE query_hash IS NOT NULL
      AND status = 'Succeeded'
      AND start_time > DATEADD(HOUR, -@hours, GETUTCDATE())
    GROUP BY query_hash
    HAVING COUNT(*) >= @min_runs
)
SELECT TOP 500
    e.query_hash,
    e.start_time,
    e.total_elapsed_time_ms,
    e.allocated_cpu_time_ms,
    e.data_scanned_remote_storage_mb,
    e.data_scanned_memory_mb,
    e.data_scanned_disk_mb,
    e.result_cache_hit,
    e.row_count,
    LEFT(e.command, 200) AS command_snippet,
    e.login_name,
    h.run_count
FROM queryinsights.exec_requests_history e
INNER JOIN hash_stats h ON e.query_hash = h.query_hash
WHERE e.status = 'Succeeded'
  AND e.start_time > DATEADD(HOUR, -@hours, GETUTCDATE())
ORDER BY h.run_count DESC, e.query_hash, e.start_time;

Classification logic — for each execution, compute total_mb = remote + memory + disk:

• result_cache_hit = 1 → cached
• total_mb = 0 → no-scan
• remote_mb / total_mb > 0.8 → cold (>80% from remote storage)
• (memory_mb + disk_mb) / total_mb > 0.8 → warm (>80% from cache)
• Otherwise → mixed

Overall pattern per query_hash:

• always-cold: Every run is cold — never benefits from caching
• always-warm: Every run is warm — well cached
• warming-up: Started cold, latest run is warm
• inconsistent: Mix of cold and warm runs
• always-cached: Result set cache hit every time

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
• Compare avg cold elapsed vs avg warm elapsed to calculate warm speedup ratio

---

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.