May 22, 2009

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!)

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

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.

This guide is called the the Manga Guide to Databasesand 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.

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

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