Optimizing Query Performance with Columnstore Indexes

Demo database: WiredBrainCoffee

Table of Contents

1. Course Overview
2. Introduction to Columnstore Indexes

• 2.1 Why are Columnstore Indexes needed?
• 2.2 What is a Columnstore Index?
• 2.3 Demo: Creation of the demonstration environment

3. Examining the structure of Columnstore Indexes

• 3.1 The two storage methods: Rowstore vs Columnstore
• 3.2 How is data physically stored?
• 3.3 History of Columnstore Indexes (2012–2022)
• 3.4 Advantages of Columnstore Indexes
• 3.5 When to choose or avoid a Columnstore Index?
• 3.6 Demo: Index size comparison

4. Creating your first Columnstore Index

• 4.1 Types of Columnstore Indexes: Clustered vs Non-Clustered
• 4.2 Which index to choose?
• 4.3 Row Groups and Segments in detail
• 4.4 Demo: Creation of Columnstore Indexes
• 4.5 Demo: Segment Elimination
• 4.6 Demo: Ordered Columnstore Index (SQL Server 2022)

5. Performance optimization of Columnstore Indexes

• 5.1 Batch Mode Processing
• 5.2 Aggregate Pushdown
• 5.3 String Predicate Pushdown
• 5.4 Adaptive Query Joins (Intelligent Query Processing)
• 5.5 Demo: Batch Mode
• 5.6 Demo: Batch Mode Part 2
• 5.7 Demo: Batch Mode on Rowstore (SQL Server 2019)
• 5.8 Demo: Aggregate Pushdown
• 5.9 Demo: String Predicate Pushdown

6. Index Health Monitoring and Maintenance

• 6.1 Impact of data modifications on a Columnstore Index
• 6.2 Fragmentation detection
• 6.3 Fragmentation Elimination
• 6.4 Demo: Data modifications
• 6.5 Demo: Fragmentation detection
• 6.6 Demo: Fragmentation Elimination

7. Put into practice: Proof of Concept and advanced use cases

• 7.1 Construction of a Proof of Concept (POC)
• 7.2 Demo: POC — Part 1
• 7.3 Demo: POC — Part 2
• 7.4 Compression of a large static table
• 7.5 Bulk Loading and Columnstore Indexes
• 7.6 Shades between index types

8. Resources and references

1. Course Overview

This course introduces and explores in depth Columnstore Indexes in SQL Server — one of the most powerful features for improving the performance of analytical queries on large volumes of data.

Problem addressed

As a data environment grows, queries that used to run in seconds can become slow in weeks or months. Users receive complaints about reports that take too long to generate. Columnstore Indexes are a solution to these performance issues.

Prerequisites

• Knowledge of SQL Server
• Experience writing T-SQL queries

Major themes covered

• How do Columnstore Indexes differ from Rowstore Indexes?
• What makes Columnstore so fast?
• Choosing between a Clustered and a Non-Clustered Columnstore Index
• Determine if a Columnstore Index is right for your environment

2. Introduction to Columnstore Indexes

Module duration: 14m 45s

2.1 Why are Columnstore Indexes needed?

Data is a company’s most valuable asset. It allows you to make informed decisions for the future based on the past: projecting sales in a region, estimating working hours for a project, etc.

Data growth

Most datasets continually grow:

• Tracking applications (health, physical activity) generate data every day
• Social networks (likes, comments) store billions of interactions
• Data retention policies require companies to retain customer data for 5-10 years
• Product owners expect applications to remain performant, even with tables with hundreds of millions of rows

It is the role of data professionals to ensure that end users do not experience performance issues, even when tables grow significantly.

Two main reasons to use a Columnstore Index

1. Data grows and queries become slow — once-fast queries on modestly sized tables become problematic as volumes increase.
2. Analytical queries (aggregations, reports) on large tables — need to consult millions or hundreds of millions of rows efficiently.

2.2 What is a Columnstore Index?

Microsoft Definition: A technology for storing, retrieving, and managing data using a columnar data format called columnstore.

Simplified definition: A type of index that stores and retrieves data by column rather than row, allowing analysis.

Short definition: A column-based index used for quick scanning.

Column storage vs row storage

Columnar storage shines best in data warehouses and analytics platforms.

2.3 Demo: Creation of the demonstration environment

The demo file creates the WiredBrainCoffee database with a schema representing a sales system. This database is used throughout the course.

WiredBrainCoffee database structure

-- Vérification et suppression de la base existante
USE [master];
GO

IF DATABASEPROPERTYEX ('WiredBrainCoffee','Version') IS NOT NULL
BEGIN
    ALTER DATABASE WiredBrainCoffee SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
    DROP DATABASE WiredBrainCoffee;
END
GO

-- Création de la base de données (nécessite ~20 GB d'espace disque)
CREATE DATABASE WiredBrainCoffee
 ON PRIMARY 
( NAME = N'WiredBrainCoffee',
  FILENAME = N'C:\Pluralsight\SQLFiles\WiredBrainCoffee.mdf',
  SIZE = 5000000KB,
  FILEGROWTH = 500000KB )
 LOG ON 
( NAME = N'WiredBrainCoffee_log',
  FILENAME = N'C:\Pluralsight\SQLFiles\WiredBrainCoffee_log.ldf',
  SIZE = 1000000KB,
  FILEGROWTH = 1000000KB )
GO

ALTER DATABASE WiredBrainCoffee SET RECOVERY SIMPLE;
GO

Tables created

• Sales.SalesPersonLevel: Seller levels (Manager, Senior Staff, Staff, Associate)
• Sales.SalesPerson: 50,000 salespeople with first names, last names, salaries, level, active status, start date
• Sales.SalesTerritoryStatus: Territory statuses (Active, Closing, In Review)
• Sales.SalesTerritory: 10 territories (Northwest, Northeast, France, Germany, Australia, etc.)
• Sales.SalesOrderStatus: Order statuses (Complete, In Progress, Returned)
• Sales.SalesOrder: 30 million rows — main table used for performance demonstrations

Auxiliary number table (dbo.Numbers)

Inspired by a script by Aaron Bertrand, this table is used for massive data generation:

SELECT TOP (25000000) Number = CONVERT(INT, ROW_NUMBER() OVER (
    ORDER BY s1.object_id))
INTO dbo.Numbers
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
CROSS JOIN sys.all_objects AS s3
GO

Generation of 30 million lines of Sales.SalesOrder

Orders are generated with random sales amounts, dates between 2016 and 2022, sellers and varied territories.

3. Examining the structure of Columnstore Indexes

Module duration: 21m 19s

3.1 The two storage methods: Rowstore vs Columnstore

Rowstore (traditional method)

• SQL Server stores all columns in a row together on 8 KB pages
• A table or index can contain a few pages or billions depending on the size
• Individual columns can be stored separately for certain types (e.g. nvarchar(max) above a certain size)
• Data is organized horizontally: all columns in a row are on the same page

Columnstore (columnar method)

• SQL Server still uses 8 KB pages, but columns are stored separately
• If we create a Non-Clustered Columnstore Index on two columns (SalesDate and SalesAmount), these two columns are stored individually
• Unlike B-Tree which stores both columns together
• Data is organized vertically: each column is processed independently

Important rule

There can only be one Columnstore Index per table (clustered or non-clustered).

Key Terminology

3.2 How is data physically stored?

Example with Rowstore (B-Tree)

Let’s imagine a table with 5 columns: Id, FirstName, LastName, SalesDate, SalesAmount. With a Clustered Index on Id:

• SQL distributes rows across pages by storing all columns together
• For a SELECT SUM(SalesAmount) query, SQL must read all pages of the table, even if only the SalesAmount column is needed

Even with a Non-Clustered Index on SalesAmount with a GROUP BY, SQL often has to do a Key Lookup to retrieve the other columns.

Example with Columnstore

With a Non-Clustered Columnstore Index on SalesAmount and SalesDate:

• For more than 1,000,000 rows, SQL takes the data and compresses each column individually into segments
• 8 KB pages are still used, but data is stored in compressed objects called segments
• For SELECT SUM(SalesAmount), SQL only reads SalesAmount segments — a huge performance gain on tables with dozens of columns

Visual structure

Row Group 1  [Segment: SalesDate | Segment: SalesPerson | Segment: SalesAmount | Segment: SalesTerritory]
Row Group 2  [Segment: SalesDate | Segment: SalesPerson | Segment: SalesAmount | Segment: SalesTerritory]
Row Group 3  [Segment: SalesDate | Segment: SalesPerson | Segment: SalesAmount | Segment: SalesTerritory]
...
Delta Store  [Lignes < 1 048 576 qui attendent la compression]

Each segment also contains the min and max values of its data, which allows segment elimination when running filtered queries.

What happens when creating a Columnstore Index

1. SQL creates Row Groups, each ideally containing ~1,048,576 rows with all index columns
2. Columns in each Row Group are individually encoded and compressed into Segments
3. Segments are stored in the same location as LOB objects (varchar(max), XML)
4. Residual data (insufficient to form a complete Row Group) is placed in the Delta Store
5. Every 5 minutes, the Tuple Mover checks if any Row Groups in the Delta Store qualify for compression

Key Takeaways

• Maximizing the number of rows in Row Groups is crucial for performance
• When doing a bulk INSERT of a few hundred thousand rows, SQL generally creates several small, fragmented Row Groups
• The reason for truncating a Row Group is visible via the trim_reason_desc column in the DMV sys.dm_db_column_store_row_group_physical_stats

3.3 History of Columnstore Indexes (2012–2022)

Recommendation: If you need to choose a version to migrate and get the maximum Columnstore Indexes, SQL Server 2019 is the first choice. SQL Server 2022 brings the particularly powerful Ordered Columnstore.

3.4 Advantages of Columnstore Indexes

1. Dramatically improved performance

Microsoft claims that Columnstore Indexes can deliver up to 100x better performance on analytical queries compared to traditional B-Tree Indexes. This particularly applies to aggregations on columns (SUM, AVG, MIN, MAX, COUNT).

2. Advanced compression

Several compression methods are available:

• Value Scale (Simple Encoding): part of the value is removed and reapplied by a mathematical formula
• Dictionary Encoding: character strings are converted to integers to save space
• RLE (Run-Length Encoding): repeating values are stored only once with a counter

These methods allow more data to reside in memory, speeding up access.

3. Elimination segment

When filters are applied (WHERE), SQL Server may eliminate entire Row Groups without reading them. Each segment stores the min and max values ​​of its data. If the filtered value is outside the min-max range of a segment, SQL skips that segment entirely.

Important: Segment Elimination requires filters in queries. Without filter, all segments are played.

4. Batch Mode Processing

SQL Server can process ~900 rows at a time in Batch Mode (as opposed to Row Mode which processes one row at a time). This significantly reduces CPU overhead.

• In SQL 2016 and 2017, Batch Mode requires a Columnstore Index
• In SQL 2019 and 2022, Batch Mode is available for tables without Columnstore Index (Batch Mode on Rowstore)

3.5 When to choose or avoid a Columnstore Index?

Favorable cases — Choosing a Columnstore Index

Bad cases — Avoiding a Columnstore Index

3.6 Demo: Index size comparison

This demonstration compares a Non-Clustered Columnstore Index to an equivalent B-Tree Non-Clustered Index, including with PAGE compression applied to the Rowstore.

-- Nettoyage du cache (NE PAS utiliser en production !)
DBCC DROPCLEANBUFFERS;
GO

-- Requête de test : résumé des ventes par vendeur et par année
SELECT SUM(so.SalesAmount) AS SalesAmount,
       CONCAT(sp.Lastname, ', ', sp.FirstName) AS SalesPerson,
       YEAR(so.SalesDate) AS SalesYear
FROM Sales.SalesOrder so
    INNER JOIN Sales.SalesPerson sp ON sp.Id = so.SalesPerson
WHERE so.SalesDate >= '1/1/2020'
      AND so.SalesDate <= '12/31/2023'
GROUP BY sp.LastName, sp.FirstName, YEAR(so.SalesDate);
GO

Without indexes, this query takes approximately 30 seconds on 30 million rows.

-- Suppression des indexes existants
DROP INDEX IF EXISTS CS_SalesOrder ON Sales.SalesOrder;
DROP INDEX IF EXISTS IX_SalesOrder_SalesDate ON Sales.SalesOrder;
GO

-- Création d'un Non-Clustered B-Tree Index
CREATE NONCLUSTERED INDEX IX_SalesOrder_SalesDate
ON Sales.SalesOrder (SalesDate)
INCLUDE (SalesPerson, SalesAmount);
GO

-- Création du Non-Clustered Columnstore Index équivalent
-- (prend environ 30 secondes)
CREATE NONCLUSTERED COLUMNSTORE INDEX CS_SalesOrder
ON Sales.SalesOrder (SalesDate, SalesPerson, SalesAmount);
GO

-- Comparaison des tailles
SELECT i.[name] AS IndexName,
       SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM [sys].[dm_db_partition_stats] AS s
    INNER JOIN [sys].[indexes] AS i
        ON s.[object_id] = i.[object_id]
           AND s.[index_id] = i.[index_id]
WHERE i.[name] IN ('CS_SalesOrder', 'IX_SalesOrder_SalesDate')
GROUP BY i.[name]
ORDER BY i.[name];
GO

Typical results (without Rowstore compression)

-- Application de la compression PAGE au B-Tree Index
ALTER INDEX IX_SalesOrder_SalesDate
ON Sales.SalesOrder
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);
GO

Results after PAGE compression of the B-Tree

Even with PAGE compression, the Columnstore Index remains nearly half as small as the compressed B-Tree.

Conclusion: The Columnstore Index offers superior compression to the B-Tree Index even with PAGE compression, while providing massive performance gains.

4. Creating your first Columnstore Index

Module duration: 30m 58s

4.1 Types of Columnstore Indexes: Clustered vs Non-Clustered

Clustered Columnstore Index (CCI)

• All columns in the table are converted to columnstore format
• No primary key specified in index definition
• Data is unsorted by default (unless you use the ORDER clause available in SQL Server 2022)
• Data resides at leaf level (like B-Tree Clustered Indexes)
• The entire table becomes the Columnstore Index
• Considerable space saving: 5x to 10x compared to a Clustered Rowstore with PAGE compression

Non-Clustered Columnstore Index (NCCI)

• Specify only the columns to include
• A copy of data of selected columns is retained in columnstore format
• Main table structure remains intact (B-Tree)
• Allows having secondary B-Tree indexes on the same table – Ideal for hybrid OLTP environments
• Adds additional storage, but often smaller than equivalent B-Trees

Comparison

4.2 Which index to choose?

Before choosing, ask questions about workload and environment:

Key questions to ask

1. Do most queries do singleton lookups? (return 1-2 rows per ID)

• If yes → the Columnstore does not bring any benefit

2. How often does the data change?

• Inserts only? Frequent updates? Mass deletions (pruning)?

