![]() 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |