Beginning SQL Server™ 2005 Administration

Chapter 4. SQL Server 2005 Storage Architecture

I had just spent the better part of the day describing the storage architecture to a group of about 30 new database administrators when one of them approached me while the class was on break and asked me pointedly, "Why do I need to know this stuff? I mean, who cares how SQL Server stores data as long as it does it?" They were valid questions. After all, I have no idea how the fuel injection system on my car works, but I drive it anyway. The key difference is that when my car needs service, I take it to a mechanic. If your database doesn't work, who are you going to take it to? Understanding the mechanics of the SQL Server storage will help you make informed decisions on where the data is stored, how the data is indexed, and how to troubleshoot an ailing database.

Retrieving information about data storage requires digging into the inner storage structures of SQL Server. For SQL Server 2005, Microsoft has made some fairly dramatic changes to how metadata and system information is stored and accessed. Understanding these changes, or if you are brand new to SQL Server with this release, just the overall storage architecture is critical to understanding how SQL Server accesses and retrieves data.

For years, SQL Server database administrators have grown accustomed to having unrestricted access to system objects. This ability gave the DBA incredible power for both good and for evil. For example, a database administrator could turn on ad hoc ...