Tutorial 10: Dedicated SQL Pools

Comparative positioning note

This document is written from the perspective of Microsoft Azure, Cloud Scale Analytics, and CSA Loom. Any description of third-party or competing products, services, pricing, or capabilities is derived from publicly available documentation and sources believed accurate at the time of writing, and is provided for general comparison only. We do not claim expertise in, or authority over, any non-Microsoft product or service; the respective vendor's official documentation is the authoritative source for their offerings, which may change over time. Nothing here is intended to disparage any vendor — where a competing product has genuine advantages, we aim to note them honestly. Verify all third-party details against the vendor's current official documentation before making decisions.

Overview

This tutorial covers Azure Synapse Dedicated SQL pools, providing enterprise-grade data warehousing capabilities with MPP (Massively Parallel Processing) architecture for high-performance analytics at scale.

Prerequisites

• Completed Tutorial 9: Serverless SQL Queries
• Understanding of data warehouse concepts
• Familiarity with T-SQL

Learning Objectives

By the end of this tutorial, you will be able to:

• Create and configure dedicated SQL pools
• Design optimized table structures with distribution
• Implement data loading patterns
• Optimize query performance
• Manage workload and resources

---

Section 1: Understanding Dedicated SQL Pools

MPP Architecture

Dedicated SQL pools use Massively Parallel Processing:

```text┌─────────────────────────────────────────────────────────────────┐ │ Control Node │ │ (Query Optimization) │ └─────────────────────────┬───────────────────────────────────────┘ │ ┌───────────────┼───────────────┐ │ │ │ ▼ ▼ ▼ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │ Compute Node 1 │ │ Compute Node 2 │ │ Compute Node N │ │ Distribution │ │ Distribution │ │ Distribution │ └────────┬────────┘ └────────┬────────┘ └────────┬────────┘ │ │ │ ▼ ▼ ▼ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │ Azure Storage │ │ Azure Storage │ │ Azure Storage │ │ (Data Files) │ │ (Data Files) │ │ (Data Files) │ └─────────────────┘ └─────────────────┘ └─────────────────┘

### Creating a Dedicated SQL Pool

```sql
-- Via Azure Portal or Azure CLI
-- az synapse sql pool create --name mypool --workspace-name myworkspace --performance-level DW100c

-- Connect to dedicated pool and verify
SELECT @@VERSION;
SELECT DB_NAME();

-- Check current performance level
SELECT
    DB_NAME() AS database_name,
    (CASE WHEN is_paused = 0 THEN 'Running' ELSE 'Paused' END) AS status
FROM sys.databases
WHERE name = DB_NAME();

---

Section 2: Table Design and Distribution

Distribution Strategies

