#WITH RECOMPILE VS OPTION RECOMPILE HOW TO#
#WITH RECOMPILE VS OPTION RECOMPILE FREE#

Execution stats recorded in sys.dm_exec_procedure_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.

Limited execution stats recorded in sys.dm_exec_query_stats.

Limited execution plans in cache to review (last execution).

The option most folks discover first is to use ‘WITH RECOMPILE’ in the header of the stored procedure, like this:Įven 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:

#WITH RECOMPILE VS OPTION RECOMPILE HOW TO#

When you’ve got procedures that you want to generate fresh plans, you start looking at how to use hints within the procedure. RECOMPILE hints in stored procedure headers While this can occasionally be useful for troubleshooting and testing, don’t make it part of production code. On busy systems I’ve seen this command take part in some nasty blocking chains. It also requires a high level of lock to complete. This command requires high permission - the user running it requires ‘alter’ permissions on the table. (Don’t bother trying to memorize this, just read the next paragraph.) But if you mark the stored procedure itself for recompilation, it will be reset at the next use. At that point, existing execution statistics in sys.dm_exec_query_stats will be reset for statements in the plan.Įxecution 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. Instead, the magic happens the next time queries referencing the recompiled object run. When this is run, related plans are not immediately removed from SQL Server’s execution plan cache. To do this, you just use syntax like this: Your hint also just applies to what you’re doing at runtime. This is great because you don’t have to change any compiled code. 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. Executing a procedure with a runtime recompile hint 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. Check out a sample query in Books Online.įor 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. It tracks execution metrics for stored procedures. sys.dm_exec_procedure_stats- This DMV is only available in SQL Server 2008 and higher.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.When I talk about impact on the execution plan cache, I’ll refer to two DMVs: Handle with care! Useful Dynamic Management Views

#WITH RECOMPILE VS OPTION RECOMPILE FREE#

If you’ve got big questions in those areas, feel free to suggest it for a future post in the comments.ĭisclaimer: Recompile hints can kill your performance by lighting your CPUs on fire when used incorrectly.

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. I’ll give some pros and cons for each method and explain what’s useful and what’s worth avoiding. This post runs through common options you have to nudge (or whack) SQL Server into generating a fresh execution plan. (Not sure what parameter sniffing is? Learn from this blog post or this 50 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? When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality.

With recompile vs option recompile