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.

This post is about comparing two timespans and determining whether they overlap at all. Suppose you have a table of timespans similar to the following abstract example:

CREATE TABLE timespans
(
   id INT IDENTITY PRIMARY KEY,
   starttime DATETIME NOT NULL,
   endtime DATETIME NOT NULL
)

A common task is to determine what expression determines whether two given timespans overlap. In this example, say @id1 and @id2 are the two timespans.

SELECT 1
FROM timespans AS timespan1,
   timespans AS timespan2
WHERE timespan1.id = @id1
   AND timespan2.id = @id2
   AND NOT timespan1.endtime < timespan2.starttime
   AND NOT timespan2.endtime < timespan1.starttime