What To Do If sp_BlitzFirst Warns About High Compilations

Compiles Aren’t The Same As Recompiles

If you’re seeing high RECOMPILES, this post isn’t for you. We’ll talk about the differences between compiles and recompiles, but not how to troubleshoot recompiles.

Recompiles mean one of two obvious things: You have a RECOMPILE hint at the stored procedure or statement level, or SQL found a reason that a new query plan had to be compiled, despite the presence of what was once a perfectly good query plan.

The reasons for this are usually sound:

• Table definition changed (columns, constraints, triggers, defaults)
• Index(es) added or dropped
• Statistics were modified, or judged to be out of date

There are other rules that deal with ANSI SET options, which is why I usually beg people not to change those unless they have good reason to. Although not all of the SET options trigger recompiles, here’s a list of the ones that do. You may also run into a situation where the use of temp tables in your code causes recompilation (scroll down to the section called ‘Recompilation Thresholds’).

Compiles, on the other hand, are when SQL gets a query, and either hasn’t seen it before, or doesn’t know that it HAS seen it before. So it compiles a plan for it and sends it on its way. SQL can either be very smart or very dumb about this. Sort of a mix Between ‘Finding Dory’ and ‘Memento’, but with less water.

Are Compiles A Problem?

In the same breath that sp_BlitzFirst warns you about High Compiles/Sec, it may also warn you about CPU utilization being high. If the two go together, this could be a problem worth looking into. They usually do, so it usually is. Shot, chaser, hangover.

Compiling plans is CPU-intensive stuff. Even if they’re not huge plans, and even if it’s just for a handful of queries. When they’re executed frequently they can really gang up and make your CPUs work harder than your liver on an airplane.

Those poor CPUs that you paid thousands of dollars to license SQL Server on. There are better things for them to do, you know.

Let’s Look At A Few Ways To Troubleshoot This

This post assumes that your server has ‘enough memory‘, and isn’t facing memory pressure issues that can cause the plan cache to get flushed out. It also assumes that you don’t have any wayward jobs executing reconfigure options that wipe your plan cache, like changing MAXDOP, Cost Threshold for Parallelism, Max Server Memory, etc.

It also assumes that you’ve addressed other high CPU causes, like rubbish indexes, implicit conversion, wonky functions, and a host of other things. For example, you’re not running on a 2-4 core VM where all your queries are going parallel across 1.8GHz cores and you have missing index requests that look like the wine prices at Le Meurice.

You may be seeing high CMEMTHREAD waits, but in general resource contention is low. You’ve tuned your queries and indexes, and things still suck. Now what?

Signs and Wonders

The first question you should ask yourself is: am I passing in a bunch of queries-as-strings from my application? If you’re not sure what that looks like, it’s probably something like this:

I’m not going to say ‘shame on you’ for not using stored procedures, but here’s one place where using them can have some really positive benefit: plan caching and reuse. If you are using stored procedures and still facing this issue, keep reading to find out why. There are some limitations to what SQL Server can safely cache and parameterize.

If you’re totally opposed to stored procedures, I’d point you to sp_prepexec to get similar behavior without sticking all your code in stored procedures. I know, it’s more typing, but think about how busy you’ll look doing it. Your boss will think you’re Powerdeveloper 5000.

Even THAT might be too much work for you! So there’s a database-level setting called Forced Parameterization which, as the name implies, forces SQL Server to parameterize all those yucky-duck, string-concatenated queries instead of treating them like individual queries in need of their own plans. The downside here is that they’ll be more sensitive to parameter sniffing, so you want to make sure that your indexes are spot on for your queries, and possibly think about taking other steps to branch code for values that will return many rows vs. values that only return a few.

Another downside is that Forced Parameterization has some limitations, which are discussed at the link. Not every part of every query can be parameterized, and there are some very common query patterns listed here that could leave your queries in the lurch.

And no, I don’t endorse RECOMPILE or using OPTIMIZE FOR hints here. The RECOMPILE hint puts you right back to the problem you’re trying to solve, and OPTIMIZE FOR UNKNOWN is… well, something you’d tell your stomach before eating fast food.

