Chapter 8 · Engine Features

Indexed Views & Columnstore

The course adds two SQL Server performance features to the star schema: an indexed view and a columnstore index. Here they are measured on the real tables, with page reads (which don't depend on how busy the Linux server is) rather than stopwatch times. One of the measurements turned up a problem worth knowing about.

The two features in one table

The course scripts

-- Indexed view: needs QUOTED_IDENTIFIER ON (the runner calls sqlcmd with -I).
USE SBEcoLTDDW;
GO

CREATE VIEW dbo.OrdersByDate
    WITH SCHEMABINDING
    AS
        SELECT Orders.OrderID, Orders.CustomerKey, Orders.OrderTotal,
            Date.FullDate, Date.DayName, Date.DayNumber,
            Date.MonthName, Date.MonthNumber, Date.QuarterNumber, Date.Year
        FROM Fact.Orders JOIN Dimension.Date ON Orders.DateKey = Date.DateKey
;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_OrdersByDate
   ON dbo.OrdersByDate (OrderID);
GO

-- course runs SELECT * (all rows); show a sample and the count instead
SELECT TOP (10) * FROM dbo.OrdersByDate ORDER BY OrderID;
SELECT COUNT(*) AS view_rows FROM dbo.OrdersByDate;
GO

USE SBEcoLTDDW;
GO

CREATE COLUMNSTORE INDEX IX_CS_FactOrders
ON Fact.Orders (OrderID, DateKey, CustomerKey, OrderTotal);
GO

USE SBEcoLTDDW;
GO

ALTER INDEX IX_CS_FactOrders
   ON Fact.Orders
   REBUILD WITH (ONLINE = ON);
GO

SELECT i.name AS index_name, i.type_desc, rg.state_desc, COUNT(*) AS row_groups, SUM(rg.total_rows) AS total_rows
FROM sys.indexes AS i
JOIN sys.dm_db_column_store_row_group_physical_stats AS rg
     ON rg.object_id = i.object_id AND rg.index_id = i.index_id
WHERE i.name = 'IX_CS_FactOrders'
GROUP BY i.name, i.type_desc, rg.state_desc;
GO

Measured: sales by year, four ways

Same question, "orders and sales per year", answered from four structures, with SET STATISTICS IO ON and MAXDOP 1 so each reads its own way:

-- 1 rowstore: force the clustered primary key
SELECT d.Year, COUNT(*), SUM(o.OrderTotal) FROM Fact.Orders o WITH (INDEX(PK_Orders))
JOIN Dimension.Date d ON d.DateKey = o.DateKey GROUP BY d.Year OPTION (MAXDOP 1);
-- 2 nonclustered columnstore
SELECT d.Year, COUNT(*), SUM(o.OrderTotal) FROM Fact.Orders o WITH (INDEX(IX_CS_FactOrders))
JOIN Dimension.Date d ON d.DateKey = o.DateKey GROUP BY d.Year OPTION (MAXDOP 1);
-- 3 the course's indexed view (one row per order, pre-joined)
SELECT Year, COUNT_BIG(*), SUM(OrderTotal) FROM dbo.OrdersByDate WITH (NOEXPAND) GROUP BY Year OPTION (MAXDOP 1);
-- 4 an aggregated indexed view (one row per year)
SELECT Year, Orders, Sales FROM dbo.SalesByYear WITH (NOEXPAND) OPTION (MAXDOP 1);

-- 1 rowstore
Table 'Orders'. Scan count 1, logical reads 72
Table 'Date'.   Scan count 1, logical reads 48
-- 2 columnstore
Table 'Orders'. Scan count 2, logical reads 0, lob logical reads 14
Table 'Orders'. Segment reads 1, segment skipped 0.
Table 'Date'.   Scan count 1, logical reads 48
-- 3 OrdersByDate
Table 'OrdersByDate'. Scan count 1, logical reads 74
-- 4 SalesByYear
Table 'SalesByYear'. Scan count 1, logical reads 2

What the numbers say:

• Columnstore reads 5× less than the rowstore for the same scan, because it reads only the DateKey and OrderTotal segments, compressed. On millions of rows the gap grows, and batch-mode execution adds CPU savings on top.
• The course's indexed view saves the join but not the reading. It stores one row per order with the date columns copied in, so scanning it costs as much as scanning the fact (74 vs 72 pages). A pre-join view helps when the join is expensive; here the Date join is cheap.
• An aggregated indexed view is the big win: 2 pages. SQL Server keeps five pre-summed rows current on every MERGE, so the yearly report reads almost nothing.

The aggregated indexed view

/* deploy 11 - dbo.SalesByYear: an AGGREGATED indexed view (idempotent).
   The course's dbo.OrdersByDate stores one row per order (a pre-join), so reading it costs
   about the same as the fact table. This view stores one row per year: SQL Server keeps the
   totals up to date on every change to Fact.Orders, and a yearly report reads a single page.
   Rules: SCHEMABINDING, two-part names, COUNT_BIG(*) with GROUP BY, SUM over a non-null value.
   CREATE VIEW must be alone in its batch, so it runs through EXEC. */
SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.SalesByYear') IS NULL
    EXEC (N'CREATE VIEW dbo.SalesByYear
            WITH SCHEMABINDING
            AS
            SELECT d.Year,
                   COUNT_BIG(*)                 AS Orders,
                   SUM(ISNULL(o.OrderTotal, 0)) AS Sales
            FROM Fact.Orders AS o
            JOIN Dimension.Date AS d ON d.DateKey = o.DateKey
            GROUP BY d.Year;');

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SalesByYear')
    EXEC (N'CREATE UNIQUE CLUSTERED INDEX IX_SalesByYear ON dbo.SalesByYear (Year);');

SELECT Year, Orders, Sales FROM dbo.SalesByYear WITH (NOEXPAND) ORDER BY Year;

Columnstore delta stores: a real effect in every load

A columnstore index only compresses rows directly when an insert brings at least 102,400 rows per thread. Smaller loads go into a delta store: a hidden, uncompressed rowstore that the columnstore has to read row by row. The initial load of 9,567 orders is below that threshold, and so is every incremental load of a handful of orders, so IX_CS_FactOrders starts every run with an OPEN delta store.

The post-load step compresses whatever is still open:

/* maintain_columnstore - compress open delta row groups of IX_CS_FactOrders (course Chapter 3).
   New rows land in an uncompressed delta row group (OPEN) when a load has fewer than
   102,400 rows per thread - e.g. the 9,567 orders of the first load, or 3 new orders later.
   REORGANIZE with COMPRESS_ALL_ROW_GROUPS turns them into compressed column segments, online;
   the old delta stores become TOMBSTONEs that are cleaned up later. */
SET NOCOUNT ON;

SELECT N'before' AS stage, state_desc, COUNT(*) AS row_groups, SUM(total_rows) AS total_rows, SUM(size_in_bytes) / 1024 AS size_kb
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(N'Fact.Orders') AND index_id = (SELECT index_id FROM sys.indexes WHERE name = N'IX_CS_FactOrders')
GROUP BY state_desc;

ALTER INDEX IX_CS_FactOrders ON Fact.Orders REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

SELECT N'after' AS stage, state_desc, COUNT(*) AS row_groups, SUM(total_rows) AS total_rows, SUM(size_in_bytes) / 1024 AS size_kb
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(N'Fact.Orders') AND index_id = (SELECT index_id FROM sys.indexes WHERE name = N'IX_CS_FactOrders')
GROUP BY state_desc;

EXEC audit.usp_LogStep @DagRunID = N'{{ run_id }}', @TaskID = N'{{ ti.task_id }}', @Note = N'REORGANIZE COMPRESS_ALL_ROW_GROUPS: IX_CS_FactOrders';

stage  | state_desc | row_groups | total_rows | size_kb
before | OPEN       | 1          | 9567       | 264       ← the 9,567 orders sit in a delta store
after  | COMPRESSED | 1          | 9567       | 82        ← about 3× smaller, column segments
after  | TOMBSTONE  | 1          | 9567       | 264       ← removed later by the background tuple mover

On a table this size the difference is small in absolute terms, but the ratio is the point: a delta store is a rowstore, so scans lose most of the columnstore's benefit until it is compressed. On big incremental loads the same step prevents a slow drift into "columnstore that mostly isn't". An alternative for very large loads is ALTER INDEX … REBUILD after the load.

Index sizes

object       | index_name                 | type                    | used_kb | rows
Fact.Orders  | PK_Orders                  | CLUSTERED               | 576     | 9571
Fact.Orders  | IX_CS_FactOrders           | NONCLUSTERED COLUMNSTORE| 128     | 9571
Fact.Orders  | UX_Orders_SalesOrderNumber | NONCLUSTERED            | 288     | 9571
OrdersByDate | IDX_OrdersByDate           | CLUSTERED (view)        | 592     | 9571
SalesByYear  | IX_SalesByYear             | CLUSTERED (view)        | 16      | 5

The columnstore copy of all four fact columns is a fifth of the size of the clustered rowstore, and the aggregated view is a rounding error. The pre-join view is the largest object here, which is another reason to prefer aggregated views for reporting.