More about batch mode

Learn which operators are available in batch mode in different verisons of SQL Server in this article, (Scroll down to the table of batch node operators).

Read [Niko’s article on why changes in SQL Server 2016 RTM can cause queries to no longer get batch mode operators](http://Columnstore Indexes – part 85 (“Important Batch Mode Changes in SQL Server 2016”) http://www.nikoport.com/2016/06/21/columnstore-indexes-part-85-important-batch-mode-changes-in-sql-server-2016/) (when using index hints).

Transcript

We are going to change gears a little bit now and have some fun with hacking in batch mode.

We’re going to focus a little bit more on what batch mode operators can do and why we might want to have them even if we aren’t really using a columnstore index.

We’re going to look at a different table now. We’re going to look at a table named agg.FirstNameByYearState and it looks like I have the table name a little wrong in my comment there so don’t tell anyone that I fixed that.

We’ve got an index on this table that is just a normal, old, rowstore index

It’s on four key columns and it includes one column. So we’re going to go ahead and create that index on the agg.FirstNameByYearState table. agg.FirstNameByYearState is an aggregate table so it contains for each year, for each FirstNameId, the total number of babies born, the name count for babies for a given FirstNameId, StateCode, Gender, and report year, and it does not have one row per baby, in other words.

We’re going to run a procedure that is not great code

I’m going to go ahead and create this. It’s called PopularNames and PopularNames, I created it purposefully to be kind of bad so it’s a little slow and I’m proud of that.

It takes a parameter called threshold and it does some running totals for different years and what it’s going to figure out is if you give me a given threshold, say the threshold that we like to test with is 100,000.

What I’m going to do is look at, for a given state, for a given FirstNameId, StateCode, and Gender, at what point did that name break through that threshold. So in what year did a baby suddenly become that popular where if we say 100,000 for that given FirstNameId, StateCode, and Gender, when did they break through that barrier? Like, last year they only had, you know, 90, 99,000 babies total who had that name but as of this year we have 101,000 babies who have that name.

To figure this out we’ve got to do a running total and then look at what was the running total this year versus the prior year and then we’ve got some kind of complex stuff in there about the threshold at the end with wheres and ands and stuff, just kind of making this a fun, slow query to play with when it’s run with certain values.

On our first run, we’re going to use that 100,000 baby threshold and we’re off to the races

Even when the data is in memory, this isn’t even a very big table, but because of everything it’s doing with the running totals it turns out to be a lot of work even against a small table because what we’re saying is for every FirstNameId, StateCode, and Gender, we’ve got to do all of these different running totals and see when they pass this threshold. I am using in the query, I am using windowing functions.

It’s not that I’ve done this the old way. I am doing parts, you know, these partition by order by windowing functions and I’m using lag in the next one but this is a pokey query to run.

So we’re at 42 seconds, it’s still chugging on and we’re going to see if we can get the actual plan. All right, so our query has finished up.

Let’s check out the execution plan

Well, I’ve got a warning on the properties and let’s take a look. Click and make sure we can see what the warning is on the properties. SQL Server is saying hey, I granted this about 1 MB of memory, it only used about 16 KB. Now, a meg isn’t a huge amount of query workspace memory to give it but it ended up using just a fraction of that so we got the warning. Looking at our query execution plan, what happened in here?

Well, I keep scrolling to the right, a lot happened in here. We scanned our nonclustered index. So it did use our nice, nonclustered row store index on the table and it, the scan totally makes sense. I’ve got to do a running total for each FirstNameId and then figure out for that FirstNameId, for the state and the Gender, I can’t just do a seek or anything, I’ve got to figure out when they broke through this and we’ve got multiple window function aggregators. Notice that each of my operators I’ve clicked on, these are all in row mode, we aren’t using batch mode and we don’t have any columnstore indexes on our table.

So SQL Server currently and SQL Server 2016 service pack one is just going to use row mode for all of these.

We have segment operators, we have a window spool operator and a stream aggregate, more segment operators. So we’re working through our windowing function and we’ve just got so many operators that this has to flow through that do a lot of work. Now, we can look at, if I click here, just up to this stream aggregate, these are all row mode so we can, if we want to figure out where we’re spending our time, we can see that by this time in the query, we’re at, and this is for it and it’s children, we’re at 75 seconds already.

We can kind of go down and see if we want to see, okay, at this operator, how much time were we at? We can kind of figure out that was 70,000. Okay, if I go back to the sequence project, at that time I was at about 64 seconds, but we can kind of see it’s just building up through these operators because there’s a lot of leg work to be done the way that we’re doing this query.

Can we speed this up with batch mode operators? I’m going to create a really weird columnstore index.

I’m putting it just on one column, on StateCode in our table. Our query uses a lot more columns than StateCode and I’m creating this columnstore index as one that just won’t be very attractive for our query because our query needs a lot more than StateCode.

We need FirstNameId, we need Gender, we need lots of stuff, but I’m just creating the columnstore index and I’m now going to go ahead and run the exact same query with the exact same threshold and SQL Server sees that a new index has been created on a table and wow, this query was way faster than a minute.

This query took 2.7 seconds. When I look at the execution plan, things are a little bit different.

Notice that I didn’t have to scroll very far to the right. We have many fewer operators and we have different operators. We still have a nonclustered index scan. This is of our disk-based rowstore index.

That is not a columnstore index, right? That’s index scan, nonclustered and when I look at the properties, this is just our plain old, this is row mode, right, this is our plain old, let’s grab the object’s name down here just to prove it. Let’s make that column a little better. Right, this is ix_irstNameByYearState_FirstNameId blah blah blah. Right, no cheating here, that is not a columnstore index and this is not a columnstore index either.

This is a clustered index seek. SQL Server saw, hey, that columnstore, that nonclustered columnstore is on StateCode, I don’t want to use that for this, but if we look at our window aggregate here, we were in row mode for the index scan but it passed the data into a window aggregate operator and when we look at the execution mode on it, it is batch execution mode and in fact this window aggregate operator, is a new batch mode window aggregation operator in 2016.

SQL Server wasn’t able to use it, the first time I ran this query because there was no columnstore index on the table.

I created this sort of super lame columnstore index on one column and it decided not to use it but now it had the option to use the batch mode window aggregate operator and when we look at the actual time statistics which are just for it, not for its child, it’s pretty speedy doing its window aggregation, right.

So our index scan took about 2.2 seconds to grab all that data from that row store index but our window aggregate operator’s really fast. We’ve got to do a little work in a compute scalar, which was 29 milliseconds and then we’re going into another window aggregate operator which is, I don’t know what kind of magic they worked in this batch mode window aggregate operator, but man that’s fast and we end up being able to do all this magic that used to be spread out across all those, you know, segment project and different operators just in a few operators that are just like magically fast using batch mode, ‘cause this window aggregate operator is the new 2016 magic batch mode operator and in terms of what operators can use batch mode, with increasing versions of SQL Server, we’ve had, kind of, you know, with different versions new capabilities.

Which capabilities you can use will depend on your SQL Server version as well as things like your database compatibility mode. So it’s worth paying attention to all these little settings in SQL Server.

But, wow, that made it really fast. Well, even though it chose not to use the columnstore index, it was able to do it.

So what if we did something a little differently.

What if we added a hint and we wanted to make sure that it didn’t use that nonclustered columnstore

I showed you that it chose not to, but what if we added a hint to the query and said I want to make sure that you don’t. Well we may run into something a little different here. I’m going to create a procedure named PopularNamesWithAHint and the only difference in it is that it says ignore the nonclustered columnstore index and I’m just going to do an estimated plan here. So this little symbol is sort of like, this is supposed to be like a little thinking bubble with the execution plan and it’ll show me an estimated plan right away without running it and when I look at my estimated plan, we are back to that one minute plus execution with all of the segment operators and the sequence project operator.

We are back to the slow operator, or the slow execution plan and we do not have the super cool batch mode operator.

Now Niko, who writes excellent information about columnstore, he has a post where he points out that this is a change that we got recently, in some cases right now in 2016, at compat level 130, if you have a columnstore index on a table and you force SQL Server to not use it by way of a hint and this might be either an index hint forcing a row store index or in my case it was that ignore nonclustered columnstore, this now disables batch mode.

In some cases if you lower your database compat mode to 120, going back to earlier behavior, you can get the batch mode back even though you’re forcing it to not use the columnstore, but Niko warned in the post, you may miss out on many improvements that you get from compat mode 130, where just many things in columnstore are made faster. So I would say the lesson to take from this is if you are using columnstore or if you’re using any of the hacks I’m going to show you or if you’re just using columnstore to keep in mind that index hints may disable batch mode. I would try to work around that and get the performance I want while staying at the highest compat mode because I get lots of performance improvements for columnstore with the highest compat mode too but of course the final decision is up to you.

Just know that index hints at compat mode 130 may disable batch mode on your operators.

I’m going to drop our goofy columnstore because you don’t want to be creating a columnstore index on a single column to be getting batch mode

You want to be either be creating a real full columnstore index on all the columns you want it for and really using it or if you have a case where you just aren’t ready to fully use columnstore yet and maybe it’s because of the insert rates on the table, maybe it has to do with just not being ready to manage columnstore, maybe there are reasons that it’s just not suitable for your table, but you still want batch mode, there are some clever hacks out there that can get you batch mode without really using columnstore and it’s way better than a single column columnstore index.