SQL SERVER – ColumnStore Indexes Without Aggregation

During the recent Comprehensive Database Performance Health Check, I noticed there were lots of aggregation going on with various data in the table. I suggested that the client can experiment with the columnstore indexes and it may help the performance. When we attempted the option of using the index, we noticed a significant difference.

SQL SERVER - ColumnStore Indexes Without Aggregation columnstore-index-800x170

Now during the query execution, the DBA team really appreciated the performance of the query due to index. However, they were not sure if the column store index is a good idea if some of the queries were not using the aggregation. Essentially the question was – Do columnstore index really help regular query which is not using aggregations.

The answer to this question is – Absolutely Yes! 

Here is the query which I had run on the WideWorldImporters database. You may notice that there is no aggregation on the query. The table which I have used in the query is Sales.SalesOrderDetail. The table already contained one Column Store Index created on it.

Here is the query:

SELECT StockItemID, OrderID, OrderLineID
FROM Sales.OrderLines
WHERE StockItemID > 1 AND StockItemID < 999

When you run the above query, it gives the following execution plan:

SQL SERVER - ColumnStore Indexes Without Aggregation columnstoreindexexe

You may notice that the execution plan is using the Batch Mode and columnstore Index. I think it is a myth that columnstore index is only used during the aggregation operation.

Here are a few blog posts on the same subject which you may find interesting:

Let me know if you have any comments about the blog post. Please leave a comment. On a separate note here is the link to my Linkedin profile.

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

ColumnStore Index, SQL Index, SQL Scripts, SQL Server
Previous Post
Why Attend 21 Essential Scripts: Performance Tuning for Everyone?
Next Post
SQL SERVER – Override Server’s Configuration of Max Degree of Parallelism

Related Posts

Leave a Reply