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)
What made their queries perform poorly in batch mode when this is meant to be a big improvement?
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.
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?
Only selected queries sir.