While I am not a great fan of Columnstore Indexes in general, I still have implemented it when I realize that my clients will be benefited from this feature. There are many instances, where clients have created columnstore indexes without planning and I had to help them either to remove them or recreate the query and index to maximize the efficiency. Recently, one of the huge med-tech organizations hired me to help with the Comprehensive Database Performance Health Check and I had a very interesting conversation with their Senior DBA about ColumnStore Index Displaying Actual Number of Rows To Zero.
Let me explain what I meant.
We will be using the same setup from the blog post here. Once the setup is complete, let us run the following statement.
USE [AdventureWorks2017] GO -- Select Table with disable batch mode hint SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty FROM [dbo].[MySalesOrderDetail] GROUP BY ProductID ORDER BY ProductID GO
Now let us check the execution plan. When you see the execution plan, it says the Columnstore Index has processed zero rows. Look at the image displayed here carefully.
When you check the Columnstore Index scan operator, it says, it zero rows processed and right after that there is a Hash Match operator, it says it is processing 266 pages. It can be extremely confusing when we see Zero rows going to any operator and it returning 266 operators. My client was also very confused with this detail and asked me what is going on with the Actual Numbers.
Well, the answer to this is very simple.
ColumnStore – Actual Number of Rows To Zero
SQL Server Execution Plan has many limitations and one of the biggest limitations is that it has very little real estate compared to what it should be displaying. If you read XML execution plan you pretty much get lots of information but there are plenty of the limitations of the SQL Server Execution Plan in the current format.
If you are familiar with the column store index, you know that it does not read the rows in the “traditional ways” we know. It actually reads the column segments as the architecture of the column store is very different. As it does not read the traditional rows (via logical reads and physical reads), it displays the value zero there. However, if you look at the message by enabling statistics IO, you will notice the two different sections. One for the row store and another for column store.
Now when segment data are passed to the batch match operator, it has to still process the traditional rows which it gets (extracts) from the segment and start processing them together.
Additionally, if you look at the details from the Columnstore index operator, while it says the Actual Number of Rows to zero, it does process rows as Actual Number of Locally Aggregated Rows. When you add both of the numbers, you will find the total number of rows that are processed.
Read the following blog posts to understand the segments and how columnstore works.
- SQL SERVER – ColumnStore Frequently Asked Queries
- SQL SERVER – ColumnStore Indexes Without Aggregation
- How to List All ColumnStore Indexes with Table Name in SQL Server?
Future Improvement Expected from SSMS
I hope this explanation helps you to understand what actually going on when you are working with SSMS and ColumnStore Index. I hope that in the future release of the SSMS, whenever there is a column store index operator is displayed, instead of zero it will display the total of Actual Number of Rows and Actual Number of Locally Aggregated Rows.
I hope this blog post helps you to understand how column store indexes work a bit more. You are always welcome to ask me more questions.
Reference: Pinal Dave (https://blog.sqlauthority.com)
