With the increasing popularity of social media, it’s no surprise that more and more questions are being asked through platforms like Twitter. However, the limited character count on Twitter can make it challenging to provide detailed answers. Recently, I received a question on Twitter asking how to determine if a columnstore index is used by a query through the execution plan. While I initially provided a brief answer, I realized that it wasn’t sufficient and decided to write this blog post to explain the process in more detail.

To demonstrate this scenario, I will be using the AdventureWorks sample database. Here are the steps to follow:

1. Create a sample table
2. Insert some data
3. Create a clustered index on the table
4. Create a nonclustered columnstore index on the table
5. Enable the execution plan in SSMS
6. Run two SELECT statements, one using the clustered index and the other using the columnstore index

Let’s create the environment and populate the 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 up to 2-10 minutes based on your system's 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 and run the following two SELECT statements:

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

-- Select Table with Columnstore Index
SELECT ProductID, SUM(UnitPrice) AS SumUnitPrice, AVG(UnitPrice) AS AvgUnitPrice, SUM(OrderQty) AS SumOrderQty, AVG(OrderQty) AS AvgOrderQty
FROM [dbo].[MySalesOrderDetail]
GROUP BY ProductID
ORDER BY ProductID
GO

Both SELECT statements will return the same result set. To determine if a columnstore index is used, click on the Execution Plan Window. You will notice that there are different execution plan operators for the regular clustered index and the nonclustered columnstore index. When you hover your mouse over both operators, they will display an operator tip that clearly indicates whether it is a regular clustered index or a nonclustered columnstore index. In the case of the columnstore index, you will also notice that the execution mode is Batch instead of Row execution mode.

I hope this blog post has provided a clear explanation of how to determine if a columnstore index is used by a query through the execution plan. If you have any further questions, feel free to reach out to me on social media.

Thank you for reading!