#STORED PROCEDURE RECOMPILE FREE#

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

#STORED PROCEDURE 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.

Stored procedure recompile