A Little About Parallel Insert Restrictions In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the intricacies of fully parallel insert restrictions in SQL Server, particularly focusing on a lesser-known issue that can impede performance. Erik Darling from Darling Data shares his insights and experiences, highlighting common pitfalls such as non-enlightenable scalar UDFs, triggers, index views, and the use of `SET ROWCOUNT`. He also discusses how columnstore indexes can affect parallel inserts due to row group compression requirements, suggesting strategies like using `ROW_NUMBER()` instead of identity or sequence columns for better performance. Throughout the video, I provide practical examples and tips on optimizing your insert operations, ensuring you get the most out of SQL Server’s powerful features.
Full Transcript
Erik Darling here with Darling Data. It just never gets better. Never gets worse either. In today’s video, we’re going to talk about fully parallel insert restrictions. There’s pretty good documentation in general around the stuff that will prevent you from getting a fully parallel insert plan.
But in this case, I’m going to talk about one that I haven’t seen written up anywhere, which I’ve seen bite a lot of people in the butt. Now, fully parallel inserts are very, very useful if you need to move a lot of data from one place to another, especially into temp tables because a lot of people use fairly big amounts of data in temp tables.
And it’s usually a good thing if you can do that insert across multiple threads because you can often reduce the total runtime of your insert query when it does that. So it’s a good thing to have.
It’s a little trickier when you start dealing with inserts into tables that have columnstore indexes on them, especially clustered columnstore indexes, because of the compression stuff. Now, for columnstore indexes, you need to have a certain number of rows get inserted for it to qualify for compression on insert.
Otherwise, it ends up in these uncompressed row groups. And so if your fully parallel insert splits the rows up too finely, if there’s too few rows per thread, each one of those threads will end up inserting into a new uncompressed row group.
And that can slow things down, actually. But for inserting into either a rowstore heat table, I’m not sure if there’s a columnstore heat table. Probably not.
Or inserting into a rowstore clustered index. That will certainly be beneficial even with multiple threads because you don’t necessarily have to worry about any of the columnstore stuff. But before we do that, of course, we need to do my usual song and dance about me and you and our lives together moving forward.
If you like this content, because it’s free, you can continue to appreciate this free content by liking and commenting and subscribing. If you would like to support this channel and my endeavors to bring you high quality, in-depth SQL Server information, you can sign up for a very low cost membership.
There’s a link below in the video description. And if you can spare four bucks a month, you know, that’s cool too. You will pay for… Well, I don’t really go out for coffee.
Most of my coffee consumption is via my espresso machine. But if you want to help me pay off my espresso machine, you can give me four bucks a month. If you need help with your SQL Server, if you are having performance or health issues or anything else really, let me know.
My rates are reasonable. If you need some high quality, low cost SQL Server training, because you maybe don’t want to hire someone, you just want to learn and do it on your own. Maybe you just want to get better at SQL Server generally.
You can get all of my training, 24 hours of it. Beginner, intermediate, and expert level training for about $150 US dollars for life with that there discount code. There is a link, of course, in the show description, video description.
I’m almost sure if this qualifies as a show to do that. If you would like to see me live and in person, you can do that in Seattle, Washington this November 4th and 5th. I will be there co-hosting two days, not one, but two full days of performance tuning madness with Kendra Little.
If there’s an event nearby you that you think I would be a good fit for, let me know what it is. If I can get a pre-con submission accepted, I’ll show up and do my song and dance just for you. I mean, if one person buys a ticket, I’m not sure that that would qualify.
Not just for you, but for hopefully a room full of people eager to learn more about SQL Server performance. And with that out of the way, let’s do some stuff. Let’s be festive. Let’s enjoy our, let’s enjoy ourselves what we can.
Now, a while back, there was a blog post about this published at that URL. That will also be in the video description. It’s not there yet because the video doesn’t exist.
But once it does, that link will be there. And there are some good things, there’s some good information in that blog post about what’s not allowed. So if you’re on 2016 or Azure SQL DB or Managed Instance, you need to be in compat level 130 or better in order for this to take effect.
Well, that’s partially true. You can use the option use hint database compatibility level thing and set that to 130 for this to work. There’s kind of a funny note after that that says, In addition, it is recommended to use a suitable SKU from the premium service tier to ensure that the IO and CPU requirements of parallel insert are satisfied.
Well, you know, if, you know, Microsoft, if I have to buy a premium service tier to make sure that a parallel insert is, you know, properly satisfied from a CPU and IO perspective, I think we need to talk a little bit about how you’re doing these service tiers. I think you might be taking us for a ride a little bit.
Hmm? I think maybe this is kind of crappy rent seeking behavior on your part. You know? Just saying.
So there’s some other more common things that will prevent a parallel insert, like the usage of any non-enlightable scalar UDFs. So that would be a problem generally. If there are triggers on the table or index views reference the table, then the parallel insert goes away because that would really make life difficult on both triggers and index views.
I can only imagine the insanity and deadlocking that might occur. Set row count. If you’re still using set row count, God bless.
It’s time to join the future. And by the future, I mean like 2012 or something. Don’t use set row count.
Goofy. The output clause only affects the table that is being output to. Now, this is, I mean, well, it’s not exactly a departure. So with output, you do have to be careful because if you output to the client, right?
So like if you just put an output clause in your, you know, modification query, insert, update, delete, or merge, and you just return that data out to SQL Server Management Studio, then that will force the entire plan to be single threaded. Ditto if you output to a table variable because outputting to a table variable has the same restrictions that modifying a table variable does in any other way, where modifications to a table variable force the whole query plan to run on a single thread.
They disallow parallel plans. In this case, though, outputting to another table that is not, obviously not SSMS because it’s a table, or a table variable, just means that the insert from the output portion will be single threaded, but the insert select to the normal table will be parallel.
Parallel insert is only used when inserting into a heap without any additional nonclustered indexes. It is also used when inserting into a columnstore index. So that’s fun there.
You know, I could have sworn that I’ve seen it for empty rowstore clustered indexes, but, you know, I could be goofy on that. In any case, it might be a good idea to, if you are the type of person, and I like the type of person who thinks about indexing temp tables, it might be a good idea in that case to do your insert and then create your clustered index later.
Sometimes the timing difference is worth it. So, you know, often, you know, if you do the insert with a empty clustered index on there and you don’t get the fully parallel insert, you might want to do the insert, then create the clustered index, because it might, like, depending on how long it takes a clustered index to create, you might see an improvement there.
And also, watch out when identity or sequence is present, because either one of those will mess things up. What I greatly prefer for this is rather than use identity or sequence, and this is a neat trick that I picked up sort of along the way, is to use row number instead. Right?
So, if you are inserting into a table that has an identity or a sequence on it and you want to keep going over that, you do need to sort out what the current max value of that is before you, you know, do the row number thing, because you have to add that value to the row number.
If you just want to, like, dump a whole bunch of data into a table and you want to have, you know, just like a surrogate primary key on it, like you’re going to loop over stuff in the table or you just want it there for whatever weird, safe reason you find helps you sleep better at night, use row number instead of an identity or sequence on there because you’ll get better performance on the insert.
So, let’s talk about one thing that’s not in this list, because this is an important thing. So, let’s pretend that we have a really important process that wants to do a parallel insert and we create a table called parallel insert. Again, it is an empty heap table.
There is nothing, no indexing going on here. And if we look at the query plan for this, now I just have the enable parallel plan preference hint on here to make things easy. We’re going to insert into that table with a tab block hint because, you know, that’s always a good idea to make sure you get that fully parallel insert.
Select into will almost always get you that in compat level 130 or better as long as the plan qualifies. But, the tab block hint certainly helps urge things along. If we look at the estimated plan for this, we’ll see that the insert is before the gather streams, right?
So, we have our insert right here and then our gather streams right here. So, the insert happens within the parallel zone. The thing that’s not documented is if you do something like this.
So, let’s say that, you know, you do, you know, you do, you know, various passes on a table or something and you do, you reference your table somewhere else in the query. Like, you say, and not exists because you don’t want to put stuff in there that already exists. Even if, you know, both, like I haven’t put any data in the parallel insert table, so there should be nothing.
There’s really no not exist to happen. But if you look at this, you’ll see that now the insert is outside of the parallel zone. There’s a gather stream.
Now there’s a gather streams here and a table insert here. So, this would certainly prevent that from happening. So, a lot of the times when I see processes like this running, you know, I want to see if the parallel insert would be useful. I have to put data into yet another table and reference that table in the not exists in order to make sure that, you know, we aren’t getting screwed up there.
So, just a couple quick notes on parallel insert stuff there. You know, not a whole lot to say beyond that. I hope you enjoyed yourselves. I hope you learned something.
I hope that you will continue to watch this channel and keep me in bats company over here because I don’t know what we’d do without you. You would get very lonely just talking to each other all day, wouldn’t we, bats? Yes, we would!
Mwah! Love you, bats! Anyway, thank you for watching. Goodbye. Bye!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
