Again, the first test is with 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;
From the IO output, you can see that SQL Server used only 82 logical reads. This time, this is really impressive. Note that the execution plan again used the Columnstore Index Scan operator, this time scanning the CCI and selecting only the rowgroups and segments needed to satisfy the query. You can see the execution plan in the following screenshot:
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 6,101 LOB logical reads. SQL Server used a serial plan on my virtual machine, a mixed batch (for CCI scan and for hash joins), and row mode operators (for other index scans). This is only slightly better than when using an NCCI. How about the point query?:
SELECT CustomerKey, Profit FROM dbo.FactTest WHERE CustomerKey = 378; SET STATISTICS IO OFF;
The point query this time used 484 LOB logical reads. Better, but still not the best possible.