See different kinds of recompile hints in action

Then compare their performance, both from the client application and in the SQL Server plan cache… if you can!

Being more gentle to the plan cache

You’ll learn more granular ways to clear out parts of your execution plan cache in the upcoming module on “How to ‘take out’ an execution plan”

Why a PowerShell script?

I used a PowerShell script in this demo because it allows me to call a stored procedure like an application does, with a specific command type that indicates it’s a stored procedure right off.

If you execute a stored procedure in SQL Server Management Studio (or if you don’t specify the command type as a procedure), SQL Server first considers your execution statement itself as a potential adhoc plan because it appears as just text. That makes the compilation-related counters look different. Since most applications that use procedures specify the command type, I used a PowerShell script to reproduce that behavior.

Gail Shaw has an article that goes deeper into this topic: hit and miss.

What about Query Store?

Good news: recompile hints don’t obscure past performance in Query Store (available in SQL Server 2016+) like they do in the plan cache. You get query plan and aggregate information in Query Store, even if you have recompile in the header of a procedure.

Transcript

We are going to watch some recompile hints.

The very first thing that we’re going to do in this demo is something that can be a little problematic to do sometimes, we’re clearing out the execution plan cache by running DBCC FREEPROCCACHE.

Now when you run this the way I have this in this demo with no limitations on it at all, it clears out the entire procedure cache on your instance. This is a really simple, easy way to make out demo rerunnable ‘cause we’re going to be looking at how many times did SQL Server see some stored procedures run.

But maybe be very careful about doing this if you’re using an instance that other people are using to test as well because this could skew their results to suddenly start compiling new execution plans if they’re in the middle of running something.

So, having a private test environment to learn your SQL Server on, very valuable, for lots of different reasons.

What we’re going to do is we’re going to compile three different stored procedures…

they’re the same code, but two of them have recompile hints in them.

The first stored procedure is called, MostPopularYearByNameRecompileHint. Let’s go ahead and create this guy. This has one query in it. There’s a set nocount on and then one query in it, and the query has an OPTION query hint at the end of the query.

The second stored procedure has the same name but it’s named RecompilerInHeader, and let’s go ahead and create that one. In this case, there’s no query hint at the end of the query. Instead with recompile is in the header of the stored procedure.

Now this seems really similar, right? There’s just one real query in the stored procedure. But as we’re going to see, these are different in some important ways.

Our third stored procedure has no recompile hint in it at all. It’s the exact same code as the other two procedures, the only difference is it lacks a recompile hint. So, we’ve created this, and now for this one, SQL Server is going to give it its own execution plan, but then reuse it as much as it can unless something happens to cause a compilation while it’s running. Now, we’re not going to do anything to cause a compilation while it’s running.

What we’re going to do is use a PowerShell script to run each procedure 500 times

We’re just using one thread to run them from, we aren’t doing anything with the results.

We’re just measuring from the PowerShell script how long it takes to run each of them 500 times. We’re going to watch perfmon while they run. We’ll be looking at batch request per sec, compilations per sec, and recompilations per sec.

So, here’s our perfmon window. I’ve changed the colors a little bit to be like I like them, but it’s plain old perfmon on Windows Server and we’ve got our counter set up. I’m going to go ahead and unfreeze the display to start collection.

A lot of times, you know in the beginning of collection, it may have some little bounces, and now I’m going to start up our PowerShell script.

So, let’s take a look, it’s first running the stored procedure that has the recompile hint as a query hint 500 times. And there we go, that just finished up and it now switched, it’s running the stored procedure who has the recompile hint in the header 500 times. There we go, let’s see how that guy does. And now, it should be about ready to switch over.

It’s now moved on to the stored procedure that has no recompile hint at all, and it’s running that 500 times. And as it finishes up here, what I’m going to do is go ahead and freeze the display so we can look at the data that was collected.

I have highlighting turned on using the little crayon. And when I have a line highlighted, it turns while so that you can easily see what we’re looking at, so right now we’re looking at the batch request per second counter.

This batch request per second counter represents throughput in this case

So, higher is better, we’re just running these each 500 times as fast as we can, so if we can get higher batch requests per sec, we’re getting better performance.

We have elevated batch request per sec on this third portion of the graph here. When we had no recompile hint at all, we were able to execute this more quickly.

So, if we can reuse execution plans, and the execution plan is good for the different values that we’re running it with, reusing the plan can absolutely get us better throughput, better performance.

Moving down one to SQL compilations per sec

For the SQL compilations per sec counter, it’s equal to batch request per sec for both the stored procedure that had the recompile hint as the query hint, as well as the stored procedure that had the recompile hint in the header.

So, it saw, “Okay, I’ve got to compile really “just this one query every time the procedure was run,” and it’s the exact same as batch request per sec.

One thing that I think is interesting is the recompilations per second counter. This one’s a little weird. We only saw recompilations for the stored procedure where we had the recompile hint as the query hint. In the middle here, we have the stored procedure that had the recompile hint in the header. It saw that only as a compilation and not as a recompilation. That doesn’t mean it performed any better. It’s simply the counter showing things in a different way.

