This is the very first blog post in a new series on the Batch Mode, welcome & enjoy!

I will be going into every detail of Batch Mode that I know off in the upcoming blog posts (yes, there will be a lot of them), but in the very first one I decided to make even more public the case I have been pushing for the last 8 months: implementing Batch Mode for RowStore.

Batch Mode

Batch Mode is a special query processing mode, which targets a very significant improvement for the execution plans where a big number of rows is being worked on.

Batch Mode looks to reduce the number of CPU instructions to process a row on average, in this way achieving some amazing overall speed improvements.

Introduced in SQL Server 2012 for the first time for the mass market and currently implemented only for the Columnstore indexes, depending on the situation you might get even over 20 times improvement in the execution time.

For what I understand in Columnstore Indexes – the biggest speed improvement for it in reality comes not from Compression & Segment Elimination but from the Batch Mode. With Segment elimination we might be able to improve the amount of data read and process, but this improvement can bring us a couple of times overall improvement, where the real speed comes from the CPU instructions optimisations.

You might ask – what is the reason of publishing this series of articles outside of Columnstore series that I am doing for over a year ?
The answer is extremely simple – to my knowledge, there are no logical reason for Batch Mode to work with Columnstore Indexes exclusively.
RowStore can greatly benefit from it.

Batch Mode for Row Store?

Reducing the number of CPU instructions per row on for some of the execution plan’s iterators for a huge query in Datawarehousing will show the benefits immediately by delivering query results in a matter of seconds instead of minutes or hours,
but for the OLTP systems even a small improvement will be multiplied by the number of queries running on this system simultaneously. The total difference is something that should be measured carefully.

I understand that in the most cases OLTP systems are running with MAXDOP = 1, but everyone of us knows a couple of hundreds of example where this is not true.
Also, please take a look at this Connect Item that Rick Kutschera from BWin.Party has submitted a couple of months ago: Clusterd Columnstore and DOP 1

Another important point is that as far as I understand the current (SQL Server 2014) implementation of the Batch Mode, the Delta-Stores are already being processed in Batch Mode which means that the algorithm itself is already able to work with a b-tree HEAPs (Row Storage), so there is no effort in re-inventing the wheel.

For all this I have opened a Connect Item yesterday which I kindly ask you to upvote:
Implement Batch Mode Support for Row Store, should you agree that Batch Mode is needed not only for the OLAP type applications but for the OLTP as well.

to be continued …