When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 30 minute free video.) You must decide: what hint or command do you use, and where do you put it? What trade-offs are you making when it comes to being able to performance tune your SQL Server in the future?
This post runs through common options you have to nudge (or whack) SQL Server into generating a fresh execution plan. I’ll give some pros and cons for each method and explain what’s useful and what’s worth avoiding.
To keep things relatively simple, I’m just discussing how this applies to stored procedures today–this post doesn’t cover other forms of parameterized (or non parameterized) queries. If you’ve got big questions in those areas, feel free to suggest it for a future post in the comments.
Disclaimer: Recompile hints can kill your performance by lighting your CPUs on fire when used incorrectly. Handle with care!
Useful Dynamic Management Views
When I talk about impact on the execution plan cache, I’ll refer to two DMVs:
- sys.dm_exec_query_stats – This DMV is helpful to see the top statements on your SQL Server, regardless of whether they’re part of a procedure or not. Check out a sample query here.
- sys.dm_exec_procedure_stats- This DMV is only available in SQL Server 2008 and higher. It tracks execution metrics for stored procedures. Check out a sample query in Books Online.
For both of these DMVs, having an execution plan in the cache is linked to being able to see execution metrics: number of total executions, total and average CPU, logical reads, etc. When an execution plan is removed from the cache due to recompilation, memory pressure, restart, or other actions, the related execution metrics are removed as well.
Executing a procedure with a runtime recompile hint
One option that I love for quick and easy testing is the ability to call a stored procedure with a recompile hint at execution time. This is great because you don’t have to change any compiled code. Your hint also just applies to what you’re doing at runtime.
To do this, you just use syntax like this:
EXEC dbo.Proc3 @parameter1=80 WITH RECOMPILE; GO
However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. You can still get parameter sniffing on any calls to sub-procedures within the stored procedure itself.
So while I do like this sometimes for testing, if you have any nesting in your stored procedures it may not help you get a truly fresh execution plan.
Using the sp_recompile System Stored Procedure
This sometimes comes in handy if you need to intervene during a production incident if you’ve got a bad plan being used repeatedly by incoming sessions. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):
EXEC sp_recompile 'Person.Address'; GO
When this is run, related plans are not immediately removed from SQL Server’s execution plan cache. Instead, the magic happens the next time queries referencing the recompiled object run. At that point, existing execution statistics in sys.dm_exec_query_stats will be reset for statements in the plan.
Execution counts in sys.dm_exec_procedure_stats will not necessarily be reset at next use if you mark a table used by a stored procedure for recompilation. But if you mark the stored procedure itself for recompilation, it will be reset at the next use. (Don’t bother trying to memorize this, just read the next paragraph.)
There’s big downsides with this one. This command requires high permission — the user running it requires ‘alter’ permissions on the table. It also requires a high level of lock to complete. On busy systems I’ve seen this command take part in some nasty blocking chains. Be careful with it!
While this can occasionally be useful for troubleshooting and testing, don’t make it part of production code.
RECOMPILE hints in stored procedure headers
When you’ve got procedures that you want to generate fresh plans, you start looking at how to use hints within the procedure. The option most folks discover first is to use ‘WITH RECOMPILE’ in the header of the stored procedure, like this:
ALTER PROCEDURE dbo.Proc3 @parameter1 int WITH RECOMPILE AS SELECT DISTINCT City as CityFromProc3 FROM Person.Address WHERE StateProvinceID=@parameter1; EXEC dbo.Proc2 @parameter1; GO
Using RECOMPILE in the stored procedure header is pretty drastic — the procedure won’t cache an execution plan when it runs. This means:
- No execution plans in cache to review
- No execution stats recorded in sys.dm_exec_query_stats
- No execution stats recorded in sys.dm_exec_procedure_stats
Wow, that’s a bummer. If you need to identify the impact this procedure is having on your server, you have to run some sort of trace or extended events session and harvest and interpret the results. That’s not quick, and running traces can impact performance.
Kind of a big negative. For that reason, I don’t like this choice much at all.
RECOMPILE hints on individual statements
This option is a bit more work, but it has a much better payoff over time. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:
ALTER PROCEDURE dbo.Proc3 @parameter1 int AS SELECT DISTINCT City as CityFromProc3 FROM Person.Address WHERE StateProvinceID=@parameter1 OPTION (RECOMPILE); EXEC dbo.Proc2 @parameter1; GO
Even if you need to use the hint on all the statements in the procedure rather than in the header, this still has benefits! By putting the recompile hint at the statement level in the proc, you magically get:
- Limited execution plans in cache to review (last execution)
- Limited execution stats recorded in sys.dm_exec_query_stats. You’ll only get stats for the last execution, but the plan_generation_num column will increment, at least giving you insight that something is up.
- Execution stats recorded in sys.dm_exec_procedure_stats
The fact that you do get some information in these DMVs can be super useful over time. Applying recompile hints only to the statements that need them is also just more responsible– it lowers your risk of burning yourself up over time with CPU burn.
DBCC FREEPROCCACHE – the nuclear option
This isn’t strictly a recompile hint– but it certainly does cause recompilation. SQL Server has a command you can use to tell it, “Start anew with fresh execution plans”. It’s usually used like this:
--This is the nuclear option DBCC FREEPROCCACHE GO
This command makes everything start fresh– for every query:
- CPU usage may go up as new plans are compiled
- Execution stats are cleared in sys.dm_exec_query_stats (immediately)
- Execution stats are cleared in sys.dm_exec_procedure_stats (immediately)
This command isn’t evil– there’s a time and a place in troubleshooting when using it can help you get to the root cause of a performance problem quickly. However, since it impacts the whole cache and can also impact performance, you must be very careful using it. I do NOT recommend using this command in any regular jobs, automated processes, or production code.
As of SQL Server 2008, you can use this command a bit more gently– you can remove a specific plan from the cache using the “plan handle” or “sql handle”– but of course you’ve got to figure out what that handle is and why you want to remove it. This could come in useful in some niche situations, but in practice it doesn’t come up much. You can also clear a resource governor pool, but, well you’d have to be using resource governor.
What’s it all mean?
When you’re testing, there’s a place in your toolkit for both ‘EXEC procedure WITH RECOMPILE’ and the sp_recompile procedure. Use them with care and beware of possible repercussions, particularly with sp_recompile.
When you’re implementing hints in code, don’t have RECOMPILE blinders on– you can use ‘OPTIMIZE FOR’ hints sometimes successfully as well (although sometimes you’re optimizing for mediocre). And at times, dynamic sql can also help you out.
But if you do use RECOMPILE hints, please keep your recompiles at the statement level– and not in the headers of your procedures.