Still Serial After All These Years

With each new version of SQL comes a slew of new stuff

While some changes are cosmetic, others bewildering, and the rest falling somewhere between “who cares about JSON?” and “OH MY GOD TAKE MY MONEY!”, but not really my money, because I only buy developer edition. Aaron Bertrand has done a better job finding, and teaching you how to find new features than I could. Head over to his blog if you want to dive in.

What I wanted to look at was something much more near and dear to my heart: Parallelism.

(It’s so special I capitalized it.)

In the past, there were a number of things that caused entire plans, or sections of plans, to be serial. Scalar UDFs are probably the first one everyone thinks of. They’re bad. Really bad. They’re so bad that if you define a computed column with a scalar UDF, every query that hits the table will run serially even if you don’t select that column. So, like, don’t do that.

What else causes perfectly parallel plan performance plotzing?

Total:

Zone:

  • Backwards scan
  • Recursive CTE
  • TOP
  • Aggregate
  • Sequence
  • System tables
  • OUTPUT clause

Other:

  • CLR functions (that perform data access)
  • Dynamic cursors
  • System functions

We’ll just look at the items from the first two lists. The stuff in Other is there because I couldn’t write a CLR function if I had Adam Machanic telling me what to type, cursors don’t need any worse of a reputation, and it would take me a year of Sundays to list every internal function and test it.

I’m going to depart from my normal format a bit, and put all the code at the end. It’s really just a mess of boring SELECT statements. The only thing I should say up front is that I’m leaning heavily on the use of an undocumented Trace Flag: 8649. I use it because it ‘forces’ parallelism by dropping the cost threshold for parallelism to 0 for each query. So if a parallel plan is possible, we’ll get one. Or part of one. You get the idea.

Just, you know, don’t use it in production unless you really know what you’re doing. It’s pretty helpful to use as a developer, on a development server, to figure out why queries aren’t going parallel. Or why they’re partially parallel.

All of this was run on 2016 CTP 3.1, so if RTM comes along, and something here is different, that’s why. Of course, backwards scans are probably close to 15 years old, so don’t sit on your thumbs waiting for them to get parallel support.

Backwards scan!

This is what happens when your ORDER BY is the opposite of your index.

This is what happens when your ORDER BY is the opposite of your index.

Scalar!

Not only do they run serial, but they run once for every row returned. Have a nice day!

Not only do they run serial, but they run once for every row returned. Have a nice day!

Table with computed column

Hint: the Id column isn't the computed one.

Hint: the Id column isn’t the computed one.

MSTVF

Multi-statement Table Valued Garbage

Multi-statement Table Valued Garbage

Table Variable Insert

You probably can't name one good reason to use a table variable.

You probably can’t name one good reason to use a table variable.

Top

Top o' the nothin!

Top o’ the nothin’!

Aggregating

You're making me wanna use Excel, here.

You’re making me wanna use Excel, here.

Row Number (or any windowing/ranking function)

You're gonna get all those row numbers one by one.

You’re gonna get all those row numbers one by one.

Accessing System Tables

Twisted SYSter

Twisted SYS-ter

The recursive part of a recursive CTE

This part was fun. I liked this part.

This part was fun. I liked this part.

Picture time is over

Now you get to listen to me prattle on and on about how much money you’re wasting on licensing by having all your queries run serially. Unless you have a SharePoint server; then you have… many other problems. If I had to pick a top three list that I see people falling victim to regularly, it would be:

  1. Scalar functions
  2. Table variables
  3. Unsupported ORDER BYs

They’re all relatively easy items to fix, and by the looks of it, we’ll be fixing them on SQL Server 2016 as well. Maybe Query Store will make that easier.

Thanks for reading!

Das Code

, , ,
Previous Post
Announcing the Office Hours Podcast (And 78% Off Videos)
Next Post
Meet Our Next Consultant: Tara Kizer

