June 3, 2009 at 6:50 am
The procedure cache can and does change. Procedures age out of cache as they are not accessed. It's a pretty normal part of the process.
"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
June 3, 2009 at 7:10 am
You say that procedures age out of cache as they are not accessed. But how often? because in the case I mentioned they aren't kept in the cache even 2 seconds.How are the plans reused if the plan will age out in 1 or 2 seconds?
June 3, 2009 at 7:40 am
That's a little bit fast. It's based on a cleanup process that starts with the complexity of the plan. The simpler the plan, the lower the complexity number, therefor the less time it'll live in cache. But you usually don't see flushes of the cache except when there are other memory pressures.
Instead of the query you're using, try querying against sys.dm_exec_cached_plans.
"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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply