Blog

When I do performance tuning for clients, I really pride myself on making as few changes as possible in order to make a tremendous difference. I consider it a failure when I have to tell someone to rewrite a bunch of queries from scratch.

However, there are some cases where I just can’t work around a perfect storm of anti-patterns. To understand why, let’s take an example. I’ve kept the general idea the same, but I’ve rewritten the entire example in the Stack Overflow database to protect the innocent.

The most resource-intensive query was a stored procedure – let’s say it looked for interesting users who live in the same location as you, who you might like to meet, because they’ve commented on the same answers that you have:

Uh oh – it called a table-valued function, ufn_UsersYouMightLike. What’s in there?

Uh oh – it’s getting worse. It populates a table variable, and we know those have massive problems. Then, it queries that table variable, and it has a scalar function in the where clause. What’s in that fn_HasCommentedOnAnAnswer?

Hoo boy. We’ve got some work to do. We have a few problems to fix:

• A table variable – producing bad row estimates, underestimating costs, and probably stopping us from getting parallelism
• A multi-statement table-valued function – which makes performance tuning way harder for my developers, who couldn’t see what was really happening inside the stored procedure
• A scalar function – which the above function is going to run row by row, and it’s going to inhibit parallelism too

I’m going to unpack all of ’em, and I could probably fix ’em in any order. Let’s start with the scalar.

Fixing the scalar function by inlining it

My general advice on scalars is to take their logic out and try to inline them wherever they’re called. In our case, the calling query looks like this:

So in a perfect world, I’d open up fn_HasCommentedOnAnAnswer, copy the logic out, and paste it directly into the WHERE clause here. Let’s open it up and see if I can do it:

Drat – they’ve got multiple statements in here. They’re setting a configuration variable, and then using that variable in a subsequent query. The more queries you find in the scalar, the harder the rewrite is going to be.

In this case, I’m going to do a very fast and dirty rewrite. I bet the @PostTypeId is a config variable that doesn’t even change very often, and I could probably hard-code it, but I’m not going to go quite that dirty. Here, I’m just going to dump the config variable into a subquery.

Instead of this:

I’m going to do this:

I know – not elegant – but it does get my scalar function down to just one query:

Which means that now I can take that one SELECT query, copy it out, and paste it into the query where it was being called, ufn_UsersYouMightLike. Here’s the part of that function that was calling the scalar:

If I copy out the scalar’s logic and embed it into the WHERE clause, it’ll look like this:

Ugh. I’m not proud of that, but at least the scalar’s out of the way for now. Next up…

Fixing the multi-statement TVF by inlining it

After inlining the scalar, I’m dealing with an outer function that looks like this:

If you’ve been through the functions module in Fundamentals of Query Tuning or in Mastering Query Tuning, you’ll recognize the problem: this function has multiple queries in it, meaning it’s a perilous multi-statement TVF. Its true cost and work won’t show up in the calling query (the proc), so our developers had no idea how bad it really was.

Like with scalars, my general advice here is to try to turn them into inline (single-statement) functions. Again, the more statements you have, the harder this is – but here, it’s not too bad. Let’s just take the statement that was populating the table variable:

And shove that into the FROM clause, which used to be:

The fast duct-tape way of doing it would be to simply paste it in:

But let’s clean that up a little:

Now I can set aside the table variable, and this whole thing turns into one statement, a magical inline table-valued function:

We still have the stored proc calling a function, mind you – but I’m trying to change the bare minimum of things I can to suddenly get the client across the finish line.

So how does it perform?

I really wanted to show you before-and-after numbers, dear reader, but the “before” query was still going after 45 minutes – and I’m only using the small 10GB StackOverflow2010 database here!

The improved version runs in under a second! Granted, the execution plan isn’t pretty:

And there are obvious improvements we could make to the code and indexes, but…who cares? IT RUNS IN UNDER A SECOND! Rather than chasing further improvements to that one, I’d hand that to my developers, walk them through the changes we made, and then talk about the next terribly long query to go tune. In most cases, the big bang for the buck will be taking those 45-minute-long queries and turning them into just 1 second, rather than turning 1 second queries into 200ms queries.

Recap and lessons learned

When I show this to a developer, there are usually hugs and sobbing involved. They believed – rightfully – that if a feature ships in SQL Server, then it should perform well. That just isn’t the reality, sadly: table variables, scalar UDFs, multi-statement TVFs – these are all things that the documentation don’t warn you about. Sure, they compile – but they sure don’t scale.

These two function examples were simple on purpose because this is a blog post, not a doctoral thesis. When you get into functions that have several statements, each building on the next, building tables and fetching configuration data, this just gets more laborious. It’s not mentally hard, mind you, it’s just hard work, rolling up your sleeves to combine queries into one big ugly monster – or rewriting it from row-by-row functions to a set-based approach inside a stored procedure.

The earlier a senior database developer can get involved in the design and code review process, the less work gets wasted by other developers who don’t know that SQL Server just can’t handle those, nor implicit conversions, dynamic WHERE clauses with OR/ISNULL/COALESCE, etc, things that compile – but don’t scale.

To learn more about why – and how to work around ’em – check out my free Watch Brent Tune Queries videos, and then graduate to the Fundamentals of Query Tuning course.

When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here’s a simple example:

The more parameters you have, the crazier the code gets, and the harder it becomes to produce really fast plans across all the parameter combinations.

But what if a lot of the queries look similar?

For example, what if your search page has a set of defaults, and the vast, vast majority of searches just use the defaults?

In our example above, what if the vast majority of the time, people always searched for users in London whose Reputation = 1? There’s no reason we can’t put a short-circuit at the top of our stored procedure:

In complex stored procedures, this short circuit buys me a few cool advantages:

You can build a hand-crafted query. You can restructure the T-SQL to be more performant, add index hints, query hints, trace flags, you name it, things you wouldn’t normally want to add to dynamic SQL on the fly.

You can get the data from somewhere different. For example, at one client, 99% of product searches were looking for the “Item of the Day” on sale – and that product list only changed once per day. We simply built a new table with the necessary product info, populated it once per day on a schedule, and changed the search procedure to something like this (obviously with a different table name):

For that client, it made a phenomenal difference in eliminating joins, memory grants, permissions checks, and sorts. (The table only had one row in it.)

You skip the overhead of building a dynamic string. I know it sounds trivial, but when you’re dealing with tens of thousands of queries per second, this can be a big deal.

How to know if this tactic makes sense

Warning: run-on sentence incoming.

1. If you run sp_BlitzCache and your top most resource-intensive queries use dynamic SQL,
2. And a little further down in the results, you see a dynamic string that was built by one of these procs,
3. And when you compare the outer proc’s resource utilization numbers (CPU, reads, duration, etc) number to the dynamic SQL’s resource utilization, and it looks like that one dynamic SQL line is a big chunk,
4. And you think you could do better tuning that particular branch of dynamic SQL by hand,
5. And the other branches wouldn’t benefit from you just tuning the main string itself,
6. Then it’s a good fit.