January 24, 2019 at 2:46 am
Working on a legacy procedure with multiple select statements . From dm_exec_query_stats was able to identify when each of these query got the part of plan recompiled ( should be due to stats update on particular table int he query). But what/when will ideally the recompilation of the SP as whole happen ?
January 24, 2019 at 6:05 am
Back in, I think, 2005, the recompilation behavior changed from procedures to statements. It's a GREAT thing that this happened. Now, instead of any little thing leading to 2, 10, or 200 statements getting recompiled, each individual statement recompiles as needed. So, the only time you'll see an entire procedure recompile is when you alter the code or you add a hint to the procedure it self to recompile. Otherwise, unless it ages out of cache, only individual statements will ever recompile. This is, in the overwhelming majority of cases, a very good thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply