See behind the SQL Server curtain with the Internals Viewer for SQL Server

Notes from SQL Server Premier Field Engineer - Internals viewer for 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... … 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 !”

CodePlex - Internals Viewer for SQL Server

“Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.

All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works

…

Features

• Integration with SSMS (SQL Server Management Studio) 2005 and 2008

• The application is installed as a SSMS add-in
• Internals information integrated into the Object Explorer
• Transaction Log viewer integrated into the Query Results

• Allocation Map

• Displays the physical layout of tables and indexes
• Displays PFS status
• Overlay pages in the Buffer Pool

• Page Viewer

• Displays Data pages including forwarding records and sparse columns
• Displays Index pages
• Displays allocation pages (IAM, GAM, SGAM, DCM, and BCM pages)
• Displays pages with SQL Server 2008 row and page compression

…”

Sometimes you need to see behind the curtain and into the depths. This utility, for which the C# source is available too, sounds like it is one of those tools that can come in handy during a SQL Server spelunking expedition.

This utility also provides a working example of how to integrate into SQL Server Management Studio, so it’s like a two for one deal ;)