SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions

Update

This seems to finally be fixed as of CU12 for SQL Server 2017. If you’re not already patched up, head over to the link to get there.

Estimated Plans Need Not Apply

This is, of course, only available in actual plans.

All together now:

But uh. Those numbers look weird to me.

Do they look weird to you?

Now, look, I’m not a math guy, but…

This is messed up

Stick with me, here.

If I run this query, QueryTimeStats registers 0 CPU and Elapsed time.

Zero Bueno

If I run this query…

Your mom

Our query that previously used 0 CPU time is now using about 9 seconds of CPU time, but the UDF is only responsible for about 3 milliseconds of it.

Ooooookayyyyyy

What’s the function doing?

Something that uses a whole lot more than 3 ms of CPU time.

Here’s the query plan for it.

Rorre

Isolation

What if we only run the function?

Tater chips

What if we take the table access out of the picture?

Tater Salad

Now, look, selecting 10 integers from a values clause doesn’t generate 8.5 seconds of CPU time on its own.

If it does, I don’t think you’re on a version of SQL Server that even supports the values clause.

What if we just run the query that’s in the function?

Tater Face

Pattern Forming

The query inside the function takes ~853ms of CPU time. The elapsed time is 143ms. The disparity here is due to parallelism. The plan uses a DOP of 6, which increases the CPU time to decrease the elapsed time. Hopefully. Usually.

When scalar valued functions run, they do so per-row returned by the query. Whether we use the Users table, or the values construct, our top 10 query invokes the function 10 times.

If you multiply the number of function executions by the CPU and elapsed time stats for the query inside the function (853 and 143, respectively), you’ll get about to where the CPU and elapsed times land for both the values clause and the query against the Users table.

I don’t expect millisecond timing to be perfect. Threads are weird, as a wise man once said.

But showing the UDF with a value of 3ms is wildly incorrect.

Thanks for reading!

Brent says: I’m not mad. I’m not even disappointed. I’m happy that plans are continuing to get additional investment from Microsoft, and I know this bug is gonna get fixed because whoever wanted this feature bad enough to get it coded is also going to be bummed out about the implementation. It’ll get there eventually, just like wait stats in execution plans – more on that in tomorrow’s post.

Previous Post
The 2018 Data Professionals Salary Survey Results
Next Post
First Responder Kit Release: It’s Too Cold Not To Do A Release

4 Comments. Leave new

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":""}