USE PLAN and Compile Time

Joe Obbish shared an especially interesting problem with me recently. He had a query on a table with a single column, but introducing a USE PLAN hint would cause a compilation time of 40+ seconds. Here’s a simplified repro of his issue in case you want to stop reading here and work on this puzzle yourself.

 

On my 2016 test instance, I see a compilation time of 4 ms without the USE PLAN hint, and 3708 ms with it. Adding more to the SELECT statement quickly increases the time taken, but only with USE PLAN. What’s going on?

Before diving in, a quick note. While I try to use simple explanations, I’m also going to get down and nerdy here, using some technical terms and undocumented trace flags. Pretty much everything I know comes from Paul the Magnificent’s series here, so if you don’t know what a “memo group” or “input tree” is and actually want to learn, start there.

The first clue comes from Microsoft, who says that “You can only force plans with USE PLAN that can be produced by the optimizer’s normal search strategy.” So, apparently the optimizer is used to validate the hint. Let’s prove this.

Start with a basic setup and query that would be trivial (a query so simple that SQL Server says “Screw it, I’m not optimizing that. Have an obvious plan.”)

Add a USE PLAN hint and it gets a full optimization! We can even use a trace flag like 8619 to see that SQL Server really is doing the optimization work it bypassed earlier.

OK, so if USE PLAN needs the optimizer for validation, does it at least change the starting point of the search that the optimizer does? Nope!

Using the example of changing table order from here with 8605 and 8608, we can see that the input tree and initial memo remain the same. (Again, these are undocumented trace flags, which means everyone gets to point and laugh at you for causing a production outage if you use them there.)

Alright, so the optimizer always runs to validate the hint, but why does it take so much longer for Joe’s query?

That last bit of information comes from trace flag 8675, which exposes information about phases. Basically, SQL Server has to make tradeoffs between finding an excellent plan and actually getting it to you before the eventual heat death of the universe. Because of this, it does cost-based optimization in three phases. Each phase adds new options for SQL Server to test out, and it only goes to the next phase if the query looks expensive and complicated enough.

Here’s a query with no hint.

And here’s the same query with USE PLAN.

You can see this occur for any plan with a USE PLAN hint, even one that would be otherwise Trivial. They go straight into Search(2) optimization, the most comprehensive search space. This makes sense, because the goal is to check that the plan you supply is at all possible, even if it takes some strange rules to get there.

So what does the optimizer spend so much time on in Joe’s query? It’s testing hundreds of thousands of different ways to order the case statements.

How to Use the USE PLAN Hint

There’s a lot of good SQL Server information out there, so much so that I was very surprised I couldn’t find a good write-up of using the USE PLAN hint. I even checked the second page of Google results! So, in hopes of being the first to write a guide, or more likely to join the other dozen bloggers who wrote about this but didn’t bother with SEO, I present to you How to Use the USE PLAN Hint.

Say you have a simple query: you want to join two single-row heaps on their ID columns (I was going to say something about this being an unrealistic demo, then I realized that you probably work with developers too).

Setup:

Simple Query:

As you can see, this produces a hash join, even with such a low rowcount.

“But wait,” you say, “I can use the exotic USE PLAN hint to force a nested loop join! This kind of skill will finally demonstrate to my boss that I’m ready to be a Senior DBA!”

So here’s what you do: add the LOOP hint and get the estimated plan XML from right-clicking the plan. (Actual plan XML will work too).

Add the following to the end of your query, and paste the XML inside the single quotes. Mind the whitespace though, a linebreak at the beginning causes an error.

Voila! You have a loop join!

You can even manually edit the XML to switch the order of the table join!

There are a number of important caveats and gotchas with USE PLAN, but who cares? Go impress your boss!

A Plus Puzzle

Have you ever wondered how many plus signs you can put in front of a number before SQL Server throws a fit?

SELECT +++++++++++1

You haven’t?! That’s probably because you have production issues to solve, and I was just bored one afternoon. But are you curious now? Add enough +s and you’ll eventually get an error saying

Some part of your SQL Statement is nested too deeply.

If you’re like me (nerdy, intrigued, and without any production issues to solve right away), you’ll probably use something similar to the binary search algorithm to find the most + signs possible. Lucky for you, I already wrote the algorithm in T-SQL

Adding SET PARSEONLY to the query counts against the number of + signs. Add 1 to the final result as a true max if you’re mashing the + key N times.

I get 1015 + signs as a max in SQL Server 2016. Different versions got different results for me.

Bonus question: how many – signs can you use? Seriously, try it out by replacing the + with a – in the script. Then chuckle as you figure out what happened.

That Awkward First Post

The first post on a blog feels to me like the objective statement on a resume. I see everyone else doing it, so I feel like I have to also, even if it’s bland and everyone already knows what it’s going to say.

Seeking to utilize my experience and problem-solving abilities to effectively share knowledge and increase community collaboration!

Meh. I have my reasons for starting a blog, and as far as I can tell, they’re the standard ones. I’m not going to bore you with them. This post is just so I can feel like I’m making progress on WordPress.