June 3, 2009 at 3:22 am
Hi
I'm running the next query to see the sp that are cached :
select object_name(objid) name ,*
from sys.syscacheobjects
where object_name(objid) is not null
and objtype = 'proc'
order by object_name(objid)
Sometimes the query returns about 400 records(and I think this is correct), but most of the time the query returns less than 10 records(included in that 400). What could be the problem. Is my query incomplete or wrong, or is there a possibility that the cache is freed by a process?
10q
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply