Understanding Columnstore Indexes In SQL Server - Part Two

In my last blog, I explained what a columnstore index is. In this blog, we will dive into creating a clustered columnstore index and look at the performance differences the index can make.

Let’s get started.

Using AdventureworksDW2016CTP3 we will work with the FactResellerSalesXL table which has 11.6 million rows in it. The simple query we will use as a demo just selects the ProductKey and returns some aggregations grouping them by the different product keys.

First, we will run the query with no existing columnstore index and only using the current clustered rowstore (normal) index. Note that I turned on SET STATISTICS IO and TIME on. These two SET statements will help us better illustrate the improvements provided by the columnstore index. SET STATISTICS IO displays statistics on the amount of page activity generated by the query. It gives you important details such as page logical reads, physical reads, scans, and lob reads both physical and logical. SET STATISTICS TIME displays the amount of time needed to parse, compile, and execute each statement in the query. The output shows the time in milliseconds for each operation to complete. This allows you to really see, in numbers, the differences.

Looking at the results below, it completed five scans, 318,076 logical reads, two physical reads and read-aheads 291,341. It also shows us a CPU Time of 8233 milliseconds (ms) and elapse time of 5008 ms. The optimizer chooses to scan the existing rowstore clustered index with a cost of 91% and scanned the entire 11.6 million records to return the 395 record result set.

Another thing worth noting is if you hover over the Clustered Index scan you can see that the storage of this index is Row and the Actual Execution Mode is also Row.

Now let’s create the Columnstore index on this table.

Using the GUI right click on indexes and choose New Index then Clustered Columnstore Index.

Under the General table, all you need to do is name the index. If there are any objects in the table that are not compatible, you will see them listed in the highlighted section below. There are many limitations and exceptions to columnstore indexes, such as certain data types like text, ntext and image, and features like sparse columns. To best see the full list of limitations take a look at the docs Microsoft gives us here.

Because of the compression involved, creating a columnstore index can be very CPU resource intensive. To help mitigate that SQL Server gives us an option, under the Options tab, to overwrite the current MaxDop server setting for the parallel build process. This is definitely something you want to consider while creating a columnstore index in a production environment. For this example, we will leave as default. On the other hand, if you are building this index during downtime, you should note that columnstore operations scale linearly in performance all the way up to a MaxDOP of 64, and this can help the index build process finish faster at the expense of overall concurrency.

Per docs.microsoft

max_degree_of_parallelism values can be,