Analytics Optimization with Columnstore Indexes in Microsoft SQL Server: Optimizing OLAP Workloads

Length: 299 pages
Edition: 1
Language: English
Publisher: Apress
Publication Date: 2022-03-13
ISBN-10: 1484280474
ISBN-13: 9781484280478
Sales Rank: #702591 (See Top 100 Books)

Meet the challenge of storing and accessing analytic data in SQL Server in a fast and performant manner. This book illustrates how columnstore indexes can provide an ideal solution for storing analytic data that leads to faster performing analytic queries and the ability to ask and answer business intelligence questions with alacrity. The book provides a complete walk through of columnstore indexing that encompasses an introduction, best practices, hands-on demonstrations, explanations of common mistakes, and presents a detailed architecture that is suitable for professionals of all skill levels.

With little or no knowledge of columnstore indexing you can become proficient with columnstore indexes as used in SQL Server, and apply that knowledge in development, test, and production environments. This book serves as a comprehensive guide to the use of columnstore indexes and provides definitive guidelines. You will learn when columnstore indexes should be used, and the performance gains that you can expect. You will also become familiar with best practices around architecture, implementation, and maintenance. Finally, you will know the limitations and common pitfalls to be aware of and avoid.

As analytic data can become quite large, the expense to manage it or migrate it can be high. This book shows that columnstore indexing represents an effective storage solution that saves time, money, and improves performance for any applications that use it. You will see that columnstore indexes are an effective performance solution that is included in all versions of SQL Server, with no additional costs or licensing required.

What You Will Learn

Implement columnstore indexes in SQL Server
Know best practices for the use and maintenance of analytic data in SQL Server
Use metadata to fully understand the size and shape of data stored in columnstore indexes
Employ optimal ways to load, maintain, and delete data from large analytic tables
Know how columnstore compression saves storage, memory, and time
Understand when a columnstore index should be used instead of a rowstore index
Be familiar with advanced features and analytics

Who This Book Is For

Database developers, administrators, and architects who are responsible for analytic data, especially for those working with very large data sets who are looking for new ways to achieve high performance in their queries, and those with immediate or future challenges to analytic data and query performance who want a methodical and effective solution

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
    What Is This Book?
    Intended Audience
    Why Columnstore Indexes?
    Contacting the Author
Chapter 1: Introduction to Analytic Data in a Transactional Database
    Where Should Analytic Data Reside?
    Analytic Data Size
    Analytic Data Structure
    Analytic Data Sources
        Data Warehouse
        Unstructured Data
        Third-Party Analytics Software
        OLAP Data in an OLTP Table in a Transactional Database
        OLAP Data in an Analytic Table in a Transactional Database
Chapter 2: Transactional vs. Analytic Workloads
    Transactional Data
    Analytic Data
    The Need for Two Systems
    Building Better Analytic Data Structures
Chapter 3: What Are Columnstore Indexes?
    The Limits of Transactional Data Storage
    Introducing Columnstore Indexes
    Benefits of Columnstore Indexes in SQL Server
        Native Analytic Data in SQL Server
        Scalability
        Exceptional Compression
        Faster Analytic Reads
        Faster Data Loads
Chapter 4: Columnstore Index Architecture
    Sample Data
    Rowgroups and Segments
    The Delta Store
    The Delete Bitmap
    Nonclustered Columnstore Index Architecture
    Physical Data on Pages
    Summarizing Differences
Chapter 5: Columnstore Compression
    Basics of Columnstore Compression
    Columnstore Compression Algorithms
        Value Encoding
        Dictionary Encoding
        Should String Data Be Normalized?
        Row Order (Vertipaq) Optimization
        Other Compression Algorithms
        Columnstore Archive Compression
        The Compression Life Cycle
