Michael J. Swart

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)
INSERT @log
EXEC sp_readerrorlog 0;
 
SELECT TOP 1 @ProcCacheStart = LogDate
FROM @log
WHERE [Text] LIKE '%SQL Plans%'
    AND [Text] LIKE '%cachestore flush%'
ORDER BY LogDate DESC
 
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.