Have you ever wondered how SQL Server processes your queries? Do you know the difference between batch mode and row mode? In this blog post, we will explore these concepts and learn how to identify when a query switches from batch processing to row processing.

Batch mode and row mode are two different execution modes used by SQL Server to process queries. Batch mode processing is optimized for columnar data storage, such as the ColumnStore Index, while row mode processing is optimized for traditional row-based storage.

In a recent blog post, I discussed the basics of the ColumnStore Index and the differences between batch mode and row mode. One of my readers asked me how to determine if a query has switched from batch processing to row processing. At first, I thought it was a trivial question, but it got me thinking.

During a Microsoft conference, I had the opportunity to ask this question to a group of experts. One of them suggested checking Extended Events, which surprised me because I was used to using Profiler. I quickly opened SQL Server Management Studio (SSMS) and found the event expression_compile_stop_batch_processing, which was exactly what I needed.

The expression_compile_stop_batch_processing event is raised when an expression is not natively supported in batch processing mode and SQL Server switches to row-by-row evaluation. This event allows us to identify when a query aborts from batch processing to row mode.

In SQL Server 2014, some functions short circuit to row mode, including UNION/UNION ALL, SELECT/COUNT DISTINCT, INNER JOIN, ORDER BY, addition, subtraction, multiplication, division, SUM, AVG, MIN, MAX, IS/IS NOT NULL, CASE WHEN/CASE WHEN NOT, IN/NOT IN, HAVING, EXISTS/NOT EXISTS, CAST, CONVERT, CTE, ISNULL(), DATEADD, TOP, BETWEEN, <, >, <=, >=, <>, = (equal, not equal for strings), and logical operators (and, or, in, not).

It’s important to note that this list may not be exhaustive, and as SQL Server versions upgrade, some of these limitations are being addressed.

I hope you found this information helpful and learned something new today. If you have any thoughts or questions, please feel free to share them in the comments below.