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.

How to Disable Batch Mode in SQL Server? - Interview Question of the Week #252 disable-batch-mode-800x288

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:

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

, , , , ,
Previous Post
How to Determine Read Intensive and Write Intensive Tables in SQL Server? – Interview Question of the Week #251
Next Post
MemoryGrantInfo – What are Different Status of IsMemoryGrantFeedbackAdjusted? – Interview Question of the Week #253

Related Posts

4 Comments. Leave new

  • Pinal,
    What made their queries perform poorly in batch mode when this is meant to be a big improvement?

    Reply
    • That is I am still figuring out. I think there are some queries just best to be used for rowstore. I will write a detailed post once I know the proper pattern about this one.

      Reply
  • Pinal,
    Did you do this with the database scoped configuration or were there so few queries that you did it as a hint in each one?

    Reply

Leave a Reply

Menu