-- Check distribution skew
DBCC PDW_SHOWSPACEUSED('dbo.FactSales');

-- Output:
-- index_name | distribution_id | row_count | size_in_bytes
-- -----------+-----------------+-----------+-------------
-- CCI_1      | 0               | 1000000   | 50000000
-- CCI_1      | 1               | 1000000   | 50000000
-- CCI_1      | 2               | 1000001   | 50000010

-- Check statistics
sys.dm_pdw_exec_stats
WHERE command LIKE '%SELECT%'

-- Monitor query performance
SELECT
    query_id,
    sql_text,
    start_time,
    end_time,
    total_elapsed_time / 1000 AS duration_seconds,
    row_count,
    spilled_thread_count
FROM sys.dm_pdw_exec_requests
WHERE status = 'Completed'
ORDER BY total_elapsed_time DESC;

-- Create CCI for fact table
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

-- Create CCI for fact table
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

-- Create nonclustered index for lookup queries
CREATE NONCLUSTERED INDEX IX_FactSales_CustomerKey
ON dbo.FactSales(customer_key)
INCLUDE (sale_date, total_amount);

-- Create materialized view for common aggregations
CREATE MATERIALIZED VIEW MV_DailySalesSummary
WITH (DISTRIBUTION = HASH(sale_date))
AS
SELECT
    sale_date,
    COUNT(*) AS transaction_count,
    SUM(total_amount) AS daily_revenue
FROM dbo.FactSales
GROUP BY sale_date;

-- Enable result set caching
SET RESULT_SET_CACHING ON;

-- Enable statistics profiling
SET STATISTICS PROFILE ON;

-- Adjust DOP (Degree of Parallelism)
SET statement_dop = 4;

-- Enable bulk insert optimization
SET BULK_INSERT_BATCH_SIZE = 100000;