3. Is this a Data Warehouse or OLTP environment?

Characteristics of a Data Warehouse → Clustered Columnstore Index

• Data does not change often
• ETL processes load data in batch loads
• Queries are analytical, with aggregations over large ranges

OLTP Features → Non-Clustered Columnstore Index

• Users insert individual orders (trickle inserts)
• Main table structure is used for transactional lookups
• B-Tree indexes are required for OLTP operations

Microsoft Recommendation

For Hybrid OLTP (Real-Time Operational Analytics) applications, Microsoft recommends using a Non-Clustered Columnstore Index on operational tables.

4.3 Row Groups and Segments in detail

Delta Store

The Delta Store is a temporary storage area for rows that do not yet qualify for compression into a full Row Group.

• Rows inserted in small batches (<102,400 rows for NCCI) go to Delta Store
• Delta Store uses B-Tree storage (rowstore), not columnar format
• There may be several Row Groups in OPEN (Delta Store active) or CLOSED (Delta Store ready for compression) state

Row Group States

Truncation reasons (trim_reason_desc)

Tuple Mover

The Tuple Mover is a background thread that:

• Runs approximately every 5 minutes
• Checks if any Row Groups in the Delta Store qualify for compression
• Compress and encode Delta Row Groups into Segments
• From SQL Server 2019: Tuple Mover includes a merge process that combines small Row Groups once they reach a certain threshold (on Clustered Columnstore Indexes)

Note: The Tuple Mover compresses Delta Row Groups, but does nothing about deleted rows.

DMV to inspect Row Groups

SELECT object_name(i.object_id) AS TableName,
       i.name AS IndexName,
       i.type_desc AS IndexType,
       rg.state_desc AS StateDescription,
       rg.total_rows AS TotalRows,
       rg.deleted_rows AS DeletedRows,
       rg.trim_reason_desc AS TrimReason
FROM [sys].[indexes] AS i
    JOIN [sys].[dm_db_column_store_row_group_physical_stats] AS rg
        ON i.object_id = rg.object_id
           AND i.index_id = rg.index_id;
GO

Degree of parallelism (MAXDOP) during creation

The number of threads used when creating a Columnstore Index influences the distribution of data in Row Groups. With MAXDOP=2, each thread creates its own Row Groups, which can create less full Row Groups. With MAXDOP = 1, compression is sequential and produces more uniform Row Groups.

To check the available schedulers:

SELECT scheduler_id AS SchedulerId,
       cpu_id AS CPUId,
       status AS CurrentStatus,
       is_online AS IsOnline
FROM [sys].[dm_os_schedulers]
WHERE status = 'VISIBLE ONLINE';

4.4 Demo: Creation of Columnstore Indexes

File: 04/demos/m4-demos/01_Creating_Columnstore_Indexes.sql

Attempting to create a CCI on a table with Clustered B-Tree Index

-- Ceci va échouer : impossible de créer un CCI si un Clustered B-Tree Index existe déjà
CREATE CLUSTERED COLUMNSTORE INDEX CCS_SalesOrder ON Sales.SalesOrder;
GO

SQL Server returns an error because the table already has a Clustered Index rowstore. A CCI replaces the Clustered Index — you must first remove the Primary Key Constraint.

Creating an NCCI

-- Création initiale
CREATE NONCLUSTERED COLUMNSTORE INDEX CS_SalesOrder2
ON Sales.SalesOrder (SalesDate, SalesPerson, SalesAmount);
GO

-- Ajout d'une colonne (nécessite DROP + RECREATE ou DROP_EXISTING)
DROP INDEX IF EXISTS CS_SalesOrder ON Sales.SalesOrder;
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX CS_SalesOrder
ON Sales.SalesOrder (SalesDate, SalesPerson, SalesAmount, SalesTerritory);
GO

-- Modification avec DROP_EXISTING (plus efficace)
CREATE NONCLUSTERED COLUMNSTORE INDEX CS_SalesOrder
ON Sales.SalesOrder
(
    SalesDate, SalesPerson, SalesAmount, SalesTerritory, OrderDescription
)
WITH (DROP_EXISTING = ON);
GO

Limitation: The nvarchar(max) data type (like OrderDescription) can be included in a Columnstore Index in recent versions of SQL Server, but with compression limitations.

Inspection of Row Groups after creation

SELECT object_name(i.object_id) AS TableName,
       i.name AS IndexName,
       i.type_desc AS IndexType,
       rg.state_desc AS StateDescription,
       rg.total_rows AS TotalRows,
       rg.trim_reason_desc AS TrimReason
FROM [sys].[indexes] AS i
    JOIN [sys].[dm_db_column_store_row_group_physical_stats] AS rg
        ON i.object_id = rg.object_id
           AND i.index_id = rg.index_id;
GO

Creation with limited MAXDOP

CREATE NONCLUSTERED COLUMNSTORE INDEX CS_SalesOrder
ON Sales.SalesOrder (SalesDate, SalesPerson, SalesAmount, SalesTerritory)
WITH (MAXDOP = 2, DROP_EXISTING = ON);
GO

With MAXDOP = 2, Row Groups are created by two threads in parallel. This can produce less populated Row Groups. Inspect the TrimReason to confirm.

4.5 Demo: Segment Elimination

File: 04/demos/m4-demos/02_Exploring_Segment_Elimination.sql

Segment Elimination is one of the key performance mechanisms of Columnstore Indexes. SQL Server uses the min/max metadata of each segment to decide whether a segment should be read or can be skipped.

Enabling IO statistics

SET STATISTICS IO ON;

SELECT SUM(SalesAmount) AS SalesAmount,
       YEAR(SalesDate) AS SalesYear
FROM Sales.SalesOrder
WHERE SalesDate >= '20220101'
      AND SalesDate <= '20221231'
GROUP BY YEAR(SalesDate);
GO

Looking at the execution plan, we can see the number of segments read vs segments discarded in the properties of the Columnstore Index Scan operator.

Segment metadata inspection

SELECT t.[Name] AS TableName,
       i.[Name] AS IndexName,
       c.[Name] AS ColumnName,
       se.segment_id AS SegmentId,
       se.row_count AS SegmentRowCount,
       se.min_data_id AS MinRowValue,
       se.max_data_id AS MaxRowValue
FROM [sys].[column_store_segments] AS se
    INNER JOIN [sys].[partitions] AS p ON p.hobt_id = se.hobt_id
    INNER JOIN [sys].[indexes] AS i ON i.OBJECT_ID = p.OBJECT_ID
        AND i.index_id = p.index_id
    INNER JOIN [sys].[index_columns] AS ic ON ic.OBJECT_ID = i.OBJECT_ID
        AND ic.index_id = i.index_id
        AND ic.index_column_id = se.column_id
    INNER JOIN [sys].[columns] AS c ON c.OBJECT_ID = p.OBJECT_ID
        AND c.column_id = ic.column_id
    INNER JOIN [sys].[tables] AS t ON t.object_id = i.object_id
WHERE c.[Name] = 'SalesDate';
GO

Why Segment Elimination may not work on an NCCI?

In a Non-Clustered Columnstore Index, the data is not ordered by column — it is inserted in the order it was in the table (or in the order the index was created with MAXDOP). Thus, the min-max ranges of each segment may overlap across all years, preventing elimination.

For example, if SalesDate data is randomly distributed across Row Groups, each segment may contain dates from 2016 to 2022. A filter on 2022 cannot eliminate segments that also contain data from 2022 mixed with other years.

For Segment Elimination to be effective, the data must be ordered in the index, which is possible with an Ordered Clustered Columnstore Index (SQL Server 2022).