You could try OPTIMIZE FOR (specific value), but unless it’s a value that you’ve artificially injected into your database so you can control its pervasiveness, you can run into trouble down the line if that value grows in an unanticipated way, or is no longer a meaningful presence compared to other stored values. And then you’re tasked with making sure your artificial data isn’t causing you other problems, since you now have to back it up, restore it, check it for corruption, etc.

Hinting Plans

Somewhere in the middle exists a couple query level hints. These aren’t perfect either. Just like stored procedures, sp_prepexec, and forced parameterization, they’ll make your queries more sensitive to parameter sniffing. But hey, if your problems are high compilations and high CPU, these are all things that can offer relief, and maybe that once-in-a-while bad query plan is well worth the trade off. I’m talking about OPTION KEEP PLAN and KEEPFIXED PLAN.

To quote Microsoft:

KEEP PLAN
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes have been made to a table by running UPDATE, DELETE, MERGE, or INSERT statements. Specifying KEEP PLAN makes sure that a query will not be recompiled as frequently when there are multiple updates to a table.

 

KEEPFIXED PLAN
Forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.

That means no matter which hint you choose, you’re going to have to pay very careful attention to whether or not the query plan you lock in is always appropriate. If your workload is consistently a bunch of single row look ups, you’re pretty safe. If you’re running customer reports for some people who have 10 orders, and some people who have 100,000 orders, you may not be.  Fun! Data! Mosquito bites!

But… you still have to KNOW what’s compiling. And how do you do THAT? You’d think it would be the simplest thing. sp_WhatIsCompiling, or something. Right? Well, not exactly. Brent would tell you to use Profiler, and that’s totally valid. But it’s CURRENT_YEAR, and Brent is busy antagonizing his illegal sturgeon farm into laying more eggs for his caviar bath.

Extended Event Planning

There are two interesting Extended Events for this.

If you’re using stored procedures and having this problem, sp_cache_miss can help you find which stored procs are looking for existing plans, not finding them, and bailing out to compile.

If you’re using more ad hoc code, query_pre_execution_showplan, is your best bet. It’s more of a catch-all, and will show you everything that’s compiling.

These ONLY work on SQL Server 2012 and up. If you’re on an earlier version, you’re going to want to crack open Profiler, and use SP:CacheMiss and Showplan XML For Query Compile to get similar information. This is where my support of Profiler begins and ends!

Usual Caveats About Extended Events

These can cause really bad observer overhead, so you’re going to want to do this on a dev server with a simulated workload. If you run this in production with no filtering, it’s going to hurt. The second one especially, which captures query plans, even warns you in the XE session GUI that it will put your server in a bad way.

I’ve seen a lot of good recommendations around filtering sessions like these. You can filter to a database, and do some modulus math on session_id to only capture data from a subset of users, etc. But really, the safest bet is to hit a dev server.

Cache Misses

If This Is Empty, Congratulations

Stored procedures are not your problem. At least they weren’t during the window you observed. If you’re *only* using stored procedures, you may want to keep sampling at different times and intervals until you’ve captured a representative workload. If you already knew that, because you’re not using them, this second XE session may work better. You shouldn’t have even bothered with this one. What’s wrong with you?

Compiling Plans

This should give you, if nothing else, a comprehensive list of things that SQL compiled during your sampling window. Now you know what you can attack with different methods.

You can try Forced Parameterization, OPTION KEEP PLAN or KEEPFIXED PLAN, or sp_prepexec. You may also want to strongly consider moving to stored procedures to get, at least, clearer reasons for why your plans aren’t being reused. Or, you know, SQL might just start re-using those plans all of a sudden. Wouldn’t that just be your lucky day? It may also take moving away from some of the limitations discussed in the Forced Parameterization article and Plan Caching and Reuse whitepaperto get there.

Yes, I’m ignoring Plan Guides and Query Store’s plan forcing here. Why? Because I’ve never used them to solve this problem. I’m not sure forcing a plan with either method would work if SQL doesn’t recognize that it’s getting the same query. It’s something interesting to test out another day.

Thanks for reading!