SQL SERVER – Identify Columnstore Index Usage from Execution Plan

I think there was a time when lots of questions were coming via either email or blog comments. Nowadays, the trend seems to change. Most of the question I receive is through social media. Here is the latest question I received through Twitter. The best or worst part of Twitter is that it allows only 140 characters, so I’ve noticed that a question is easy to ask on Twitter, but an answer is difficult to provide using this social network.

The question I received at https://mobile.twitter.com/pinaldave is as follows:

How do I know if columnstore index is used by query through execution plan?

Well, my reaction was simple – “From Operators“. See, I managed to answer this in two words only. However, I was not satisfied with my own answer. The questioner did not follow up after this question but I personally did not like my answer. I felt that if he was talking about execution plan, he is indeed aware of operators. If he was aware of operators, my answer was no use to him. Suddenly, I thought I was arrogant (or at least not clear). I sent him a direct message that I would write a blog post tomorrow to explain how to figure out if columnstore index is used or not via execution plan.

If you are interested in Columnstore Index, read following related posts on my blog  for additional details:

To demonstrate this scenario, I have created the following script. I am using AdventureWorks (note: AdventureWorks Installation 60 Seconds Tutorial) for this sample database. Here are the steps which are to be followed:

  • Create a sample table
  • Insert some data
  • Create clustered index on it
  • Create nonclustered Columnstore Index on it
  • Enable execution plan in SSMS
  • Run two SELECT statement together with using clustered indexand columnstore index (use hint if needed)

Let us create environment and populate tables.

USE CLAdventureWorks
-- Create New Table
CREATE TABLE [dbo].[MySalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
-- Create clustered index
CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail] ( [SalesOrderDetailID])
-- Create Sample Data Table
-- WARNING: This Query may run upto 2-10 minutes based on your systems resources
INSERT INTO [dbo].[MySalesOrderDetail] SELECT S1.*
FROM AdventureWorks.Sales.SalesOrderDetail S1
GO 10
-- Create ColumnStore Index
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore] ON [MySalesOrderDetail] (UnitPrice, OrderQty, ProductID)

Now enable the actual execution plan in SSMS.

Run following two SELECT statement together.

-- Select Table with Clustered Index (Not Columnstore)
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail] WITH(INDEX([CL_MySalesOrderDetail]))
-- Select Table with Columnstore Index
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail] GROUP BY ProductID

Both will return the same result set. Click on Execution Plan Window.

You will see that there is different execution plan operator for regular clustered index and non-clustered columnstore index.

When you hover your mouse on both operators, they open operator tip which also clearly indicates if it is a regular clustered index and non-clustered columnstore index.

In case of the columnstore index, you will notice that the execution mode is also Batch instead of Row execution mode.

I guess now this completes the answer for the question asked to me on Twitter. Let us quickly clean up.

-- Cleanup
DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail] GO
TRUNCATE TABLE dbo.MySalesOrderDetail
DROP TABLE dbo.MySalesOrderDetail

Feel free to ask me any question on social media – twitter or facebook.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index
Previous Post
SQL SERVER – A Tricky Question and Even Trickier Answer – Index Intersection – Partition Function
Next Post
SQL SERVER – Migrate a SQL Server Reports from one server to another server

Related Posts

5 Comments. Leave new

  • Hi
    i am having a small query
    is it mandatory to have a clustered index before adding a columnstore index

  • nakulvachhrajani
    May 14, 2012 11:15 pm

    Column store indices use batch mode, which I guess is the reason why they are faster, under certain situations.

    • That is one of the reason. As the data is stored in the Pages in columnar format It becomes very fast while calculating aggregates for those columns. As It compresses data to a much higher extent because of the probability of similar data in a column is large, It uses less memory while performing the operations ,hence making the query execute faster.


Leave a Reply Cancel reply

Exit mobile version