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:

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:

Here are the results that I get:

Set Statistics IO in Azure and Segment EliminationNotice 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 …

Leave a Reply

Your email address will not be published. Required fields are marked *