SQL Server Columnstore Indexes: Fast Reporting on Large Tables
If your reports aggregate millions of rows, such as totals by month, department or pay code, a columnstore index can make them dramatically faster and the table much smaller. It is one of the most effective features in SQL Server for analytics, as long as you use it for the right workload.
Rowstore vs columnstore
Normal tables and indexes are rowstore: each page holds complete rows. That is ideal for transactions, which read or write a few whole rows at a time.
A columnstore stores each column separately, compressed, in groups of up to about a million rows (row groups). A query that sums Amount by PayCode reads only those two columns and skips the rest. Because values within one column are similar, they compress very well, often to a fraction of the rowstore size.
Batch mode
Queries on columnstore indexes can run in batch mode, processing rows in batches instead of one at a time, which cuts CPU use sharply for scans, joins and aggregations. From SQL Server 2019 (compatibility level 150), batch mode can also be used on rowstore tables in some analytic queries.
Clustered columnstore: for fact tables
A clustered columnstore index (CCI) makes the whole table columnar. It suits large, append-mostly tables used for reporting, such as a payroll history or sales fact table:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_PayrollHistory
ON dbo.PayrollHistory;You can still add rowstore nonclustered indexes on top of a CCI to support a few selective lookups.
Nonclustered columnstore: analytics on an OLTP table
A nonclustered columnstore index (NCCI) adds a columnar copy of selected columns to a normal transactional table, so reports can run against live data without a separate reporting database:
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_PayrollLine_Reporting
ON dbo.PayrollLine (PayrollRunId, EmployeeId, PayCode, Amount, CreatedAt);Writes become slightly more expensive, because the extra index must be maintained, so test the impact on busy transactional tables.
Load data in large batches
New rows first go into a rowstore delta store and are compressed into columnstore row groups later. Bulk inserts of 102,400 rows or more at a time can go straight into compressed row groups, which is faster and gives better compression. Many small inserts leave lots of uncompressed delta rows. You can check and tidy them:
SELECT state_desc, COUNT(*) AS row_groups, SUM(total_rows) AS total_rows
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.PayrollHistory')
GROUP BY state_desc;
ALTER INDEX CCI_PayrollHistory ON dbo.PayrollHistory
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);Segment elimination
Each column segment stores its minimum and maximum values. If data is loaded roughly in date order, a query for one month can skip row groups that cannot contain that month. Loading in a sensible order, or partitioning by date, makes this work well.
When not to use columnstore
- Tables where the main access pattern is fetching or updating single rows by key.
- Small tables, where the overhead is not worth it.
- Tables with very frequent updates and deletes to existing rows; these are stored as deleted markers plus new rows and degrade compression over time.
Quick checklist
- Use a clustered columnstore for large, append-mostly reporting tables.
- Use a nonclustered columnstore for real-time analytics on OLTP tables.
- Load in batches of 102,400+ rows and monitor the delta store.
- Load or partition by date to benefit from segment elimination.
- Keep rowstore indexes for single-row lookups.
Related reading:
Comments
Post a Comment