Implement Batch Mode Support for Row Store
I would love to see the Batch Mode implemented for the Row Store, it would bring a major improvement to the query execution plans performance.
With current data growth explosion almost no OLTP system is working on couple of hundreds of rows, the real numbers are moving very fast into millions.
OLTP systems can't be viewed only from the perspective of writing the data, where in fact small amounts of data are processed, but when dealing with a constantly increasing number of rows read - this situation needs to be addressed.
I constantly see systems where index scan operators are bringing great amounts of data (200 steps statistics on a complex distribution over 10 million rows can't always give a good estimate and so for those cases this is even more important), and bringing some basic Batch Mode support would be a major improvement for SQL Server engine.
The actual decision on whether to action Batch Mode for RowStore can be left for Query Optimiser, which in it's turn should base it on the statistics (if we are handling 10 rows, than we are better off with Row Mode definitely).
Anywhere we are using Hash Join, there is a potential for an improvement in OLTP Systems and since the functionality itself is implemented for Columnstore, transferring it to Rowstore should not be the same amount of work as starting it from the beginning.
Right now the Batch Mode is limited only for Columnstore Indexes, and with some of the current limitations, such as locking, update performance, loading they are prohibitive for a good number of OLTP systems.
Upvotes: 81<=-=Aug 5 2014 8:28AM=-=>
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 per se is already able to work with a b-tree HEAPs (Row Storage).<=-=Mar 3 2017 9:38AM=-=>
I’m working on this feature right now. It won’t make the next version, but I’d really like to talk to you about what use cases you see for it. There is more to the work than just enabling the optimizer to consider batch mode plans over a rowstore.
I’ll start an offline conversation about some of the particulars.
Matthias Vill commented
Isn't this comming in SQL Server 2019?