Query Processing Architecture Guide

• 02/21/2020
• 89 minutes to read

Applies to: SQL Server (all supported versions) Azure SQL Database

The SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. The following topics cover how SQL Server processes queries and optimizes query reuse through execution plan caching.

Execution modes

The SQL Server Database Engine can process Transact-SQL statements using two distinct processing modes:

• Row mode execution

Batch mode execution

Row mode execution

Row mode execution is a query processing method used with traditional RDMBS tables, where data is stored in row format. When a query is executed and accesses data in row store tables, the execution tree operators and child operators read each required row, across all the columns specified in the table schema. From each row that is read, SQL Server then retrieves the columns that are required for the result set, as referenced by a SELECT statement, JOIN predicate, or filter predicate.

Note

Row mode execution is very efficient for OLTP scenarios, but can be less efficient when scanning large amounts of data, for example in Data Warehousing scenarios.

Batch mode execution

Batch mode execution is a query processing method used to process multiple rows together (hence the term batch). Each column within a batch is stored as a vector in a separate area of memory, so batch mode processing is vector-based. Batch mode processing also uses algorithms that are optimized for the multi-core CPUs and increased memory throughput that are found on modern hardware.

Batch mode execution is closely integrated with, and optimized around, the columnstore storage format. Batch mode processing operates on compressed data when possible, and eliminates the exchange operator used by row mode execution. The result is better parallelism and faster performance.

When a query is executed in batch mode, and accesses data in columnstore indexes, the execution tree operators and child operators read multiple rows together in column segments. SQL Server reads only the columns required for the result, as referenced by a SELECT statement, JOIN predicate, or filter predicate.
For more information on columnstore indexes, see Columnstore Index Architecture.

Note

Batch mode execution is very efficient Data Warehousing scenarios, where large amounts of data are read and aggregated.

SQL Statement Processing

Processing a single Transact-SQL statement is the most basic way that SQL Server executes Transact-SQL statements. The steps used to process a single SELECT statement that references only local base tables (no views or remote tables) illustrates the basic process.

Logical Operator Precedence

When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic, and bitwise, operators are handled before logical operators. For more information, see Operator Precedence.

In the following example, the color condition pertains to product model 21, and not to product model 20, because AND has precedence over OR.

SQLCopy

SELECT ProductID, ProductModelID

FROM Production.Product

WHERE ProductModelID = 20 OR ProductModelID = 21

  AND Color = 'Red';

GO

You can change the meaning of the query by adding parentheses to force evaluation of the OR first. The following query finds only products under models 20 and 21 that are red.

SQLCopy

SELECT ProductID, ProductModelID

FROM Production.Product

WHERE (ProductModelID = 20 OR ProductModelID = 21)

  AND Color = 'Red';

GO

Using parentheses, even when they are not required, can improve the readability of queries, and reduce the chance of making a subtle mistake because of operator precedence. There is no significant performance penalty in using parentheses. The following example is more readable than the original example, although they are syntactically the same.

SQLCopy

SELECT ProductID, ProductModelID

FROM Production.Product

WHERE ProductModelID = 20 OR (ProductModelID = 21

  AND Color = 'Red');

GO

Know More (Link to PDF)