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 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.
0 kommentar(er)
