How to Disable Batch Mode in SQL Server? – Interview Question of the Week #252

Question: How to Disable Batch Mode in SQL Server?

Answer: I received this as a follow-up question to my previous blog post SQL SERVER – Simple Example of Batch Mode in RowStore. My immediate reaction was why would you disable batch mode in SQL Server when it is really is an awesome feature which will help us to improve the performance, however, just like everything there is always another side of the coin. Let me answer how you can disable batch mode for a single query in this blog post.

My client of Comprehensive Database Performance Health Check recently sent me an email with proof where he demonstrates there are hundreds of queries in their system where they are seeing tremendous improvement with the Batch mode for rowstore. However, there are two particular queries they are noticing the performance regression. What they want to do is to force the queries to not use Batch Mode.

Here is one way how you can disable query to use batch mode. We will be using the same setup from the blog post here.

-- Select Table with disable batch mode hint
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail]
GROUP BY ProductID
ORDER BY ProductID
OPTION(USE HINT('DISALLOW_BATCH_MODE'))
GO

If you run the above query without the hint with SQL Server 2019 compatibility level, you will notice that it will uses batch mode for the query performance. However, if you run that with the hint DISALLOW_BATCH_MODE, it runs like it is running in the earlier compatibility level and also disable the batch mode.

Once I provide the query to our client, they were extremely happy as after using the hint now every single query in their system providing amazing performance. Let us see a few related blog posts on this topic:

• SQL SERVER – ColumnStore Frequently Asked Queries
• SQL SERVER – ColumnStore Indexes Without Aggregation\
• How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153

Reference: Pinal Dave (https://blog.sqlauthority.com)