Hope you had great holidays! Since Christmas and New Year are past us, let me resume blogging on SQL Server.
I am guessing I won’t be making big news if I tell you that SQL Server database is just a collection of 8K pages. Each page serves different purpose. There are data pages, which hold actual rows in tables, there are index pages which make up index, SGAM/GAM pages which track extents, PFS pages which track free space on pages in the database, IAM pages, overfow pages and many others… You probably also heard about DBCC PAGE command which allows you to look at the content of any page and you probably used it one way or the other to peek how SQL Server stores data internally. However, it is likely that you did not enjoy typing all these sys.indexes, sys.objects and sys.system_internals_allocation_units queries in SQL Server Management Studio and chasing pageIDs for root pages, first IAM pages and first data pages. If all this sounds familiar, let me tell you that there is actually a better way :). Danny Gould developed an awesome tool Internals Viewer for SQL Server which integrates with SQL Server Management Studio and allows you easily drill into individual pages and better understand the relationships between them and overall architecture of SQL Server storage engine.
Give it a serious try and have fun playing and learning internals of Microsoft DBMS !