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.