4.6 Demo: Ordered Columnstore Index (SQL Server 2022)

File: 04/demos/m4-demos/03_Segment_Elimination_2022.sql

SQL Server 2022 introduces the ability to create an Ordered Clustered Columnstore Index, which dramatically improves Segment Elimination.

Attempt on an NCCI (failed)

-- Ceci retourne une erreur : impossible d'ordonner un NCCI
CREATE NONCLUSTERED COLUMNSTORE INDEX CS_SalesOrder_SalesDate
ON Sales.SalesOrder (SalesDate, SalesPerson, SalesAmount, SalesTerritory)
ORDER (SalesDate);
GO

Procedure to create an Ordered CCI

-- Étape 1 : Supprimer le Clustered B-Tree Index
ALTER TABLE Sales.SalesOrder
DROP CONSTRAINT [PK_SalesOrder_Id];
GO

-- Étape 2 : Supprimer le NCCI existant
DROP INDEX IF EXISTS [CS_SalesOrder] ON Sales.SalesOrder;
GO

-- Étape 3 : Créer le Ordered CCI
-- Microsoft recommande MAXDOP = 1 pour un meilleur ordonnancement
CREATE CLUSTERED COLUMNSTORE INDEX CCSI_SalesOrder
ON Sales.SalesOrder ORDER (SalesDate)
WITH (MAXDOP = 1);
GO

Verification of the Elimination Segment with the Ordered CCI

SET STATISTICS IO ON;

SELECT SUM(SalesAmount) AS SalesAmount,
       YEAR(SalesDate) AS SalesYear
FROM Sales.SalesOrder
WHERE SalesDate >= '20220101'
      AND SalesDate <= '20221231'
GROUP BY YEAR(SalesDate);
GO

With an Ordered CCI, SQL eliminates 20 segments out of 30 million rows thanks to perfect data ordering.

Ordered segment metadata result

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
       i.[Name] AS IndexName,
       c.[Name] AS ColumnName,
       se.segment_id AS SegmentId,
       se.row_count AS SegmentRowCount,
       se.min_data_id AS MinRowValue,
       se.max_data_id AS MaxRowValue
FROM [sys].[column_store_segments] se
    INNER JOIN [sys].[partitions] p ON p.hobt_id = se.hobt_id
    INNER JOIN [sys].[indexes] i ON i.OBJECT_ID = p.OBJECT_ID
        AND i.index_id = p.index_id
    INNER JOIN [sys].[tables] t ON t.OBJECT_ID = i.OBJECT_ID
    INNER JOIN [sys].[columns] c ON c.OBJECT_ID = t.OBJECT_ID
        AND c.column_id = se.column_id
WHERE c.[Name] = 'SalesDate';
GO

Key Pattern: With an Ordered CCI, the MaxRowValue of Segment 0 is exactly equal to the MinRowValue of Segment 1, and so on. Segments do not overlap — ensuring maximum elimination.

5. Performance optimization of Columnstore Indexes

Module duration: 31m 44s

5.1 Batch Mode Processing

Batch Mode is one of the most important performance mechanisms of Columnstore Indexes. Instead of processing one row at a time (Row Mode), SQL Server processes about 900 rows at a time in Batch Mode.

Row Mode vs Batch Mode

History and evolution of Batch Mode

Operators that support Batch Mode

• Hash Match (join and aggregation)
• Exit
• Filter
• The Scalar and Aggregate operators

Important: Seek type operators (Index Seek, Key Lookup) never execute in Batch Mode.

COMPATIBILITY_LEVEL and Batch Mode

-- SQL Server 2017 (niveau 140)
ALTER DATABASE WiredBrainCoffee SET COMPATIBILITY_LEVEL = 140;

-- SQL Server 2014 (niveau 120) — MAXDOP = 1 désactive Batch Mode
ALTER DATABASE WiredBrainCoffee SET COMPATIBILITY_LEVEL = 120;

-- SQL Server 2019 (niveau 150)
ALTER DATABASE WiredBrainCoffee SET COMPATIBILITY_LEVEL = 150;

5.2 Aggregate Pushdown

Aggregate Pushdown is an optimization where SQL Server pushes aggregation calculations directly to the Columnstore Index scan, avoiding pushing all rows up to higher aggregation operators.

Conditions for Aggregate Pushdown to work

• The query must use standard aggregation functions: SUM, COUNT, MIN, MAX, AVG
• Aggregated columns should not pass through functions (like ROUND())
• Filters should not use functions on filter columns

Behavior Examples

-- ✅ Aggregate Pushdown actif
SELECT SUM(sh.ShipWeight) AS TotalShipWeight
FROM Sales.Shipping sh;

-- ✅ Aggregate Pushdown actif
SELECT COUNT(1) AS SalesOrderCount
FROM Sales.SalesOrder so;

-- ❌ Pas d'Aggregate Pushdown (fonction ROUND)
SELECT SUM(ROUND(sh.ShipWeight, 0)) AS TotalShipWeight
FROM Sales.Shipping sh;

-- ❌ Pas d'Aggregate Pushdown (fonction YEAR dans le WHERE)
SELECT COUNT(so.SalesDate) AS TotalSalesOrders
FROM Sales.SalesOrder so
WHERE YEAR(so.SalesDate) = '2022';

-- ✅ Aggregate Pushdown actif (filtre sans fonction)
SELECT COUNT(so.SalesDate) AS TotalSalesOrders
FROM Sales.SalesOrder so
WHERE so.SalesDate BETWEEN '1/1/2022' AND '12/31/2022';

Rule: Avoid wrapping columns in functions in the WHERE or in aggregations to benefit from Aggregate Pushdown.

Aggregate Pushdown and Numeric Type Precision

Columns of type DECIMAL(36, 2) may not benefit from Aggregate Pushdown due to their precision. Reducing the precision (e.g. DECIMAL(10, 2)) can enable Aggregate Pushdown.

5.3 String Predicate Pushdown

String Predicate Pushdown is an optimization that pushes filters on string columns directly to the Columnstore Index scan.

-- ✅ Standard Predicate Pushdown (numérique)
SELECT SUM(sh.ShipWeight) AS ShipWeightTotal
FROM Sales.Shipping sh
WHERE sh.ShipWeight > 50;

-- ✅ String Predicate Pushdown actif
SELECT SUM(sh.ShipWeight) AS ShipWeightTotal
FROM Sales.Shipping sh
WHERE sh.ShipState = 'Indiana';

-- ❌ Pas de String Predicate Pushdown (CAST désactive l'optimisation)
SELECT SUM(sh.ShipWeight) AS ShipWeightTotal
FROM Sales.Shipping sh
WHERE CAST(sh.ShipState AS NVARCHAR(25)) = 'Indiana';

-- ❌ Pas de String Predicate Pushdown (ISNULL sur la colonne de filtre)
SELECT SUM(sh.ShipWeight) AS ShipWeightTotal
FROM Sales.Shipping sh
WHERE ISNULL(sh.ShipPriority, 'SuperLow') = 'SuperLow';

Rule: Do not wrap filter columns in functions like CAST, CONVERT, ISNULL, COALESCE, etc. — this prevents String Predicate Pushdown and other optimizations.

5.4 Adaptive Query Joins (Intelligent Query Processing)

Adaptive Query Processing (AQP) is a family of features introduced in SQL Server 2017 that allows the optimizer to make better runtime decisions over time.

Issue Addressed: Parameter Sniffing

SQL Server reuses the last cached execution plan for a given query, even if that plan is not optimal for the new parameters. For example:

• A SalesPersonId = 5 can have 10,000,000 associated rows → optimal Hash Match
• A SalesPersonId = 25 may only have 1000 rows → Nested Loop optimal

But SQL can use the cached plan for both, which is suboptimal.

Adaptive Query Join

SQL Server uses a decision tree with a threshold to dynamically choose between:

• Nested Loop: for small sets of lines
• Hash Match: for large rowsets

If the number of rows exceeds the threshold, SQL automatically switches to a Hash Match.

Configuration

-- Activer le niveau de compatibilité SQL 2017 (minimum requis)
ALTER DATABASE WiredBrainCoffee SET COMPATIBILITY_LEVEL = 140;

Requirements

• Requires a Columnstore Index on the table
• COMPATIBILITY_LEVEL ≥ 140 (SQL Server 2017)
• Statistics must be current for thresholds to be accurate

Limitations

• Most Intelligent Query Processing (IQP) features are only available in Enterprise Edition
• Available in Azure SQL Database and Managed Instance (all editions)

5.5 Demo: Batch Mode

File: 05/demos/m5-demos/01_Batch_Mode.sql

This demonstration uses the Sales.Shipping table created with an NCCI.

Creation of the Sales.Shipping table and its NCCI

CREATE TABLE Sales.Shipping
(
    Id int identity(1, 1) NOT NULL,
    SalesOrder int NOT NULL,
    ShipDate date NOT NULL,
    ShipState nvarchar(100) NOT NULL,
    ShipWeight decimal(10, 2) NOT NULL,
    ShipPriority nvarchar(20) NULL,
    CONSTRAINT PK_Shipping_Id PRIMARY KEY (Id),
    CONSTRAINT FK_SalesOrder FOREIGN KEY (SalesOrder)
        REFERENCES Sales.SalesOrder (Id)
);
GO

The table is populated with 3.2 million rows with states (Indiana, Kentucky, Ohio, Michigan) and random weights.

-- Création du NCCI sur Shipping
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Shipping
ON Sales.Shipping (SalesOrder, ShipDate, ShipState, ShipWeight, ShipPriority)
WITH (MAXDOP = 1);
GO

-- B-Tree Index secondaire sur ShipDate
CREATE NONCLUSTERED INDEX IX_ShipDate ON Sales.Shipping (ShipDate);
GO

Batch Mode Test

-- Test avec NCCI (doit utiliser Batch Mode)
SELECT COUNT(1) AS ShipCount,
       YEAR(ShipDate) AS ShipYear,
       MONTH(ShipDate) AS ShipMonth
FROM Sales.Shipping s
WHERE ShipDate >= '1/1/2021' AND ShipDate <= '12/31/2021'
GROUP BY YEAR(ShipDate), MONTH(ShipDate);
GO

-- Test avec Index Hint Rowstore (pas de Batch Mode via Seek)
SELECT SUM(ShipWeight) AS ShipCount,
       YEAR(ShipDate) AS ShipYear,
       MONTH(ShipDate) AS ShipMonth
FROM Sales.Shipping s WITH (INDEX(IX_ShipDate))
WHERE ShipDate >= '1/1/2021' AND ShipDate <= '12/31/2021'
GROUP BY YEAR(ShipDate), MONTH(ShipDate);
GO

Behavior testing in SQL Server 2014 (MAXDOP = 1)

ALTER DATABASE WiredBrainCoffee SET COMPATIBILITY_LEVEL = 120;
GO

-- MAXDOP = 1 : pas de Batch Mode en SQL 2014
SELECT SUM(ShipWeight) AS TotalShipWeight
FROM Sales.Shipping
OPTION (MAXDOP 1);
GO

-- Sans MAXDOP = 1 : Batch Mode disponible
SELECT SUM(ShipWeight) AS TotalShipWeight
FROM Sales.Shipping;
GO

-- Retour à SQL 2019
ALTER DATABASE WiredBrainCoffee SET COMPATIBILITY_LEVEL = 150;
GO

5.6 Demo: Batch Mode Part 2

File: 05/demos/m5-demos/02_Batch_Mode_Part_2.sql

This demonstration compares in detail Row Mode and Batch Mode with SET STATISTICS TIME, IO ON.

SET STATISTICS TIME, IO ON;

-- Forcer l'utilisation du Clustered Index (déclenchera Batch Mode sur SQL 2019)
SELECT COUNT(1) AS ShippingCount,
       MONTH(ShipDate) AS ShipMonth
FROM Sales.Shipping WITH (INDEX(PK_Shipping_Id))
GROUP BY ShipDate;
GO

-- Désactiver explicitement le Batch Mode avec un Query Hint
SELECT COUNT(1) AS ShippingCount,
       MONTH(ShipDate) AS ShipMonth
FROM Sales.Shipping WITH (INDEX(PK_Shipping_Id))
GROUP BY ShipDate
OPTION (USE HINT ('DISALLOW_BATCH_MODE'));
GO

SET STATISTICS TIME, IO OFF;
GO

The USE HINT ('DISALLOW_BATCH_MODE') forces SQL Server to use Row Mode, allowing direct comparison of execution times.

5.7 Demo: Batch Mode on Rowstore (SQL Server 2019)

In SQL Server 2019 (COMPATIBILITY_LEVEL = 150), Batch Mode is available on tables without Columnstore Index. This feature is part of the Intelligent Query Processing family.

In the demonstration, by forcing the use of the Clustered Index (B-Tree) with WITH (INDEX(PK_Shipping_Id)), SQL Server still chooses to use Batch Mode if the Compatibility Level is 150. The execution plan displays:

• Actual Execution Mode: Batch
• Estimated Execution Mode: Batch

5.8 Demo: Aggregate Pushdown

File: 05/demos/m5-demos/03_Aggregate_Pushdown.sql

-- ✅ Aggregate Pushdown actif
SELECT SUM(sh.ShipWeight) AS TotalShipWeight
FROM Sales.Shipping sh;
GO

-- ❓ Est-ce qu'on a Aggregate Pushdown ici ? (SalesOrder a un CCI/NCCI différent)
SELECT SUM(sh.SalesAmount) AS SalesTotal
FROM Sales.SalesOrder sh;
GO

-- ✅ COUNT(1)
SELECT COUNT(1) AS SalesOrderCount
FROM Sales.SalesOrder so;
GO

-- ❌ ROUND désactive Aggregate Pushdown
SELECT SUM(ROUND(sh.ShipWeight, 0)) AS TotalShipWeight
FROM Sales.Shipping sh;
GO

-- ❌ YEAR() dans WHERE désactive Aggregate Pushdown
SELECT COUNT(so.SalesDate) AS TotalSalesOrders
FROM Sales.SalesOrder so
WHERE YEAR(so.SalesDate) = '2022';
GO

-- ✅ Filtre direct sans fonction
SELECT COUNT(so.SalesDate) AS TotalSalesOrders
FROM Sales.SalesOrder so
WHERE so.SalesDate BETWEEN '1/1/2022' AND '12/31/2022';
GO

5.9 Demo: String Predicate Pushdown

File: 05/demos/m5-demos/04_String_Predicate_Pushdown.sql

-- ✅ Standard Predicate Pushdown (numérique)
SELECT SUM(sh.ShipWeight) AS ShipWeightTotal
FROM Sales.Shipping sh
WHERE sh.ShipWeight > 50;
GO

-- ✅ String Predicate Pushdown actif
SELECT SUM(sh.ShipWeight) AS ShipWeightTotal
FROM Sales.Shipping sh
WHERE sh.ShipState = 'Indiana';
GO

-- ❌ CAST désactive String Predicate Pushdown
SELECT SUM(sh.ShipWeight) AS ShipWeightTotal
FROM Sales.Shipping sh
WHERE CAST(sh.ShipState AS NVARCHAR(25)) = 'Indiana';
GO

-- ❌ ISNULL désactive String Predicate Pushdown
SELECT SUM(sh.ShipWeight) AS ShipWeightTotal
FROM Sales.Shipping sh
WHERE ISNULL(sh.ShipPriority, 'SuperLow') = 'SuperLow';
GO

6. Monitoring and maintaining index health

Module duration: 23m 53s

6.1 Impact of data modifications on a Columnstore Index

Understanding how DML operations (INSERT, UPDATE, DELETE) affect a Columnstore Index is essential for maintenance.

INSERT

• New rows go to the Delta Store (state OPEN)
• Exception: if ≥ 102,400 rows are inserted in a single operation on a Clustered Columnstore Index, they go directly into a compressed Row Group (Bulk Load Path)
• For Non-Clustered Columnstore Indexes, the Bulk Load Path does not apply — inserts always go through the Delta Store
• Tuple Mover compresses Delta Row Groups into Segments every ~5 minutes

UPDATE

An UPDATE in a Columnstore Index is treated as:

1. DELETE the old version of the line (marked as deleted in the Deleted Bitmap)
2. INSERT of the new version in the Delta Store

There is no in-place update in a Columnstore Index.

DELETE

• Row deletions in compressed Row Groups do not physically delete data
• The row is marked as deleted in the Deleted Bitmap (internal table)
• The row is physically removed during the next REORGANIZE or REBUILD operation
• Deletions in the Delta Store: rows are physically deleted from the Delta Store

Internal structures linked to changes

DMV to inspect internal structures

SELECT object_name(i.object_id) AS TableName,
       i.[name] AS IndexName,
       p.[internal_object_type_desc] AS [Description],
       p.[rows] AS [RowCount],
       p.[data_compression_desc] AS [CompressionType]
FROM [sys].[internal_partitions] AS p
    JOIN [sys].[indexes] AS i ON p.[object_id] = i.[object_id]
        AND p.[index_id] = i.[index_id]
WHERE i.[name] = 'NCCI_Shipping';
GO

6.2 Fragmentation detection

In a Columnstore Index, fragmentation is measured by the percentage of rows deleted in compressed Row Groups.

Microsoft Fragmentation Formula

$$\text{Fragmentation %} = \frac{100 \times \text{deleted_rows}}{\text{total_rows}}$$

Fragmentation detection request

-- Niveau de fragmentation par Row Group
SELECT object_name(i.object_id) AS TableName,
       i.name AS IndexName,
       i.type_desc AS IndexType,
       rg.state_desc AS StateDescription,
       rg.total_rows AS TotalRows,
       rg.deleted_rows AS DeletedRows,
       100 * (ISNULL(deleted_rows, 0)) / NULLIF(total_rows, 0) AS Fragmented,
       rg.trim_reason_desc AS TrimReason
FROM [sys].[indexes] AS i
    JOIN [sys].[dm_db_column_store_row_group_physical_stats] AS rg
        ON i.object_id = rg.object_id
           AND i.index_id = rg.index_id
WHERE i.name = 'NCCI_Shipping';
GO

-- Niveau de fragmentation global de l'index
SELECT object_name(i.object_id) AS TableName,
       i.name AS IndexName,
       100 * (ISNULL(SUM(deleted_rows), 0)) / NULLIF(SUM(total_rows), 0) AS Fragmented
FROM [sys].[indexes] AS i
    JOIN [sys].[dm_db_column_store_row_group_physical_stats] AS rg
        ON i.object_id = rg.object_id
           AND i.index_id = rg.index_id
WHERE i.name = 'NCCI_Shipping'
GROUP BY object_name(i.object_id), i.name;
GO

When to act?

A heavily fragmented index may not be used by the query optimizer. Regularly monitor the level of fragmentation and act when it exceeds an acceptable threshold (generally 10-30% depending on the environment).

6.3 Elimination of fragmentation

There are two options for eliminating fragmentation of a Columnstore Index:

Option 1: ALTER INDEX … REORGANIZE

• online process (the table remains accessible)
• Merges small Row Groups into larger Row Groups
• Physically delete rows marked as deleted
• The COMPRESS_ALL_ROW_GROUPS = ON option forces compression of Delta Stores even if they are small

-- REORGANIZE standard : fusionne les Row Groups et supprime les lignes effacées
ALTER INDEX NCCI_Shipping ON Sales.Shipping REORGANIZE;
GO

-- Avec COMPRESS_ALL_ROW_GROUPS : compresse même les petits Delta Stores
ALTER INDEX NCCI_Shipping ON Sales.Shipping REORGANIZE
WITH (COMPRESS_ALL_ROW_GROUPS = ON);
GO

Option 2: ALTER INDEX … REBUILD

• Completely recreate the index
• Produces optimal and full Row Groups
• Eliminate all fragments and deleted lines
• Option offline by default (SQL 2016+: online for Clustered Columnstore Indexes)
• MAXDOP = 1 recommended for better quality of Row Groups

-- REBUILD complet avec MAXDOP = 1
ALTER INDEX NCCI_Shipping ON Sales.Shipping REBUILD WITH (MAXDOP = 1);
GO

REORGANIZE vs REBUILD comparison

6.4 Demo: Data modifications

File: 06/demos/m6-demos/01_Data_Modifications.sql

This demonstration shows the state of Row Groups before and after INSERT, UPDATE, DELETE operations.

-- Inspection de l'état initial des Row Groups
SELECT object_name(i.object_id) AS TableName,
       i.name AS IndexName,
       i.type_desc AS IndexType,
       rg.state_desc AS StateDescription,
       rg.total_rows AS TotalRows,
       rg.deleted_rows AS DeletedRows,
       rg.trim_reason_desc AS TrimReason
FROM [sys].[indexes] AS i
    JOIN [sys].[dm_db_column_store_row_group_physical_stats] AS rg
        ON i.object_id = rg.object_id
           AND i.index_id = rg.index_id
WHERE i.name = 'NCCI_Shipping';
GO

-- INSERT de 500 lignes → vont dans le Delta Store
INSERT INTO Sales.Shipping
SELECT TOP 500 SalesOrder, ShipDate, ShipState, ShipWeight, ShipPriority
FROM Sales.Shipping;
GO

-- UPDATE de 500 lignes → delete + insert dans Delta Store
UPDATE Sales.Shipping
SET ShipState = 'California'
WHERE Id <= 500;
GO

-- DELETE de 1000 lignes (500 du Delta Store + 500 marquées comme supprimées)
DELETE FROM Sales.Shipping
WHERE Id > 3199500;
GO

Deleted Buffer Inspection

SELECT object_name(i.object_id) AS TableName,
       i.[name] AS IndexName,
       p.[internal_object_type_desc] AS [Description],
       p.[rows] AS [RowCount],
       p.[data_compression_desc] AS [CompressionType]
FROM [sys].[internal_partitions] AS p
    JOIN [sys].[indexes] AS i ON p.[object_id] = i.[object_id]
        AND p.[index_id] = i.[index_id]
WHERE i.[name] = 'NCCI_Shipping';
GO

6.5 Demo: Fragmentation detection

File: 06/demos/m6-demos/02_Detecting_Fragmentation.sql

-- Suppression de plus d'un million de lignes (> capacité d'un Row Group)
;WITH cte_shipping AS (
    SELECT TOP 1048577 *
    FROM Sales.Shipping
    ORDER BY Id ASC
)
DELETE FROM cte_shipping;
GO

After this deletion, the Delete Buffer fills up. After about 5 minutes, the Tuple Mover transfers the entries to the Deleted Bitmap.

-- Vérification du taux de fragmentation
SELECT object_name(i.object_id) AS TableName,
       i.name AS IndexName,
       100 * (ISNULL(deleted_rows, 0)) / NULLIF(total_rows, 0) AS Fragmented
FROM [sys].[indexes] AS i
    JOIN [sys].[dm_db_column_store_row_group_physical_stats] AS rg
        ON i.object_id = rg.object_id
           AND i.index_id = rg.index_id
WHERE i.name = 'NCCI_Shipping'
GROUP BY object_name(i.object_id), i.name;
GO

6.6 Demo: Elimination of fragmentation

File: 06/demos/m6-demos/03_Eliminating_Fragmentation.sql

-- Vérification avant défragmentation
SELECT object_name(i.object_id) AS TableName,
       i.name AS IndexName,
       rg.state_desc AS StateDescription,
       rg.total_rows AS TotalRows,
       rg.deleted_rows AS DeletedRows,
       100 * (ISNULL(deleted_rows, 0)) / NULLIF(total_rows, 0) AS Fragmented
FROM [sys].[indexes] AS i
    JOIN [sys].[dm_db_column_store_row_group_physical_stats] AS rg
        ON i.object_id = rg.object_id
           AND i.index_id = rg.index_id
WHERE i.name = 'NCCI_Shipping';
GO

-- REORGANIZE standard
ALTER INDEX NCCI_Shipping ON Sales.Shipping REORGANIZE;
GO

-- REORGANIZE avec compression de tous les Row Groups (même les petits Delta Stores)
ALTER INDEX NCCI_Shipping ON Sales.Shipping REORGANIZE
WITH (COMPRESS_ALL_ROW_GROUPS = ON);
GO

-- REBUILD complet pour des Row Groups optimaux (MAXDOP = 1)
ALTER INDEX NCCI_Shipping ON Sales.Shipping REBUILD WITH (MAXDOP = 1);
GO

After a REBUILD WITH (MAXDOP = 1), all Row Groups are recreated with the maximum number of rows possible, without deleted rows or fragmented Row Groups.

7. Putting it into practice: Proof of Concept and advanced use cases

7.1 Construction of a Proof of Concept (POC)

Before deploying a Columnstore Index in production, it is crucial to build a Proof of Concept (POC) to objectively prove the performance gains.

Why do a POC?

Stakeholders will always ask questions about the impact of a change:

• “Will it break something?”
• “What are the side effects?”
• “Will this really make things better?”

It is unfair to ask your stakeholders to take your word for it without concrete proof. A POC provides objective measures to justify change.

Steps of a POC Columnstore

1. Familiarization: Understanding Columnstore Indexes (you are doing it now)
2. Identifying candidate queries: Identify slow queries that perform aggregations on large tables
3. Establish a baseline: Measure current performance with native SQL tools
4. Create the Columnstore Index in a non-production environment
5. Measure improvement: Compare before and after metrics
6. Simulate Load: Use tools like SQLQueryStress to simulate multiple concurrent users
7. Make an informed decision: Implement if results justify the change

Performance Metrics in SQL Server

-- Activer les statistiques IO et temps
SET STATISTICS IO, TIME ON;
GO

-- Nettoyer le cache (NE PAS utiliser en production)
DBCC DROPCLEANBUFFERS;
GO

Key metrics to compare:

• Elapsed time (execution time)
• Logical reads (logical reads in pages)
• CPU time

7.2 Demo: POC — Part 1

File: 07/demos/m7-demos/01_Proof_of_Concept_Part_1.sql

This demo compares an NCCI to a B-Tree Index on the Sales Report query.

Creation of the B-Tree Reference Index

-- Suppression du B-Tree index précédent
DROP INDEX IF EXISTS [IX_SalesOrder_SalesDate] ON Sales.SalesOrder;
GO

-- Nouveau B-Tree Index avec compression PAGE
CREATE NONCLUSTERED INDEX [IX_SalesOrder_SalesPerson_SalesDate]
ON Sales.SalesOrder (SalesPerson, SalesDate)
INCLUDE (SalesAmount, SalesTerritory)
WITH (DATA_COMPRESSION = PAGE);
GO

B-Tree vs Columnstore Comparison

SET STATISTICS IO, TIME ON;
GO

DBCC DROPCLEANBUFFERS;
GO

-- Requête en utilisant le B-Tree Index
SELECT SUM(so.SalesAmount) AS SalesAmount,
       CONCAT(sp.lastname, ', ', sp.firstname) AS SalesPerson,
       AVG(so.SalesAmount) AS AverageSalesAmount,
       MAX(so.SalesAmount) AS TopSalesAmount,
       YEAR(so.SalesDate) AS SalesYear,
       DATENAME(MONTH, so.SalesDate) AS SalesMonth
FROM Sales.SalesOrder so WITH (INDEX([IX_SalesOrder_SalesPerson_SalesDate]))
    INNER JOIN Sales.SalesPerson sp ON so.SalesPerson = sp.Id
WHERE so.SalesDate >= '1/1/2022' AND so.SalesDate <= '12/31/2023'
GROUP BY so.SalesDate, sp.LastName, sp.FirstName
OPTION (MAXDOP 2);

DBCC DROPCLEANBUFFERS;
GO

-- Même requête en utilisant le Columnstore Index (SQL choisit automatiquement)
SELECT SUM(so.SalesAmount) AS SalesAmount,
       CONCAT(sp.lastname, ', ', sp.firstname) AS SalesPerson,
       AVG(so.SalesAmount) AS AverageSalesAmount,
       MAX(so.SalesAmount) AS TopSalesAmount,
       YEAR(so.SalesDate) AS SalesYear,
       DATENAME(MONTH, so.SalesDate) AS SalesMonth
FROM Sales.SalesOrder so
    INNER JOIN Sales.SalesPerson sp ON so.SalesPerson = sp.Id
WHERE so.SalesDate >= '1/1/2022' AND so.SalesDate <= '12/31/2023'
GROUP BY so.SalesDate, sp.LastName, sp.FirstName
OPTION (MAXDOP 2);

SET STATISTICS IO, TIME OFF;
GO

Areas for improvement identified

1. Change the precision of SalesAmount: Reducing from DECIMAL(36, 2) to a lower precision may enable Aggregate Pushdown
2. Order the data by the most used filter: Use an Ordered CCI on SQL Server 2022 to activate Segment Elimination

7.3 Demo: POC — Part 2

File: 07/demos/m7-demos/02_Proof_of_Concept_Part_2.sql

This demo measures the impact of data changes on Columnstore Index performance.

Comparison of initial index size

SELECT i.[name] AS IndexName,
       SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM [sys].[dm_db_partition_stats] AS s
    INNER JOIN [sys].[indexes] AS i ON s.[object_id] = i.[object_id]
        AND s.[index_id] = i.[index_id]
WHERE i.[name] IN ('CS_SalesOrder', 'IX_SalesOrder_SalesPerson_SalesDate')
GROUP BY i.[name];
GO

Sizes before modifications

Activity simulation (100,000 insertions + 1.3M updates)

