SQL SERVER – ColumnStore Index – Batch Mode vs Row Mode

What do you do when you are in a hurry and hear someone say things which you do not agree or is wrong? Well, let me tell you what I do or what I recently did.

I was walking by and heard someone mentioning “Columnstore Index are really great as they are using Batch Mode which makes them seriously fast.” While I was passing by and I heard this statement my first reaction was I thought Columnstore Index can use both – Batch Mode and Row Mode. I stopped by even though I was in a hurry and asked the person if he meant that Columnstore indexes are seriously fast because they use Batch Mode all the time or Batch Mode is one of the reasons for Columnstore Index to be faster. He responded that Columnstore Indexes can run only in Batch Mode.

However, I do not like to confront anybody without hearing their complete story. Honestly, I like to do information sharing and avoid confronting as much as possible. There are always ways to communicate the same positively. Well, this is what I did, I quickly pull up my earlier article on Columnstore Index and copied the script to SQL Server Management Studio. I created two versions of the script. 1) Very Large Table 2) Reasonably Small Table. I a query which uses columnstore index on both of the versions. I found very interesting result of the my tests.

I saved my tests and sent it to the person who mentioned about that Columnstore Indexes are using Batch Mode only. He immediately acknowledged that indeed he was incorrect in saying that Columnstore Index uses only Batch Mode. What really caught my attention is that he also thanked me for sending him detail email instead of just having argument where he and I both were standing in the corridor and neither have no way to prove any theory.

Solarwinds

Here is the screenshots of the both the scenarios.

1) Columnstore Index using Batch Mode

SQL SERVER - ColumnStore Index - Batch Mode vs Row Mode batchmode

2) Columnstore Index using Row Mode

SQL SERVER - ColumnStore Index - Batch Mode vs Row Mode rowmode

Here is the logic behind when Columnstore Index uses Batch Mode and when it uses Row Mode. A batch typically represents about 1000 rows of data. Batch mode processing also uses algorithms that are optimized for the multicore CPUs and increased memory throughput.  Batch mode processing spreads metadata access costs and overhead over all the rows in a batch.  Batch mode processing operates on compressed data when possible leading superior performance.

Here is one last point – Columnstore Index can use Batch Mode or Row Mode but Batch Mode processing is only available in Columnstore Index. I hope this statement truly sums up the whole concept.

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

Solarwinds
Previous Post
SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video
Next Post
SQL SERVER – Follow up – Usage of $rowguid and $IDENTITY

Related Posts

4 Comments. Leave new

  • Dear,

    For testing I have made columnstore index in my one of the table but it is giving me the following error while update.

    UPDATE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, then rebuilding the columnstore index after UPDATE is complete.

    Reply
  • once you add a column store to a table, though, you cannot delete, insert or update the data – it is READ ONLY.

    Reply
  • Hi Pinal ! What are the factors that influence optimizer to choose between Row vs Batch mode,

    Reply

Leave a Reply

Menu