Understand Your Plan: Computing Scalars

Blank Slate

This is awful. It really is. It’s so awful. These operators skated by undetected for years. Acting so innocent with their 0% cost.

Subprime operators, or something.

In this post, I’m going to show you how compute scalars hide work, and how interpreting them in actual execution plans can even be tricky.

Most of the time, Compute Scalar operators are totally harmless. Most of the time.

Like this:

SELECT TOP (1)
    Id = 
        CONVERT
        (
            bigint,
            u.Id
        )
FROM dbo.Users AS u;

Has this plan:

Paul White has a customarily deep and wonderful post about Compute Scalars, of course. Thankfully, he can sleep soundly knowing that my post will not overtake his for Compute Scalar supremacy.

I’m here to talk about when Compute Scalars go wild.

Mercy Seat

Compute Scalars are where Scalar User Defined Functions Hide. I know, SQL Server 2019, UDF inlining, blah blah blah.

Talk to me in five years when you finally upgrade to 2019 because your vendor just got around to certifying it.

Here’s where things get weird:

SELECT  
    @d = dbo.serializer(1)
FROM dbo.Badges AS b;

Operator times in the query plan don’t match up with the Query Time Stats in the properties of the Select operator. It executed for ~108 seconds, but only ~3 seconds is accounted for.

For some reason, time isn’t tracked for variable assignment. If we use a similar query to dump the results into a #temp table, it works fine:

SELECT  
    d = dbo.serializer(1)
INTO #b
FROM dbo.Badges AS b;

No wonder all the smart people are going over to MongoDB.

Aaron Bertrand

You know that guy? Never owned a piece of camouflage clothing. Blogs a bit. Has some wishy washy opinions about T-SQL.

Anyway, he recently wrote a couple conveniently-timed posts about FORMAT being an expensive function. Part 1, Part 2. The example here is based on his code.

SELECT  
    d = 
        CONVERT
        (
            varchar(50), 
            FORMAT
            (
                b.Date, 
                'D', 
                'en-us'
            )
        )
INTO #b
FROM dbo.Badges AS b;

Just a quick note that variable assignment of this function has the same behavior as the Scalar User Defined Function above, where operator time isn’t tracked, but it also isn’t tracked for the temp table insert:

If you saw this query plan, you’d probably be very confused. I would be too. It helps to clarify a bit if we do the insert without the FORMAT funkiness.

SELECT  
    d = b.Date
INTO #bb
FROM dbo.Badges AS b;

It only takes a few seconds to insert the unprocessed date. That should be enough to show you that in the prior plan, we spent ~60 seconds formatting dates.

Clams

Computer Scalar operators can really hide a lot of work. It’s a shame that it’s not tracked better.

When you’re tuning queries, particularly ones that feature Scalar User Defined Functions, you may want to take Computer Scalar costing with a mighty large grain of salt.

To recap some other points:

If operator times don’t match run time, check the Query Time Stats in the properties of the Select operator
FORMAT is nice and all, but…
Scalar User Defined Functions are quite poisonous

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.