Intelligent Query Processing in SQL Server: Batch Mode on Rowstore

As you already know from Chapter 27, the Database Engine supports a feature called Batch Mode on Columnstore using columnstore indices. This feature can execute a query that processes millions or billions of rows more efficiently by including the rows in batches (up to 1000 rows in each batch) and executing batches rather than individual rows. That way, performance of queries can be significantly increased. (For a detailed discussion of this mode, see the “Batch Mode on Columnstore” section in Chapter 27.)

Even if a query does not involve any table with a columnstore index, the query optimizer of the Database Engine in SQL Server 2019 uses heuristics to decide whether to use Batch Mode or not. The heuristics consist of:

  • An estimation of how many rows are in the input query, as well as which operators are used for the execution of that query
  • Additional checkpoints to discover new and more optimal execution plans for the query

That way, the query optimizer can use batches of rows in certain cases even if the columnstore index is not specified.

We will use examples similar to ones presented in Chapter 27 to show a query in which the query optimizer uses Batch Mode on Rowstore. First, Example 28.7 (which corresponds to Example 27.9) increases the number of processors to four, using the max degree of parallelism (MAXDOP) advanced configuration option.

NOTE The query in Example 28.7 uses the same tables created in Examples 27.6 and 27.7. Therefore, if your sample database already contains the FactlnternetSales, DimCustomer, and DimDate tables, start with Example 28.7. If not, first create these tables using Examples 27.6 and 27.7.

Example 28.7

EXEC sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC sp_configure ‘max degree of parallelism’, 4;

GO

RECONFIGURE WITH OVERRIDE;

GO

We increase the number of processors to four because Batch Mode processing uses algorithms that are optimized for multicore CPUs, and this processing mode is not a choice if the number of processors is one, which is the default value.

Example 28.8 creates a nonclustered non-columnstore index.

NOTE: If you have already created the columnstore index called CLI_CS_IFactInternetSales (see Example 27.10), you have to drop it to disable Batch Mode on Columnstore. To drop the index, use the following statement:

DROP INDEX FactInternetSales.CLI CS IFactInternetSales

Example 28.8

USE sample;

CREATE NONCLUSTERED INDEX nCLI_IFactSales

ON dbo.FactInternetSales

(OrderDateKey, CustomerKey, SalesAmount) ;

The main difference between the sequence of statements in Chapter 27 (Examples 27.6 through 27.10) and the sequence of statements in this chapter is the type of index created in Example 28.8. Here, we create a non-columnstore index, whereas Example 27.10 created a columnstore index. That way, we disable Batch Mode on Columnstore, which requires creation of a columnstore index. In spite of this difference, the query optimizer uses Batch Mode for the query in Example 28.9, too. (The query in Example 28.9 is identical to the query in Example 27.10.)

Example 28.9

USE sample;

SELECT c.CommuteDistance,

  d.CalendarYear,

SUM(f.SalesAmount) TotalSalesByCommuteDistance

FROM dbo.FactInternetSales as f

INNER JOIN dbo.DimCustomer as c ON

f.CustomerKey = c.CustomerKey

INNER JOIN dbo.DimDate d ON

d.DateKey = f.OrderDateKey

GROUP BY c.CommuteDistance, d.CalendarYear;

Figure 28-6 shows the execution plan of the query in Example 28.9 in general and the properties of the Index Scan operator in particular. Two properties are important in relation to Batch Mode on Rowstore: Estimated Execution Mode and Storage. The former has the value Batch and the latter the value RowStore. This means that the query is processed in Batch Mode, although the corresponding columnstore index is not created.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *