Nuts and Bolts
Deep within my little toolbox, is this little trick. Firstly, lets just set the record straight here, I don’t like little tricks, hacks and especially quick wins. However, with that said, sometimes you’re in a bind and you gotta get that query over the finishing line because that line at your desk isn’t getting any shorter (its actually Teams messages, but you get the point).
So, with that said, this is a little trick I once learnt from the T-SQL master Itzik Ben-Gan, whilst on a training course he delivered.
Batch Mode: ON
So what is batch mode? Batch mode sometimes turns the dial up to eleven on your queries, especially ones which have a bunch of analytical functions within them. Batch mode will allow the operators within your execution plan to work on ‘batches’ of rows instead of a row by row basis, this, of course, can do wonders for performance.
Normally, SQL server would only tread these murky waters if Columnstore indexes were present on any of your tables involved. However, we can fool SQL into performing the same behaviours in a number of ways.
Show Me All The Things
So let us see this thing in action. To ‘trick’ SQL server into thinking here, we are going to use a Columnstore index. However, unlike traditional Columstore indexes, this index is going to be tiny in terms of footprint and won’t even get used. (i know that sounds strange, bear with me).
First, let us turn on STATS IO and TIME. This allows us to verify any improvements we have made in terms of IO and CPU time
SET STATISTICS IO, TIME ON;
We will now execute a stored procedure which I have created. The contents of the stored procedure are not important here for this post. Ultimately, it just contains a bunch of aggregation functions which aren’t really doing much, other than erm, aggregating against the dbo.Comments table from the StackOverflow database.
USE StackOverflow2010; EXEC dbo.AllTheAggregations
Much like the content of the procedure, the results aren’t really important here either. However, let us have a look at the execution plan for this procedure
Nothing too crazy going on there as you can see. I will ignore the elephant in the room and choose not to mention the spill happening on that Hash Match, oh I just did.
Anyway, if we check a couple of the operators involved, we will see that they are all operating in row mode
Now let us do something. Let’s create a columnstore index on the dbo.Comments table
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCX_ZoomZoom ON dbo.Comments (Id) WHERE Id = -1 and Id = -2;
As you will see, something looks a bit strange there. In order for anything to get through to this index, the ID would need to equal both -1 and -2, which of course will never happen. However, that doesn’t matter.
Now we have a “Columnstore index” on the Comments table, let’s run our procedure again
USE StackOverflow2010; EXEC dbo.AllTheAggregations
Now, let’s take a look at the execution plan too. Of course, we can see a couple of new operators involved now.
However, let’s have a look at the execution mode now for a couple of the operators
Interesting, so now most of our operators are executing in batch mode. This is pretty cool and has a great improvement to the stored procedure we have been using. Let us compare the output of the statistics IO and TIME
As you can see above. We have about a 40% improvement on CPU time alone, just from adding our Columnstore index that will never even get populated or used. Not a bad improvement by anyone’s opinion.
Having that Columnstore index on the Comments table has enabled SQL to use Batch mode execution across a few of the operators seen in our execution plan. This enables much more efficient processing of the data rather than the row mode executions we saw earlier. Batches of row (up to 900 at a time) can be processed simultaneously.
Just for transparency, we will take a closer look at that index we created using the great open-source tool sp_BlitzIndex from another SQL master Brent Ozar
EXEC sp_BlitzIndex @DatabaseName = 'StackOverflow2010', @SchemaName = 'dbo',@TableName = 'Comments'
As you can see above, our index NCCX_ZoomZoom has an insanely small footprint at 0.1MB. Additionally, we can see that it has 0 scans/seeks, 0 deletes and 0 updates. Nice.
When all is said and done
Of course, the differences in the above example are not gigantic, however, I hope it gives a picture of what enabling batch mode can sometimes achieve. I have had scenarios at work where enabling batch mode in such ways has taken literally an hour or two of query run times. Sure, this is not something I would advocate using often but when you need to get something running better (or running at all) until you can implement a better solution, its good to have a trick or two up your sleeve such as this one.
There are numerous ways to achieve such behaviour within SQL Server. For more examples, I would recommend checking out the excellent Kendra Little blog here
Thanks for reading
