Batch Mode On Rowstore

Kevin Farlee announces another query processing improvement in SQL Server 2019:

In the SQL Sever 2019 preview, we are further expanding query processing capabilities with several new features under the Intelligent Query Processing (QP) feature family.  In this blog post we’ll discuss one of these Intelligent QP features that is now available in public preview, batch mode on rowstore. This feature unlocks the advantages of batch mode execution in cases where there is no columnstore participating in the query.

Batch mode is a different execution mode primarily targeted at analytics queries which are characterized as scanning many rows, and doing significant aggregations, sorts, and group-by operations across these rows.  Batch mode has been reserved for queries which involve columnstore  indexes until now.

Performing scans and calculations using batches of ~ 900 rows at a time rather than row by row is much more efficient for analytic-type queries.  For queries that can take advantage of it, batch mode can easily make queries execute many times faster than the same query against the same data in row mode.

To date, the workaround you could use was to create an empty filtered columnstore index on a rowstore table.  This solution is more architecturally pleasing and means one less hack to explain.

Related Posts

Iterative Solutions To The Closest Match Problem

Itzik Ben-Gan has a follow-up article looking at row-by-row solutions to the closest match problem: Last month, I covered a puzzle involving matching each row from one table with the closest match from another table. I got this puzzle from Karen Ly, a Jr. Fixed Income Analyst at RBC. I covered two main relational solutions that […]

Read More

Speeding Up The First Responder Power BI Interface

Kellyn Pot’vin-Gorman hits the Go Faster button: The gist of this kit is that it is a database repository as part of the sp_BlitzFirst to collect monitoring alerting and performance metric data. Once you’ve set this up, then you can use a Power BI desktop dashboard as an interface for all that data.Now this is an awesome […]

Read More

Categories

September 2018
MTWTFSS
« Aug Oct »
 12
3456789
10111213141516
17181920212223
24252627282930