📊

Features of Dedicated SQL Pool Explained

Aug 4, 2024,

View transcript

Dedicated SQL Pool Features

Introduction

  • Discussing additional features of Dedicated SQL Pool, focusing on:
    • Workload Management
    • Result Set Caching
    • Partitioning

Workload Management

  • Purpose: Handle concurrent queries efficiently.
  • Concurrent Queries: Queries performed by internal processes and users simultaneously.

Classification of Queries

  • Classification: Mapping submitted queries to workload groups based on rules.
  • Workload Groups: Defined sets of resources for specific queries. Categories might include:
    • ETL processes
    • Finance department queries (high priority)
    • Ad hoc queries (lower priority)

Classifier Rules

  • Classification can be based on:
    • Database username
    • Database role
    • Entra login
    • Time of day

Resource Reservation

  • Minimum Memory: Set for workload groups to ensure specific resources are available (e.g., at least 10%).
    • Parameter: min percentage resource
  • Maximum Memory: Limits the usage of resources (e.g., not more than 50%).
    • Parameter: cap percentage resource
  • Memory per Query: Amount of memory allocated to each query from a workload group.
    • Parameter: request min resource grant percent

Workload Importance

  • Priority Handling: Determines execution order of queries:
    • Default: First In, First Out (FIFO)
    • Can assign higher priorities to certain workload groups.
    • Levels: High, Normal, Low, Above Normal, Below Normal

Result Set Caching

  • Purpose: Improve query performance by caching results.
  • Enabling Caching: Can be enabled at session level or pool level.
  • Impact of Caching:
    • First execution stores result, subsequent executions retrieve from cache.
    • Eviction occurs if the cache is unused for 48 hours, exceeds size limits, or if source data changes.
    • Non-deterministic queries (e.g., using getdate()) are not cached.

Partitioning

  • Purpose: Improve maintenance and performance for large tables (primarily fact tables).

Benefits of Partitioning

  • Maintenance: Facilitates quicker data removal operations (e.g., sliding window scenario).
  • Query Optimization: Optimizer can skip unnecessary partitions based on date filtering.

Partitioning Process

  • Switching Partitions: Move partitions quickly without locks.
  • Sliding Window: Continuously manage partitions to maintain a fixed range of data (e.g., retain 3 years).
  • Best Practices:
    • Partition large tables (fact tables), not dimension tables.
    • Limit partitions—preferably < 100.
    • Ensure partitions have sufficient rows (> 1 million) to benefit from columnstore index.

Syntax for Partitioning

  • Use CREATE TABLE with PARTITION clause. Example:
CREATE TABLE [table_name] (
    [columns]
)
WITH (
    DISTRIBUTION = HASH([column]),
    PARTITION = RANGE RIGHT FOR VALUES (
        DATE '2021-01-01',
        DATE '2021-02-01',
        DATE '2021-03-01'
    )
)

T-SQL Commands

  • Pivot/Unpivot: Allows transformation of data for better reporting.
    • Example: Pivot to convert types to column headers.
  • Identity Property: Automatically generate surrogate keys in tables.
  • Data Skew: Use SHOWSPACEUSED to analyze distribution of rows across partitions.

Conclusion

  • Covered:
    • Workload Management and query concurrency.
    • Result Set Caching for performance enhancement.
    • Partitioning for maintenance and performance.
    • Key TSQL commands for data manipulation.

Full transcript