Not all computed columns generate serial plans – only those with scalar functions. Even when you’re not referencing that actual column. Even when they’re used in computed columns in one of the tables. SELECT statements, on the other hand, don’t neccessarily. UPDATE, INSERT and DELETE on table variables cause a completely serial plan. Oh, and, as soon as I had just finished the post, I realized Erik Darling wrote a post on this a few years ago.
Here’s a list of features/operations that I’ve found will need a serial plan or serial zone. If you’ve ever tried to make an XQuery run parallel, you probably already know everything in this post.
I didn’t bother with in-memory tables, XML, JSON and CLR procedures. I also iterated through each service pack respectively – so that’s 12 versions all in all.įor each service pack level, I ran through a script that I’ve created to try to force a parallel plan, given enough data (real, or a billion fake rows) and trace flag 8649. I set up a 16-core Azure VM with 64 GB of memory, and installed Developer Edition of SQL Server 2012, 2014, 20. I read up on this stuff a number of years ago (including a great post by Paul White), and thinking that some things must have changed since, I decided to go see for myself. Some operations in SQL Server will turn your entire query plan serial (single-threaded), others will just reserve a so-called “serial zone”.
0 kommentar(er)