30 Comments. Leave new

  • “It’s so special I capitalized it.”

    Parallelism [sic]?

    Perhaps in an earlier draft. I know, picky, picky.

    Thanks for this article. I’m not sure I got it all but it was informative and useful.

    Cheers.

    Reply
  • So wait… Table Variables are bad and so are cursors? Then what should I use? Or is that a loaded question?

    I know I know, we shouldn’t have to enumerate through our data. But then again, I should be rich instead of just good looking. 🙂

    Hey thanks for the great article.

    Reply
    • Hi! I prefer temp tables to table variables, hands down. As for cursor replacements, it depends on what you’re trying to do.

      Reply
      • Can you briefly explain why, or point me to an article? I haven’t been writing queries long, but I have been trying to use table variables wherever possible because they seem to work a little faster and I also appreciate that they require less cleanup than temp tables. I’m not working with vast amounts of data typically; but I want to start good habits now if I can.

        Also, I’m still struggling to replace cursors in my algorithms with better logic; can you point me to any articles that might help me wrap my head around ways to do cursory things without cursors? Thanks!

        Reply
      • I can give you exactly one reason why you might want to use table variables over temp tables: internationalization.
        But still, table variables… who came up with them?

        Reply
  • N00b question here: if aggregates cause serial plans, then why is there a stream aggregate in the screenshot?

    Reply
    • Only the aggregation part will go serial, the “read-the-data-part” will be parallel. On the other hand, some aggregations could be become more tricky if they are parallel.

      Reply
      • Did a bit more research. Aggregation itself doesn’t cause a plan to go serial. A big part of the aggregation can be done in parallel. You just need a serial aggregation at the end to aggregate the different results of the parallel streams, which seems totally logical to me.

        So I don’t really agree with how aggregates are portrayed here in this blog post; as they are bad for parallelism. Algorithm-wise they can’t be calculated for 100% in parallel, but the heavy load of calculating the aggregate can be done in parallel.

        Reply
        • Hey Koen, it causes a serial zone in the plan, which was my only point. Several of the operators do, that’s why I differentiated between which each causes at the beginning of the post — zone or full serialization — so when people look at execution plans, they don’t think something is wrong when an aggregation is serialized.

          Reply
  • Alex Friedman
    January 7, 2016 5:17 am

    You mentioned sequences — how do they cause issues?
    Thanks

    Reply
    • Hi! Not sequences like the identity generating sequences. I’m referring to the Sequence (Project) operator that is used in execution plans that call windowing functions. It’s up there in one of the screen grabs.

      Reply
  • Great article.

    How about if the computed column is Persisted?

    Reply
    • No, doesn’t matter.

      USE [tempdb]
      SET NOCOUNT ON

      ;WITH E1(N) AS (
      SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
      SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
      SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
      SELECT NULL ),
      E2(N) AS (SELECT NULL FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j),
      Numbers AS (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E2)
      SELECT
      IDENTITY (BIGINT, 1,1) AS [ID]
      INTO [ComputedColumns]
      FROM [Numbers] [N]
      ORDER BY [N] DESC
      ;

      ALTER TABLE [dbo].[ComputedColumns] ADD [CompCol] AS [ID] * 2 PERSISTED

      SELECT [cc].[ID]
      FROM [dbo].[ComputedColumns] AS [cc]
      OPTION(QUERYTRACEON 8649)

      DROP TABLE [dbo].[ComputedColumns]

      Reply
  • I think you want “me” not “be” in the first paragraph after “PICTURETIME IS OVER” header.

    Great article.

    This isn’t really for posting… I think/hope these comments are moderated?

    Reply
  • I think inline TVF is not so bad

    Reply
  • This is a fantastic article, I keep coming back here to re read it.

    Just to clarify, do all computed columns cause serial plans, or just those using scalar functions?
    And does it cause a serial zone, or completely serial plan?

    I’ve often heard good things about computed columns, but this is definitely something to consider.

    Reply
    • Hey, thanks! So for computed columns, it’s only the kind that reference scalar functions, and as far as my testing has shown it’s the entire plan.

      Reply
      • Phew. Came here looking for this. I was plowing through the courses this weekend (hellooooo Black Friday deal!) and I thought you meant all Computed Columns, but your specific video on them made them seem magical.

        The heading for them here ^ also made me do a double-take. Glad to know it’s just the ones I’d never do, because functions are pretty bad all round.

        Reply
  • Hello Erik,
    I think for ranking functions you can get serial execution (Sequence Project +Segment)
    but parallel execution is also possible with new Window Aggregate operator that can use multiple threads.

    Simple example on Wide World Importers DB:
    SELECT ROW_NUMBER() OVER(PARTITION BY i.DeliveryMethodID ORDER BY i.InvoiceID) AS RN,
    i.InvoiceID
    FROM Sales.Invoices i
    INNER JOIN Sales.InvoiceLines il ON il.InvoiceID = i.InvoiceID
    /*DUMMY TABLE JOIN*/
    LEFT JOIN dbo.ForBatchModeOnly ON 1 = 2
    WHERE i.InvoiceID < 100000
    GO

    Reply
  • Accessing System Tables – The use of TOP kinda overshadows the demonstration of sys tables killing the parallelism doesn’t it?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}