Some of the learnings can lead to another exploration when someone does stump you with something you didn’t know. To understand the basics of batch mode and row mode, read the blog SQL SERVER – ColumnStore Index – Batch Mode vs Row Mode where I have explained the concepts.
This blog got inspired by the fact when one of the readers after reading this blog came back to me and asked, how do I know that the query that I am working has aborted from Batch processing and reverted to row processing. At a glance, I felt this was a trivial question and didn’t think much. But the question kept haunting me for a while. I incidentally, was at a Microsoft conference and got to ask this question with bunch of experts as a side conversation.
The first answer I got was, did you check Extended events? I was pleasantly surprised because I didn’t think it that way. Since I come from the school of using Profiler for quite some time this was not a natural thought. As soon as heard that, I cranked my SSMS to check. Guess what – I got to an event expression_compile_stop_batch_processing which exactly did fit my requirement.
I went to read the description as: Occurs when an expression is not natively supported in batch processing mode and a wrapper of row-by-row evaluation is used.
So this event is raised when the query aborts from batch processing to row mode. This I personally felt was a great learning and exploration for me when working with ColumnStore Indexes. Since this was a SQL Server 2014 instance, some of the functions that short circuit to row mode include:
- UNION/UNION ALL, SELECT/COUNT DISTINCT, INNER JOIN, ORDER BY
- addition, subtraction, and multiplication, and 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 (strings)
- and, or, in, not,
I know this may not be exhaustive and as the versions upgrade, some of these limitations are getting addressed. Hope you learnt something new today and feel free to let me know your thoughts via the comments below.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
The list above means almost every query I have ever written is executed in row mode as I often use inner joins, distinct and other items of that list.
Same here. Nearly every query I have written has at least one of the values from that list.
Running SQL Server 2017, the same query used batch mode in the Developer edition, but resorted to row mode in the Standard edition, with a 89% improvement in query time when batch mode occurred. The query was using “COUNT DISTINCT” in addition to other aggregations (Min/Max/Avg).