SQL PASS Conference – Welcome to Seattle

I made the long trek across the country this week to attend my favorite conference, the SQL PASS Community Summit, this year in Seattle, and so far it's been well worth it. I usually arrive early to attend a pre-conference session or two to get the most out of the week. This year I attended Kalen Delaney's session on SQL Server Data Storage Formats: Internals, Performance and Best Practices and I was not disappointed.

Kalen writes a regular article in SQL Server Magazine on similar subjects and always makes the driest topics on SQL Server internals seem actually compelling. I remember buying her book entitled "Inside SQL Server 2000", a real door-stopper of a book, and being surprised what a good read it was. I won't go as far as to say "I couldn't put it down" but it was close. She also wrote the SQL sequel (sorry couldn't resist that one) although "Inside SQL Server 2005" was actually split into several books of which she wrote two: The Storage Engine and Query Tuning and Optimization. A new book "SQL Server 2008 Internals" should be out next Spring. As a presenter, she is very engaging without being overly formal and mixes up the classic Powerpoint slides with some meaningful demos.  

Some of the topics covered included Data Storage in SQL Server 2005 and now 2008 with the differences and new features we can take advantage of. Kalen can also give a unique historical view to the product since she has been using SQL Server since her Sybase days over 21 years ago. (I too, can claim to be from the Sybase lineage but that was after her time, a mere 13 years ago). For instance, I had almost forgotten that SQL Server used to have 2K data pages before SQL 7.0 introduced the current 8K data page size. I also had chosen to forget that SQL Server ran on OS/2 all those years ago.  But the overarching theme of the seminar was the new storage internals of SQL Server 2008.

In order to understand the internals of each of these features, Kalen showed us how to use some "undocumented" features. Actually, even though some of these features are not in the official help system "Books Online", Microsoft regards a feature documented as long as it is somewhere in the MSDN Knowledgebase. These useful features (whether documented or not) include DBCC PAGE, DBCC IND, DBCC EXTENTINFO and sys.system_internals_allocation_units. They help us look inside database files and understand the arrangement and contents of the actual data pages. This is critical when attempting to understand the implications of the new storage options.

The three major topics covered for the new release were Sparse columns, Filestream data and Data compression. I have written about each of these features in other blog entries, but all I can say is that I learnt a great deal during this session. Kalen did a great job of showing us how to use the tools available to assess the effects of the new storage options. I like her style. To use a fishing analogy, Kalen gave us the fishing pole and showed us how to use it, instead of just giving us the fish.

More seafood from Seattle later on,


Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2008 IDG Communications, Inc.