What I really mean by showing you this is don’t only look at the recompilations per sec counter

The recompilation per sec counter can be low and you might still have recompile hints just in the stored procedure of headers, or in the header of stored procedures, which doesn’t show up in this graph.

So, if you are looking at a SQL Server and you want to know, “Are we having recompiles happen?” Yes, you can look at the counters. If you are looking at the counters though, you want to look at both compilations and recompilations and really, what you might want to do is look at the code base that’s running against the SQL Server and look for recompile hints in there too.

These counters can be a little confusing, don’t only look at the recompilations per sec counter.

The main thing to take away from this is: we did get better throughput in this case with no recompilation hint!

Looking at our PowerShell script, it confirms this.

The first result here where it says recompile hint, to run the procedure with the recompile query hint 500 times took 14.9 seconds. When we moved that to the header, it did get a little better throughput. It was able to run them in 14.1 seconds. But wow, with no recompile hint, even on such a simple query, we were able to get the 500 runs in just 12.9 seconds.

So, a full second or second and a half, close to two seconds faster just by reusing the execution plans.

Now, reusing execution plans doesn’t always go well but this is one of the reasons why using a recompile hint as treatment isn’t always my first choice when I have multiple choices.

But here’s where stuff gets real too.

What can we see about performance in the dynamic management views in SQL Server?

We’re going to first look at the sys.dm_exec_query_stats dynamic management view. Now, this is a really useful view because it says, “Let’s look at the queries that have run, “that have execution plans in cache,” and we can see things like how many times have they executed with that plan, how many logical reads did they use?

How much CPU did they use, which is called worker time. How long did they take, lots of stuff.

So, we’re going to say we want to see what’s in cache for everything whose text is like MostPopularYearByName. And I am putting an option recompile on this query because I don’t want this query hanging out in my cache and filling up my cache. For data that I want to hide, or for queries that I want to hide, I do sometimes use recompile hints on those.

Something interesting here.

In sys.dm_exec_query_stats, we only are seeing two rows for queries whose text contains MostPopularYearByName… and we had three stored procedures

This first row here, it saw execute 500 times, and we do get information about that. We could calculate the average CPU time by doing total worker time divided by execution count. But let’s click on the query plan and see who this is. Now, we are having this sort of weird display thing where it gave the set option a lot of space, so I’ve just dragged them back together. But we can see from the header up here that this is the procedure MostPopularYearByName. Its plan is in cache, so we when we didn’t have the recompile hint, it saw that 500 times.

The second row, it saw execute only once. We only have information about the last of 500 runs for, if I click on the query plan, the procedure who’s MostPopularYearByName recompile hint, and if I drag this up and we hover over this guy, we can see that OPTION , this is the stored procedure where it has a query hint with OPTION , it is not in the header.

When we put OPTION in the header of the stored procedure, we do not have any information in sys.dm_exec_query_stats at all for that.

By putting recompile in the header, we have made it invisible

By putting recompile on the query hint, we now only see the information for it, we ran it 500 times, we only see that it’s run once by looking at it here.

So, by using recompile hints, we make it much more difficult to see the impact of the query on our instance because we only see either the last one if we use it as a query hint, or we don’t see anything at all if we put it in the header.

There’s another dynamic management view, called sys.dm_exec_procedure_stats

Now, as you can imagine, this is not for ad hoc queries. This is where our stored procedures are going to show up for procedure-level execution information.

I’m looking for object whose names are like MostPopularYearByName, and let’s see what we see in this alternate dynamic management view.

Now, the news is a little better, but we still only have two rows.

We have information first for the procedure that doesn’t have a recompile hint, and then we have information for the procedure that has the recompile hint as a query hint.

We still have no information for the procedure that had the recompile hint in the header. That’s a big bummer and that is a really good reason to not put recompile hints in the header of a procedure.

We do see, for where there was no recompile hint, it’s all 500 runs, and here’s the good news. In sys.dm_exec_procedure_stats, for the stats at the procedure level itself, for the one that had the recompile hint in the query, we still get the execution statistics for the stored procedure for all 500 runs. Even though that statement was recompiling within the procedure, because it wasn’t in the header, it’s still in sys.dm_exec_procedure_stats was able to see, “Oh, well, here’s your total CPU time for all 500 runs in your total logical reads.”

The query plan that’s in cache is only going to be the latest query plan for that query itself

We don’t know what the query execution plan for that statement in the stored procedure was prior to this last execution, but at least we get execution statistics for it.

So, for these reasons, for performance reasons, when I’m considering ways to get consistent high execution of a given set of code, I will consider recompile hints, but they’re be among my last choices, and that’s because I want the most information I can get in the plan cache and, if I don’t have to compile every time, I will get better performance if I can get an execution plan that’s good for all the values that it runs for. If I am going to use recompile hints, I am not going to put them in the header in any stored procedures, I’ll put them only on the statements that need them using a query hint and I’ll document why I did it.