You might remember that there is still one option left for columnar storage compression—archive compression. Let's turn it on with the following code:
ALTER INDEX CCI_FactTest ON dbo.FactTest REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE); GO
You can imagine what comes next; recheck the space used by the test fact table:
EXEC sys.sp_spaceused N'dbo.FactTest', @updateusage = N'TRUE'; GO
The result is as follows:
Name rows reserved data index_size unused ------------ ------- --------- --------- ---------- ------- dbo.FactTest 2279810 19528 KB 19336 KB 0 KB 192 KB
The LZ77 algorithm added some additional compression. Compare the data size now with the initial data size when the data size was 498,528 KB; now it is only 19,336 KB. The compression rate is more than 25 times! This is really impressive. Of course, you'd expect test queries to be even more efficient now. For example, here is the simple query:
SET STATISTICS IO ON; SELECT f.StockItemKey, SUM(f.TotalAmount) AS Sales FROM dbo.FactTest AS f WHERE f.StockItemKey < 30 GROUP BY f.StockItemKey ORDER BY f.StockItemKey;
This time, SQL Server needed only 23 LOB logical reads. The next query to test is the complex query:
SELECT f.SaleKey, f.CustomerKey, f.Customer, cu.[Buying Group], f.CityKey, f.City, ci.Country, f.DateKey, d.[Calendar Year], f.StockItemKey, f.Product, f.Quantity, f.TotalAmount, f.Profit FROM dbo.FactTest AS f INNER JOIN Dimension.Customer AS cu ON f.CustomerKey = cu.[Customer Key] INNER JOIN Dimension.City AS ci ON f.CityKey = ci.[City Key] INNER JOIN Dimension.[Stock Item] AS s ON f.StockItemKey = s.[Stock Item Key] INNER JOIN Dimension.Date AS d ON f.DateKey = d.Date;
This time, SQL Server needed 4,820 LOB logical reads in the test fact table. It can't get much better than this for scanning all of the data. And what about the point query?:
SELECT CustomerKey, Profit FROM dbo.FactTest WHERE CustomerKey = 378;
This time, it used 410 LOB logical reads. This number can still be improved.