How Functions Can Make Performance Tuning SQL Server Queries Harder

Sensational


I’ve posted quite a bit about how cached plans can be misleading.

I’m gonna switch that up and talk about how an actual plan can be misleading, too.

In plans that include calling a muti-statement table valued function, no operator logs the time spent in the function.

Here’s an example:

SELECT TOP (100)
     p.Id AS [Post Link],
     vs.up,
     vs.down
FROM dbo.VoteStats() AS vs --The function
JOIN dbo.Posts AS p
    ON vs.postid = p.Id
WHERE vs.down > vs.up_multiplier
AND   p.CommunityOwnedDate IS NULL
AND   p.ClosedDate IS NULL
ORDER BY vs.up DESC

When I run the query, it drags on for 30-ish seconds, but the plan says that it only ran for about 2.7 seconds.

SQL Server Query Plan
As we proceed

But there it is in Query Time Stats! 29 seconds. What gives?

SQL Server Query Times From Execution Plan
Hi there!

Estimations


If we look at the estimated plan for the function, we can see quite a thick arrow pointing to the table variable we populate for our results.

SQL Server Query Plan
Meatballs

That process is all part of the query, but it doesn’t show up in any of the operators. It really should.

More specifically, I think it should show up right here.

SQL Server Query Plan

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.



One thought on “How Functions Can Make Performance Tuning SQL Server Queries Harder

Comments are closed.