SQL Server 2005 Metadata I Couldn’t Live Without

The terrific people at SQL Server Magazine have offered me the wonderful opportunity to write the commentary for SQL Server Magazine UPDATE three times a month. I’m hoping to share with you information regarding upcoming SQL Server features and events and occasionally look back over my 20 plus years of working with SQL Server and share some stories. I plan to talk about what excites you about SQL Server and what you wish could be changed. I spend a lot of time in the classroom, which is a great way for me to find out what different organizations are doing with their SQL Servers, what their biggest pain points are, and what their plans for migration and upgrade are. And now SQL Server Magazine UPDATE will enable me to get input from an even broader cross-section of the SQL Server user community.

Some of you might be aware of my regular articles for SQL Server Magazine, my Inside SQL Server books, and my SQL Server Internals Training DVDs. My focus is on the core database engine; that’s what I know and love the best about SQL Server. So when I do write a technical commentary, it will usually be about features of SQL Server’s database engine. In my next few commentaries, I’ll probably talk about some of the most eagerly anticipated database engine features that will be available in SQL Server 2008.

As many of us are looking ahead to SQL Server 2008, I’d like to reflect a bit on our upgrade to SQL Server 2005. In some ways it seems like it happened only recently and in other ways it seems like it was ages ago. I think what makes it seem recent to me is that I was still teaching SQL Server 2000 classes up until the summer of 2005. What makes it seem like ages ago was the fact that I was involved with SQL Server 2005 from the very early beta days of the product and began writing some SQL Server 2005 white papers at the end of 2002. I know that making the move to a new SQL Server version needs to be carefully considered, but for those of you who are now running production applications on SQL Server 2005, I’m wondering what SQL Server 2005 features you could absolutely not live without. For me, the biggest must–have feature in SQL Server 2005 is the metadata, particularly the Dynamic Management Objects. Having filterable and joinable views and functions to see what SQL Server is doing is priceless to me. My favorite views are

Of course, that’s just the tip of the iceberg. There are many more Dynamic Management Objects that I use on a regular basis and dozens more that are there when I need them. I can barely remember how I did performance tuning without them.