If this is the kind of SQL Server stuff you love learning about, you’ll love my training. In general, it’s a good strategy when building larger #temp tables, but at this point I add a tablock hint to every #temp table insert at first to test things out. If it is, you may not see the full performance gains from getting it. There are many good reasons to want a fully parallel insert, but you need to make sure that the bottleneck isn’t earlier in the plan. Not exists is just an easy example of that Any reference to the table you're inserting into be prepared to see weird things if you use OUTPUT #AvgComments_BrokenTablock WITH (TABLOCK) But that's not gonna be the case with #temp tables Also, if there's a trigger or indexed view on the target table SET COMPATIBILITY_LEVEL = Anything_Less_Than_130 If you’re doing any of this stuff, like, bye. This will not get you a fully parallel insertĭROP TABLE #AvgComments_NoTablock Doesn’t Work: A Whole Laundry List Of Stuffīasically any one thing quoted out has the ability to deny the parallel insert that we’re after. Without the tablock hint, this will get you the plan we don’t want, where the insert operator is outside the parallel zone. This *will* get you a fully parallel insert, unless goofiness is involved.ĭROP TABLE #AvgComments_Tablock Doesn’t Work: INSERT, without TABLOCK Rather than selecting directly into a table, here we’re gonna create the table and issue an insert statement with the tablock hint. anything listed below in the "broken" selectĭROP TABLE #AvgComments_SelectInto Works: INSERT, with TABLOCK This will "always" work, as long as you don't do Goofy things will be explained later in the post. For the purposes of this thread: attention, please Works: SELECT INTOĪs long as you don’t do anything too goofy here, a fully parallel insert will “always” be allowed, here. The thing to keep an eye out for is the insert operator being in the serial zone. Let’s look at some scenarios where a fully parallel insert is allowed, and then not allowed. The story gets a little more complicated when inserting into a columnstore index, but this post isn’t really about that. A big contrast between variables and #temp tables is that the former fully disallows parallelism in modification queries, and the latter do not. One of the best ways to see that is with insert queries that do a lot of work. Serial zones in parallel plans can leave a lot of performance on the table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |