Tutorial 13: Monitoring and Diagnostics¶

-- View query execution steps
SELECT
    request_id,
    step_index,
    operation_type,
    distribution_type,
    location_type,
    status,
    total_elapsed_time / 1000.0 AS step_seconds,
    row_count,
    command
FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID12345'
ORDER BY step_index;

-- View data movement details
SELECT
    request_id,
    step_index,
    pdw_node_id,
    distribution_id,
    type,
    status,
    start_time,
    end_time,
    total_elapsed_time / 1000.0 AS seconds,
    rows_processed,
    bytes_processed / 1024.0 / 1024.0 AS mb_processed
FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID12345'
ORDER BY step_index, pdw_node_id;

-- Identify data skew
SELECT
    step_index,
    MIN(rows_processed) AS min_rows,
    MAX(rows_processed) AS max_rows,
    AVG(rows_processed) AS avg_rows,
    MAX(rows_processed) * 1.0 / NULLIF(AVG(rows_processed), 0) AS skew_factor
FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID12345'
GROUP BY step_index
HAVING MAX(rows_processed) * 1.0 / NULLIF(AVG(rows_processed), 0) > 2
ORDER BY step_index;

-- View current waits
SELECT
    request_id,
    type AS wait_type,
    state,
    object_type,
    object_name,
    request_time,
    DATEDIFF(second, request_time, GETDATE()) AS wait_seconds
FROM sys.dm_pdw_waits
WHERE state = 'Queued'
ORDER BY request_time;

-- Aggregate wait statistics
SELECT
    type AS wait_type,
    COUNT(*) AS wait_count,
    SUM(DATEDIFF(second, request_time, acquire_time)) AS total_wait_seconds,
    AVG(DATEDIFF(second, request_time, acquire_time)) AS avg_wait_seconds
FROM sys.dm_pdw_waits
WHERE acquire_time IS NOT NULL
  AND request_time >= DATEADD(day, -1, GETUTCDATE())
GROUP BY type
ORDER BY total_wait_seconds DESC;

-- View node resource utilization
SELECT
    pdw_node_id,
    type,
    name,
    compute_node_id,
    process_id,
    memory_usage_mb,
    cpu_usage_percent,
    total_space_mb,
    available_space_mb
FROM sys.dm_pdw_nodes_resource_governor_workload_groups
ORDER BY pdw_node_id;

-- View storage utilization
SELECT
    pdw_node_id,
    distribution_id,
    reserved_space_mb,
    data_space_mb,
    index_space_mb,
    unused_space_mb
FROM sys.dm_pdw_nodes_db_file_space_usage
ORDER BY reserved_space_mb DESC;

-- Table storage metrics
SELECT
    OBJECT_NAME(object_id) AS table_name,
    SUM(reserved_page_count) * 8 / 1024.0 AS reserved_mb,
    SUM(data_page_count) * 8 / 1024.0 AS data_mb,
    SUM(index_page_count) * 8 / 1024.0 AS index_mb,
    SUM(row_count) AS row_count
FROM sys.dm_pdw_nodes_db_partition_stats
GROUP BY object_id
ORDER BY reserved_mb DESC;

-- View serverless SQL query history
SELECT
    submit_time,
    end_time,
    DATEDIFF(second, submit_time, end_time) AS duration_seconds,
    data_processed_mb,
    status,
    error_id,
    LEFT(query_text, 200) AS query_preview
FROM sys.dm_exec_requests_history
WHERE submit_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY submit_time DESC;

-- Cost estimation (based on data processed)
SELECT
    CAST(submit_time AS DATE) AS query_date,
    COUNT(*) AS query_count,
    SUM(data_processed_mb) AS total_data_mb,
    SUM(data_processed_mb) / 1024.0 AS total_data_gb,
    SUM(data_processed_mb) / 1024.0 * 5.0 AS estimated_cost_usd  -- ~$5 per TB
FROM sys.dm_exec_requests_history
WHERE submit_time >= DATEADD(day, -30, GETUTCDATE())
GROUP BY CAST(submit_time AS DATE)
ORDER BY query_date DESC;

-- Top data consumers
SELECT TOP 20
    query_hash,
    COUNT(*) AS execution_count,
    SUM(data_processed_mb) AS total_data_mb,
    AVG(data_processed_mb) AS avg_data_mb,
    MAX(LEFT(query_text, 200)) AS sample_query
FROM sys.dm_exec_requests_history
WHERE submit_time >= DATEADD(day, -7, GETUTCDATE())
GROUP BY query_hash
ORDER BY total_data_mb DESC;

-- Create KPI view
CREATE VIEW monitoring.vw_DailyKPIs
AS
WITH QueryStats AS (
    SELECT
        CAST(submit_time AS DATE) AS metric_date,
        COUNT(*) AS total_queries,
        SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) AS failed_queries,
        AVG(total_elapsed_time / 1000.0) AS avg_duration_seconds,
        MAX(total_elapsed_time / 1000.0) AS max_duration_seconds,
        SUM(CASE WHEN result_cache_hit = 1 THEN 1 ELSE 0 END) AS cache_hits
    FROM sys.dm_pdw_exec_requests
    WHERE submit_time >= DATEADD(day, -30, GETUTCDATE())
    GROUP BY CAST(submit_time AS DATE)
)
SELECT
    metric_date,
    total_queries,
    failed_queries,
    ROUND(100.0 * (total_queries - failed_queries) / NULLIF(total_queries, 0), 2) AS success_rate,
    ROUND(avg_duration_seconds, 2) AS avg_duration_seconds,
    ROUND(max_duration_seconds, 2) AS max_duration_seconds,
    ROUND(100.0 * cache_hits / NULLIF(total_queries, 0), 2) AS cache_hit_rate
FROM QueryStats;
GO