-- INSERT de 100 000 lignes
INSERT INTO Sales.SalesOrder (SalesPerson, SalesAmount, SalesDate, SalesTerritory, SalesOrderStatus, OrderDescription)
SELECT TOP 100000 SalesPerson, SalesAmount, SalesDate, SalesTerritory, SalesOrderStatus, OrderDescription
FROM Sales.SalesOrder;
GO

-- UPDATE d'environ 1,3 millions de lignes
UPDATE Sales.SalesOrder
SET SalesAmount = CASE
    WHEN Id >= 1 AND Id < 100000 THEN '200.00'
    WHEN Id >= 100000 AND Id < 250000 THEN '400.00'
    WHEN Id >= 250000 AND Id < 500000 THEN '150.00'
    WHEN Id >= 500000 AND Id < 800000 THEN '1000.00'
    WHEN Id >= 800000 AND Id <= 1300000 THEN '2000.00'
END
WHERE Id <= 1300000;
GO

Sizes after modifications

Performance after modifications

Conclusion: Even after significant modifications, the Columnstore Index maintains significantly superior performance to the B-Tree. The Rowstore almost doubles its execution time after updates.

7.4 Compressing a large static table

File: 07/demos/m7-demos/03_Compress_Big_Static_Table.sql

This demonstration illustrates the different compression levels available for a table of 10 million rows (dbo.BigTable).

Creating the test table

CREATE TABLE dbo.BigTable
(
    Id bigint identity(1, 1),
    SalesCode int,
    CustomerId int,
    OrderId int,
    TrackingId int,
    TrackingCount int,
    StartDate date,
    EndDate date
);
GO

-- Insertion de 10 millions de lignes avec données aléatoires
DECLARE @StartDate AS date = '01-01-2020';
DECLARE @EndDate AS date = '12-31-2023';
DECLARE @DaysBetween AS int = (1 + DATEDIFF(DAY, @StartDate, @EndDate));

INSERT INTO dbo.BigTable (SalesCode, CustomerId, OrderId, TrackingId, TrackingCount, StartDate, EndDate)
SELECT TOP 10000000
    ABS(CHECKSUM(NEWID()) % 1000) + 10 AS SalesCode,
    ABS(CHECKSUM(NEWID()) % 2000) + 20 AS CustomerId,
    ABS(CHECKSUM(NEWID()) % 3000) + 30 AS OrderId,
    ABS(CHECKSUM(NEWID()) % 4000) + 40 AS TrackingId,
    ABS(CHECKSUM(NEWID()) % 100000) + 1000 AS TrackingCount,
    DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (@DaysBetween), @StartDate) AS StartDate,
    DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (@DaysBetween), @StartDate) AS EndDate
FROM dbo.Numbers n1;
GO

Size comparison by compression type

EXEC sp_spaceused N'dbo.BigTable';  -- Taille originale
GO

-- PAGE Compression (inclut ROW compression)
ALTER TABLE dbo.BigTable REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused N'dbo.BigTable';
GO

-- Clustered Columnstore Index
CREATE CLUSTERED COLUMNSTORE INDEX CCI_BigTable ON dbo.BigTable;
GO
EXEC sp_spaceused N'dbo.BigTable';
GO

-- COLUMNSTORE_ARCHIVE compression (compression maximale)
ALTER TABLE dbo.BigTable REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO
EXEC sp_spaceused N'dbo.BigTable';
GO

Compression results (10 million rows)

COLUMNSTORE_ARCHIVE use case: For rarely accessed historical tables (archiving), where maximum compression takes priority over read performance. Queries on a COLUMNSTORE_ARCHIVE table will be slower because decompression is more expensive.

7.5 Bulk Loading and Columnstore Indexes

File: 07/demos/m7-demos/04_Bulk_Loading.sql

Understanding the Bulk Load Path is essential to optimize bulk inserts in Columnstore Indexes.

Bulk Load Path Rule

When ≥ 102,400 rows are inserted in a single operation into a Clustered Columnstore Index, the rows go directly into a compressed Row Group without going through the Delta Store.

This rule applies only to Clustered Columnstore Indexes. For Non-Clustered Columnstore Indexes, inserts always go through the Delta Store.

Bulk Load Path Demonstration

-- Création d'une table staging avec CCI
SELECT * INTO Sales.Shipping_Clustered FROM Sales.Shipping;
GO

-- Inspection des Row Groups initiaux
SELECT i.name, rg.row_group_id, rg.state_desc, rg.total_rows,
       rg.deleted_rows, rg.trim_reason_desc
FROM sys.dm_db_column_store_row_group_physical_stats rg
    INNER JOIN sys.indexes i ON i.object_id = rg.object_id
        AND i.index_id = rg.index_id
WHERE i.name = 'NCCI_Shipping';

-- INSERT de 102 400 lignes dans le NCCI (va dans le Delta Store)
INSERT INTO Sales.Shipping (SalesOrder, ShipDate, ShipState, ShipWeight, ShipPriority)
SELECT TOP 102400 SalesOrder, ShipDate, ShipState, ShipWeight, ShipPriority
FROM Sales.Shipping_Clustered
ORDER BY Id ASC;
GO

-- Création d'un CCI sur la table staging
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Shipping_Clustered
ON Sales.Shipping_Clustered
WITH (MAXDOP = 1);
GO

-- INSERT de 102 400 lignes dans le CCI (Bulk Load Path → directement compressé)
INSERT INTO Sales.Shipping_Clustered (SalesOrder, ShipDate, ShipState, ShipWeight, ShipPriority)
SELECT TOP 102400 SalesOrder, ShipDate, ShipState, ShipWeight, ShipPriority
FROM Sales.Shipping
ORDER BY Id ASC;
GO

After insertion into the CCI, Row Groups display TrimReason = BULKLOAD for Row Groups created by the Bulk Load Path.

Tuple Mover behavior (SQL Server 2019+)

Starting with SQL Server 2019, the Tuple Mover includes a merge process that combines small Row Groups into larger Row Groups once they reach a certain threshold. This behavior was only observed on Clustered Columnstore Indexes.

Official reference: Microsoft — Columnstore Indexes Data Loading Guidance

7.6 Nuances between index types

File: 07/demos/m7-demos/04_Index_Type_Nuance.sql

This file is identical to 04_Bulk_Loading.sql in content — it delves deeper into the behavioral nuances between Clustered and Non-Clustered Columnstore Indexes, including:

• Behavior of the Bulk Load Path depending on the index type
• Behavior of Tuple Mover and merge process on SQL Server 2019+
• Impact of MAXDOP on the quality of Row Groups during index creation

8. Resources and references

Official Microsoft Documentation

• Columnstore Indexes — Query Performance
• Columnstore Indexes — Batch Mode Execution
• Columnstore Indexes — Aggregate Pushdown
• Columnstore Indexes — String Predicate Pushdown
• Detecting Fragmentation on Columnstore Indexes
• Columnstore Indexes Data Loading Guidance
• Recommendations for Improving Query Performance

SQL Server Community Articles

• Aaron Bertrand: Generate a Set — dbo.Numbers
• Niko Neugebauer:
• Joe Obbish: Aggregate Pushdown Limitations
• Erik Darling: Aggregate Pushdown Limitations
• Paul Randal: Unexpected Fragmentation

Recommended tools

• SQLQueryStress: Simulates multiple users running queries simultaneously
• SQL Server Management Studio (SSMS): For analyzing execution plans and operator properties (Batch Mode, Segment Elimination)

Key DMVs used in this course

Search Terms

optimizing · query · performance · columnstore · indexes · microsoft · sql · server · databases · index · batch · mode · creation · comparison · elimination · fragmentation · pushdown · row · segment · b-tree · compression · data · modifications · ncci