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:

• SQL SERVER – Fundamentals of Columnstore Index
• SQL SERVER – How to Ignore Columnstore Index Usage in Query
• SQL SERVER – Updating Data in A Columnstore Index
• SQL SERVER – Video – Performance Improvement in Columnstore Index

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.

CREATE DATABASE CLAdventureWorks
GO
USE CLAdventureWorks
GO
-- 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
) ON [PRIMARY] GO
-- Create clustered index
CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail] ( [SalesOrderDetailID])
GO
-- 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)
GO

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]))
GROUP BY ProductID
ORDER BY ProductID
GO
-- 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
ORDER BY ProductID
GO

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
GO
DROP TABLE dbo.MySalesOrderDetail
GO

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

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