Sqldw Operations Cli

# 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`.

```sql
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;
```

| Parameter | Default | Description |
|-----------|---------|-------------|
| `@limit` | 5 | Max queries to return |

**Return fields:**

| Field | Type | Description |
|-------|------|-------------|
| `last_run_command` | string | SQL query text |
| `last_run_total_elapsed_time_ms` | integer | Last execution time (ms) |
| `median_total_elapsed_time_ms` | integer | Median execution time across all runs (ms) |
| `number_of_runs` | integer | Total 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).

---

### Top Resource Consumers

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

```sql
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;
```

| Parameter | Default | Description |
|-----------|---------|-------------|
| `@limit` | 5 | Max results |
| `@hours` | 1 | Time window in hours |

**Return fields:**

| Field | Type | Description |
|-------|------|-------------|
| `command` | string | SQL query text |
| `total_elapsed_time_ms` | integer | Execution time (ms) |
| `allocated_cpu_time_ms` | integer | CPU time consumed (ms) |
| `data_scanned_remote_storage_mb` | float | Data from OneLake (cold) |
| `data_scanned_memory_mb` | float | Data from memory cache |
| `data_scanned_disk_mb` | float | Data from disk cache |

**Recommendation thresholds:**

| Condition | Recommendation |
|-----------|----------------|
| Remote scans > 1,000 MB | Review data layout; consider OPTIMIZE/clustering |
| CPU > 5,000,000 ms | Review 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.

```sql
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