Chapter 6: Columnstore Metadata
    Available Columnstore Metadata
        Rowgroup Metadata
            Partition_number
            Delta_store_hobt_id
            State and State_Description
            Total_rows
            Deleted_rows
            Size_in_bytes
        Segment Metadata
            Encoding_type
            Row_Count
            Has_nulls
            Base_id and Magnitude
            Min_data_id and Max_data_id
            Null_value
            On_disk_size
        Rowgroup Physical Metadata
            State_desc
            Total_rows, Deleted_rows, Size_in_bytes
            Trim_reason_desc
            Transition_to_compressed_state_desc
            Has_vertipaq_optimization
            Created_time
        Rowgroup Operational Statistics
            Index_scan_count
            Scan_count
            Delete_buffer_scan_count
            Row_group_lock_count, Row_group_lock_wait_count, and Row_group_lock_wait_in_ms
            Returned_row_count
        Columnstore Index Memory Usage
            Object_type_desc
            Access_count
            Object_load_time
        Internal Columnstore Index Objects
            Internal_object_type_desc
Chapter 7: Batch Execution
    Row Mode Execution
    Batch Mode Execution
    How Does Batch Mode Work?
    Batch Mode vs. Row Mode Performance
Chapter 8: Bulk Loading Data
    Bulk Load Processes Explained
    Bulk Loading into Columnstore Indexes
    Performance of Bulk Loading into Columnstore Indexes
    Trickle Insert vs. Staged Insert
    Other Data Load Considerations
        Drop Nonclustered Indexes During Data Loads
        Columnstore Reorganize Operations with Each Data Load
    Summary
Chapter 9: Delete and Update Operations
    The Cost of Modifying Data
    Delete Operations
    Update Operations
Chapter 10: Segment and Rowgroup Elimination
    Segment Elimination
    Rowgroup Elimination
    Combining Segment and Rowgroup Elimination
Chapter 11: Partitioning
    Maintain Hot/Warm/Cold Data
    Faster Data Movement/Migration
    Partition Elimination
    Database Maintenance
    Partitioning in Action
    Partitioning Guidelines
        Partition and Rowgroup Sizing
        Partition Column Choice
        Storage Choice
    Additional Benefits
Chapter 12: Nonclustered Columnstore Indexes on Rowstore Tables
    Use Rowstore Indexes
    Separate OLAP and OLTP Processes
    Nonclustered Columnstore Indexes
    Managing Hot, Warm, and Cold Transactional Data
        Compression Delay
        Filtered Nonclustered Columnstore Indexes
        Code Changes
    Vertipaq Optimization for Nonclustered Columnstore Indexes
    Testing Nonclustered Columnstore Indexes
    Don’t Forget to Drop Unneeded Indexes!
Chapter 13: Nonclustered Rowstore Indexes on Columnstore Tables
    Using Nonclustered Rowstore Indexes
    Enforcing Constraints
    Filtered Nonclustered Rowstore Indexes
    Enabling Vertipaq Optimization
        Add Filters to Nonclustered Rowstore Indexes
        Perform Periodic Index Maintenance
    Indexed Views
    Compression for Nonclustered Rowstore Indexes
    Nonclustered Rowstore Index Guidance
Chapter 14: Columnstore Index Maintenance
    What Causes Fragmentation?
    How Much Fragmentation Is Too Much?
        Quantifying Deleted Rows
        Detailing Unordered Data
    The No-Maintenance Columnstore Index
    Columnstore Reorganize
    Reorganize to Remove Delta Rowgroups
    Columnstore Rebuild
    Columnstore Reorder and Rebuild
    Columnstore Index Maintenance by Partition
    Index Maintenance in Nonclustered Columnstore Indexes
Chapter 15: Columnstore Index Performance
    Columnstore Metadata Reads
    Columnstore Data Reads
    Memory Sizing
    Dictionary Size and Dictionary Pressure
        Normalizing Wide Columns
        Add or Change the Columnstore Sorting Column
        Partitioning
    Columnstore Indexes on Temporary Tables
    Memory-Optimized Columnstore Indexes
        Demonstrating Memory-Optimized Columnstore Indexes
    Optimization Strategies
Index

.NET C & C++ Windows Programming Database Storage & Design Microsoft Programming SQL Server

Donate to keep this site alive

To access the Link, solve the captcha.

How to download source code?

1. Go to: https://github.com/Apress

2. In the Find a repository… box, search the book title: Analytics Optimization with Columnstore Indexes in Microsoft SQL Server: Optimizing OLAP Workloads, sometime you may not get the results, please search the main title.