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.
This feature will be available under database compatibility mode 150 for SQL Server 2019 and in Azure SQL Database (available today).
Matthias Vill commented
Isn't this comming in SQL Server 2019?