-- ROUND_ROBIN Distribution (default)
-- Good for: Staging tables, tables without clear join key
CREATE TABLE staging.Sales
(
    SaleID INT,
    ProductID INT,
    CustomerID INT,
    SaleAmount DECIMAL(10,2),
    SaleDate DATE
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

-- HASH Distribution
-- Good for: Large fact tables, frequent join columns
CREATE TABLE fact.Sales
(
    SaleID INT NOT NULL,
    ProductID INT NOT NULL,
    CustomerID INT NOT NULL,
    DateKey INT NOT NULL,
    Quantity INT,
    UnitPrice DECIMAL(10,2),
    TotalAmount DECIMAL(12,2),
    DiscountAmount DECIMAL(10,2)
)
WITH
(
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION (DateKey RANGE RIGHT FOR VALUES
        (20240101, 20240201, 20240301, 20240401,
         20240501, 20240601, 20240701, 20240801,
         20240901, 20241001, 20241101, 20241201))
);

-- REPLICATE Distribution
-- Good for: Small dimension tables (<2GB compressed)
CREATE TABLE dim.Product
(
    ProductKey INT NOT NULL,
    ProductID VARCHAR(20) NOT NULL,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    SubCategory VARCHAR(50),
    Brand VARCHAR(50),
    UnitPrice DECIMAL(10,2),
    IsActive BIT
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE dim.Customer
(
    CustomerKey INT NOT NULL,
    CustomerID VARCHAR(20) NOT NULL,
    CustomerName VARCHAR(100),
    Segment VARCHAR(50),
    Region VARCHAR(50),
    Country VARCHAR(50),
    City VARCHAR(100)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Indexing Strategies

-- Clustered Columnstore Index (default, best for analytics)
CREATE TABLE fact.Orders
(
    OrderID BIGINT,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(12,2)
)
WITH
(
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX
);

-- Clustered Index (good for point lookups)
CREATE TABLE dim.DateDimension
(
    DateKey INT NOT NULL,
    FullDate DATE NOT NULL,
    Year INT,
    Quarter INT,
    Month INT,
    MonthName VARCHAR(20),
    Day INT,
    DayOfWeek INT,
    DayName VARCHAR(20),
    IsWeekend BIT,
    IsHoliday BIT
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED INDEX (DateKey)
);

-- Heap (good for staging/temporary tables)
CREATE TABLE staging.RawData
(
    RawID BIGINT IDENTITY(1,1),
    JsonData NVARCHAR(MAX),
    LoadDate DATETIME DEFAULT GETDATE()
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
);

-- Adding Non-Clustered Indexes
CREATE NONCLUSTERED INDEX IX_Customer_Region
ON dim.Customer(Region);

CREATE NONCLUSTERED INDEX IX_Product_Category
ON dim.Product(Category, SubCategory);

Partitioning

-- Create partitioned table
CREATE TABLE fact.SalesPartitioned
(
    SaleID BIGINT NOT NULL,
    DateKey INT NOT NULL,
    ProductKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    Quantity INT,
    Amount DECIMAL(12,2)
)
WITH
(
    DISTRIBUTION = HASH(CustomerKey),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION (DateKey RANGE RIGHT FOR VALUES
        (20230101, 20230401, 20230701, 20231001,
         20240101, 20240401, 20240701, 20241001))
);

-- Switch partition (fast data loading)
-- Create staging table with same structure
CREATE TABLE staging.SalesQ1
(
    SaleID BIGINT NOT NULL,
    DateKey INT NOT NULL,
    ProductKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    Quantity INT,
    Amount DECIMAL(12,2)
)
WITH
(
    DISTRIBUTION = HASH(CustomerKey),
    CLUSTERED COLUMNSTORE INDEX
);

-- Load data into staging table
-- Then switch partition
ALTER TABLE staging.SalesQ1
SWITCH TO fact.SalesPartitioned PARTITION 5;

-- Split partition for new data
ALTER TABLE fact.SalesPartitioned
SPLIT RANGE (20250101);

-- Merge partitions (combine old partitions)
ALTER TABLE fact.SalesPartitioned
MERGE RANGE (20230101);

---

Section 3: Data Loading Patterns

COPY Command (Recommended)

-- Basic COPY command
COPY INTO staging.Sales
FROM 'https://yourstorageaccount.dfs.core.windows.net/data/sales/*.parquet'
WITH (
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Managed Identity')
);

-- COPY with options
COPY INTO staging.Customers
FROM 'https://yourstorageaccount.dfs.core.windows.net/data/customers/'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,
    ENCODING = 'UTF8',
    DATEFORMAT = 'ymd',
    MAXERRORS = 10,
    ERRORFILE = 'https://yourstorageaccount.dfs.core.windows.net/errors/'
);

-- COPY with column mapping
COPY INTO fact.Sales (SaleID, ProductKey, CustomerKey, DateKey, Quantity, Amount)
FROM 'https://yourstorageaccount.dfs.core.windows.net/data/sales/*.parquet'
WITH (
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Managed Identity')
);

-- COPY from multiple files with pattern
COPY INTO staging.DailySales
FROM 'https://yourstorageaccount.dfs.core.windows.net/data/sales/2024/*/sales_*.parquet'
WITH (
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Managed Identity')
);

PolyBase (External Tables)

-- Create external data source
CREATE EXTERNAL DATA SOURCE AzureDataLake
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://data@yourstorageaccount.dfs.core.windows.net',
    CREDENTIAL = StorageCredential
);

-- Create external file format
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

-- Create external table
CREATE EXTERNAL TABLE ext.Sales
(
    SaleID BIGINT,
    ProductID INT,
    CustomerID INT,
    SaleDate DATE,
    Amount DECIMAL(12,2)
)
WITH (
    LOCATION = '/sales/2024/',
    DATA_SOURCE = AzureDataLake,
    FILE_FORMAT = ParquetFileFormat
);

-- Load via CTAS (Create Table As Select)
CREATE TABLE fact.Sales
WITH (
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT
    SaleID,
    ProductID AS ProductKey,
    CustomerID AS CustomerKey,
    CONVERT(INT, FORMAT(SaleDate, 'yyyyMMdd')) AS DateKey,
    Amount
FROM ext.Sales;

Incremental Loading Pattern

-- Create watermark table
CREATE TABLE etl.Watermark
(
    TableName VARCHAR(100),
    WatermarkColumn VARCHAR(100),
    WatermarkValue DATETIME2
)
WITH (DISTRIBUTION = REPLICATE);

-- Insert initial watermark
INSERT INTO etl.Watermark VALUES ('fact.Sales', 'ModifiedDate', '2024-01-01');

-- Incremental load procedure
CREATE PROCEDURE etl.IncrementalLoadSales
AS
BEGIN
    DECLARE @LastWatermark DATETIME2;
    DECLARE @NewWatermark DATETIME2 = GETUTCDATE();

    -- Get last watermark
    SELECT @LastWatermark = WatermarkValue
    FROM etl.Watermark
    WHERE TableName = 'fact.Sales';

    -- Load incremental data
    INSERT INTO fact.Sales
    SELECT
        SaleID,
        ProductKey,
        CustomerKey,
        DateKey,
        Quantity,
        Amount
    FROM staging.Sales
    WHERE ModifiedDate > @LastWatermark
      AND ModifiedDate <= @NewWatermark;

    -- Update watermark
    UPDATE etl.Watermark
    SET WatermarkValue = @NewWatermark
    WHERE TableName = 'fact.Sales';
END;

---

Section 4: Query Optimization

Explain Plans

-- View estimated execution plan
EXPLAIN
SELECT
    c.CustomerName,
    p.Category,
    SUM(f.TotalAmount) AS TotalSales
FROM fact.Sales f
JOIN dim.Customer c ON f.CustomerKey = c.CustomerKey
JOIN dim.Product p ON f.ProductKey = p.ProductKey
WHERE f.DateKey >= 20240101
GROUP BY c.CustomerName, p.Category;

-- View actual execution with statistics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT
    c.CustomerName,
    p.Category,
    SUM(f.TotalAmount) AS TotalSales
FROM fact.Sales f
JOIN dim.Customer c ON f.CustomerKey = c.CustomerKey
JOIN dim.Product p ON f.ProductKey = p.ProductKey
WHERE f.DateKey >= 20240101
GROUP BY c.CustomerName, p.Category;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Statistics Management

-- Create statistics on filter columns
CREATE STATISTICS Stats_Sales_DateKey ON fact.Sales(DateKey);
CREATE STATISTICS Stats_Sales_CustomerKey ON fact.Sales(CustomerKey);
CREATE STATISTICS Stats_Sales_ProductKey ON fact.Sales(ProductKey);

-- Create multi-column statistics
CREATE STATISTICS Stats_Sales_DateProduct
ON fact.Sales(DateKey, ProductKey);

-- Update statistics
UPDATE STATISTICS fact.Sales;

-- Update with full scan (more accurate)
UPDATE STATISTICS fact.Sales WITH FULLSCAN;

-- View statistics info
DBCC SHOW_STATISTICS ('fact.Sales', 'Stats_Sales_DateKey');

-- Check statistics age
SELECT
    t.name AS table_name,
    s.name AS stats_name,
    STATS_DATE(s.object_id, s.stats_id) AS stats_updated
FROM sys.stats s
JOIN sys.tables t ON s.object_id = t.object_id
WHERE t.name = 'Sales'
ORDER BY stats_updated DESC;

Query Hints and Optimization

-- Force specific distribution
SELECT *
FROM fact.Sales
OPTION (FORCE SHUFFLE);

-- Force broadcast join
SELECT f.*, c.CustomerName
FROM fact.Sales f
JOIN dim.Customer c ON f.CustomerKey = c.CustomerKey
OPTION (FORCE BROADCAST);

-- Label queries for monitoring
SELECT
    c.Region,
    SUM(f.TotalAmount) AS TotalSales
FROM fact.Sales f
JOIN dim.Customer c ON f.CustomerKey = c.CustomerKey
GROUP BY c.Region
OPTION (LABEL = 'Regional_Sales_Report');

-- Result set caching
SET RESULT_SET_CACHING ON;

SELECT
    Category,
    SUM(TotalAmount) AS TotalSales
FROM fact.Sales f
JOIN dim.Product p ON f.ProductKey = p.ProductKey
GROUP BY Category;

-- Check if result was cached
SELECT
    result_cache_hit,
    request_id
FROM sys.dm_pdw_exec_requests
WHERE [label] = 'Regional_Sales_Report';

Materialized Views

-- Create materialized view for common aggregations
CREATE MATERIALIZED VIEW mv.SalesByCategory
WITH (DISTRIBUTION = HASH(Category))
AS
SELECT
    p.Category,
    p.SubCategory,
    d.Year,
    d.Month,
    SUM(f.TotalAmount) AS TotalAmount,
    SUM(f.Quantity) AS TotalQuantity,
    COUNT_BIG(*) AS TransactionCount
FROM fact.Sales f
JOIN dim.Product p ON f.ProductKey = p.ProductKey
JOIN dim.DateDimension d ON f.DateKey = d.DateKey
GROUP BY p.Category, p.SubCategory, d.Year, d.Month;

-- Query uses materialized view automatically
SELECT
    Category,
    Year,
    SUM(TotalAmount) AS YearlyTotal
FROM fact.Sales f
JOIN dim.Product p ON f.ProductKey = p.ProductKey
JOIN dim.DateDimension d ON f.DateKey = d.DateKey
GROUP BY Category, Year;

-- Refresh materialized view
ALTER MATERIALIZED VIEW mv.SalesByCategory REBUILD;

-- Check materialized view state
SELECT
    name,
    is_valid,
    definition
FROM sys.materialized_views;

---

Section 5: Workload Management

Resource Classes

-- View available resource classes
SELECT * FROM sys.database_principals
WHERE type = 'R' AND name LIKE '%rc%';

-- Assign user to resource class
EXEC sp_addrolemember 'largerc', 'DataLoadUser';

-- View current user's resource class
SELECT
    r.name AS resource_class,
    m.name AS member_name
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name LIKE '%rc%';

-- Resource class memory allocation
SELECT
    wc.name AS resource_class,
    wc.min_percentage_resource,
    wc.max_percentage_resource,
    wc.cap_percentage_resource
FROM sys.workload_management_workload_classifiers wc;

Workload Groups and Classifiers

-- Create workload group for reporting
CREATE WORKLOAD GROUP ReportingWorkload
WITH (
    MIN_PERCENTAGE_RESOURCE = 10,
    MAX_PERCENTAGE_RESOURCE = 50,
    CAP_PERCENTAGE_RESOURCE = 50,
    REQUEST_MIN_RESOURCE_GRANT_PERCENT = 5,
    REQUEST_MAX_RESOURCE_GRANT_PERCENT = 25,
    IMPORTANCE = NORMAL,
    QUERY_EXECUTION_TIMEOUT_SEC = 3600
);

-- Create workload group for data loading
CREATE WORKLOAD GROUP DataLoadWorkload
WITH (
    MIN_PERCENTAGE_RESOURCE = 25,
    MAX_PERCENTAGE_RESOURCE = 100,
    CAP_PERCENTAGE_RESOURCE = 100,
    REQUEST_MIN_RESOURCE_GRANT_PERCENT = 25,
    IMPORTANCE = HIGH
);

-- Create classifier for reporting users
CREATE WORKLOAD CLASSIFIER ReportingClassifier
WITH (
    WORKLOAD_GROUP = 'ReportingWorkload',
    MEMBERNAME = 'ReportingUser',
    IMPORTANCE = NORMAL
);

-- Create classifier for data load users
CREATE WORKLOAD CLASSIFIER DataLoadClassifier
WITH (
    WORKLOAD_GROUP = 'DataLoadWorkload',
    MEMBERNAME = 'DataLoadUser',
    IMPORTANCE = HIGH
);

-- Classifier based on label
CREATE WORKLOAD CLASSIFIER DashboardClassifier
WITH (
    WORKLOAD_GROUP = 'ReportingWorkload',
    LABEL = 'Dashboard%',
    IMPORTANCE = HIGH
);

Monitoring Queries

-- View running queries
SELECT
    request_id,
    status,
    submit_time,
    start_time,
    total_elapsed_time / 1000.0 AS elapsed_seconds,
    [label],
    command,
    resource_class
FROM sys.dm_pdw_exec_requests
WHERE status NOT IN ('Completed', 'Failed', 'Cancelled')
ORDER BY submit_time DESC;

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

-- View data movement
SELECT
    request_id,
    step_index,
    pdw_node_id,
    distribution_id,
    type,
    status,
    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;

-- View waits
SELECT
    request_id,
    type,
    state,
    priority,
    object_type,
    object_name,
    request_time
FROM sys.dm_pdw_waits
WHERE request_id = 'QID12345';

---

Section 6: Maintenance Operations

Table Maintenance

-- Rebuild columnstore indexes
ALTER INDEX ALL ON fact.Sales REBUILD;

-- Reorganize columnstore indexes (less resource intensive)
ALTER INDEX ALL ON fact.Sales REORGANIZE;

-- Check index health
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10;

-- Check columnstore segment quality
SELECT
    OBJECT_NAME(rg.object_id) AS table_name,
    rg.partition_number,
    rg.state_desc,
    rg.total_rows,
    rg.deleted_rows,
    rg.size_in_bytes / 1024.0 / 1024.0 AS size_mb
FROM sys.column_store_row_groups rg
WHERE OBJECT_NAME(rg.object_id) = 'Sales'
ORDER BY partition_number, row_group_id;

Scale Operations

-- Scale pool (via Azure CLI or Portal)
-- az synapse sql pool update --name mypool --workspace-name myworkspace --performance-level DW200c

-- Pause pool (cost savings)
-- az synapse sql pool pause --name mypool --workspace-name myworkspace

-- Resume pool
-- az synapse sql pool resume --name mypool --workspace-name myworkspace

-- Check current DWU level
SELECT COUNT(*) * 100 AS approximate_dwu_level
FROM sys.dm_pdw_nodes
WHERE type = 'COMPUTE';

---

Exercises

Exercise 1: Design Table Distribution

Create a star schema with appropriate distribution strategies for a retail analytics scenario.

Exercise 2: Optimize Data Loading

Implement an incremental loading pattern using COPY command and watermarks.

Exercise 3: Query Performance Tuning

Take a slow query and optimize it using statistics, indexes, and materialized views.

---

Best Practices Summary

Practice	Recommendation
Distribution	HASH for large facts, REPLICATE for small dims
Indexing	Columnstore for analytics, B-tree for lookups
Partitioning	By date for time-series data, 10-100 partitions
Loading	COPY command for best performance
Statistics	Create on filter/join columns, update regularly
Maintenance	Rebuild indexes weekly, update stats daily

---

Next Steps

• Continue to Tutorial 11: Power BI Integration
• Explore SQL Performance Best Practices
• Review Dedicated SQL Troubleshooting