Azure Columnstore, part 3 – Modern Segment Elimination and Set Statistics IO

Welcome to the 3rd blog post in the Azure Columnstore series, this one is dedicated to one very important improvement that Microsoft has added to Azure SQL Database v12 – the number of segments read and skipped whenever reading Columnstore Indexes.

If you are interested in all the articles, feel free to visit my whole Columnstore Series

This improvement has been live for quite some time, but somehow I never managed to blog about it, but this weekend I decided to do a couple of blogposts about the little big improvements that Microsoft has implemented for Columnstore Indexes. (see also Clustered Columnstore Indexes – part 53 (“What’s new for Columnstore in SQL Server 2014 SP1”))

In the past, I have already shown 3 different ways for capturing information on the amount of RowGroups read & skipped: (Using Trace Flag 646, Using Extended Events (and specifically column_store_segment_eliminate), and the newest way, available only on Azure SQLDatabase is to be found inside the execution plans).

Right now on Azure SQLDatabase we have the newest, and let me add by far the easiest way on getting the overview information for Segment Elimination of our queries – by simply using “SET STATISTICS IO ON” command.

I will use a freshly restored (transferred to Azure SQL Database) version of ContosoRetailDW database, where I will drop the primary the foreign keys from the test table FactOnlineSales:

USE [master]

alter database ContosoRetailDW
	set SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [ContosoRetailDW] 
	FROM  DISK = N'C:\Install\ContosoRetailDW.bak' WITH  FILE = 1,  
		MOVE N'ContosoRetailDW2.0' TO N'C:\Data\ContosoRetailDW.mdf', 
		MOVE N'ContosoRetailDW2.0_log' TO N'C:\Data\ContosoRetailDW.ldf',  
		NOUNLOAD,  STATS = 5;

alter database ContosoRetailDW
	set MULTI_USER;
GO

ALTER DATABASE [ContosoRetailDW] SET COMPATIBILITY_LEVEL = 120
GO
use ContosoRetailDW;

alter table dbo.[FactOnlineSales] DROP CONSTRAINT PK_FactOnlineSales_SalesKey;

alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimCurrency;
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimCustomer;
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimDate;
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimProduct;
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimPromotion;
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimStore;
GO

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales;

To see this new way of getting the Segment Elimination grouped and summarised one will need to execute a simple query agains a Columnstore table:

set statistics io on

select sum(SalesAmount), sum(SalesAmount) - sum(SalesAmount*prom.DiscountPercent)
	from dbo.FactOnlineSales sales
		inner join dbo.DimPromotion prom
			on sales.PromotionKey = prom.PromotionKey
	where DateKey > '2008-01-01 00:00:00.000';

Here are the results that I get:

(1 row(s) affected)
Table 'FactOnlineSales'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 3518, lob physical reads 0, lob read-ahead reads 0.
Table 'FactOnlineSales'. Segment reads 11, segment skipped 3.
Table 'DimPromotion'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Notice the 3rd line in the reported statistics – the most interesting part is here:
Table ‘FactOnlineSales’. Segment reads 11, segment skipped 3.
Mag-ni-fi-cent! We have all information aggregated and presented in a readable way without all the hustles that one needs to pass through in SQL Server 2014 – using Extended Events, storing data outside of SQL Server, importing into a table, and then running queries analysing the final results…
I am really hoping to have this feature in the upcoming SQL Server 2016, this is definitely a life-saviour for anyone running Columnstore Indexes.

Oh, and one more thing – If I could ask, I would love to have the total number of reads (sum) in the last line, which would help avoiding some trivial maths on every day basis)… :)

to be continued …