Michael J. Swart

May 22, 2009

How old is the procedure cache?

Filed under: SQL Scripts,Technical Articles — Michael J. Swart @ 4:55 am

I like to use DMVs a lot. There’s a lot of good information in there. Especially powerful is combining sys.dm_exec_cached_plans with sys.dm_exec_query_plan with a cross apply.

I like looking at the procedure cache because it’s a really good sample of typical activity on a server. It’s useful, but you have to take that information with a grain of salt. The procedure cache might be missing interesting queries that have not been sent since the last server restart. It makes intuitive sense: The longer the procedure cache has to monitor and cache queries, the more comprehensive the procedure cache, and the closer to a good representation of typical server activity.

So it makes sense to ask “How old is the procedure cache?”.

I’ve written a script that quickly tells me when the last server restart was, or when the last DBCC FREEPROCCACHE was issued: (Update June 2, 2009: The comments for this post is a must-see for a much simpler query that returns the same information as the query I post here!)

DECLARE @log TABLE (LogDate datetime, ProcessInfo NVARCHAR(100), [Text] NVARCHAR(MAX));
DECLARE @ProcCacheStart datetime
-- get last server restart
SELECT @ProcCacheStart = login_time
FROM sys.sysprocesses
WHERE spid = 1
-- get last freeproccache (if necessary)
EXEC sp_readerrorlog 0;
SELECT TOP 1 @ProcCacheStart = LogDate
FROM @log
WHERE [Text] LIKE '%SQL Plans%'
    AND [Text] LIKE '%cachestore flush%'
SELECT @ProcCacheStart

This gives a pretty good idea of how old your procedure cache is. There are a couple caveats though. For one, sp_readerrorlog is an undocumented feature. Also, the info could be inaccurate if the error log is cycled with sp_cycle_errorlog.


  1. Very cool, Michael! Thanks for sharing. 🙂

    Comment by Michelle Ufford — May 26, 2009 @ 8:12 am

  2. Would this query do the same :-
    SELECT MIN(creation_time) AS ‘age of proc cache’
    FROM sys.dm_exec_query_stats

    Comment by Anonymous — June 2, 2009 @ 4:48 am

  3. Well of course it would! Why didn’t I think of that? Thanks for sharing. (I love the internet)

    Comment by Michael J. Swart — June 2, 2009 @ 5:46 am

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress