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.

May 14, 2009

SQL Server 2008 R2

Filed under: Tongue In Cheek — Michael J. Swart @ 4:43 am
It was announced at Tech Ed that the next major SQL Server version (codenamed Kilimanjaro) will be called SQL Server 2008 R2. This surprised me. I would have bet that it was going to be called SQL Server 2010. 


Microsoft marketing has to walk a fine line. On one hand, they want to placate any people who are nervous about upgrading to the first version of a major release. On the other hand, they want the products version name to announce that this is the latest and greatest.
Another danger is that in the future, I can imagine ridiculous names for particular versions: Imagine a CTP for SQL Server 2008 R2 SP3.
As for me, when SQL Server 2008 R2 comes out, I’m not going upgrade. I’m holding out for the version after that: SQL Server 2008 Threepio.

May 11, 2009

The Manga Guide to Databases?

Filed under: Tongue In Cheek — Michael J. Swart @ 10:34 am

Jeff James, Editor-in-Chief at SQL Server Magazine, mentioned a particular guide in the “New Products” section of the May 2009 Issue.

This guide is called the the Manga Guide to Databases and there’s no hint of sarcasm or satire here. Follow the link for a better idea of what this book is about. Here’s a sample from their promotional material copy: 
Want to learn about databases without the tedium? With its unique combination of Japanese-style comics and serious educational content, The Manga Guide to Databases is just the book for you.
Whew! So here’s my question… Who is this book for? What demographic are they aiming at? If you think it’s you, then let me know in the comments. Tell me what about this book appeals to you. If you don’t think it’s you, then hazard a guess in the comments.

May 7, 2009

When do two timespans overlap?

Filed under: SQL Scripts,Technical Articles — Michael J. Swart @ 12:14 pm

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
   starttime 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.

A straightforward approach might lead you to consider a number of different scenarios to check. I’ve seen the following checks a couple times:
  • timespan @id1 contains the start time of timespan @id2
  • timespan @id1 contains the end time of timespan @id2
  • timespan @id2 contains the start time of timespan @id1
  • timespan @id2 contains the end time of timespan @id1
These are four checks which result in 8 comparisons. It turns out that the last check is extraneous and so only 6 comparisons are needed.
I find it helpful to first determine whether the timespans do not overlap by doing the following checks:
  • timespan @id1 starts after timespan @id2 ends
  • timespan @id2 starts after timespan @id1 ends

This only needs two checks but each check is one comparison for a total of 2 comparisons. This leads to queries that contain expressions like the one used here:

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

This query will return 1 if the two timespans @id1 and @id2 overlap in some way.

Powered by WordPress