SQL Server Internals Viewer

When Danny Gould, a SQL Server and .NET professional working in London’s financial district, first started learning about SQL Server internals, he found that it was difficult and time consuming to see what SQL Server was doing internally on an 8K data page. The SQL Server Internals Viewer is the result of Danny’s effort to take all of the cumbersome elements out of finding out how SQL Server stores data.

The SQL Server Internals Viewer offers four main areas of functionality. Each of the following areas includes a GUI to help you visualize and navigate the internals of SQL Server:

• Allocation Map—The Allocation Map lets you quickly and easily see how all 8K data pages, extents, and special purpose pages, such as the Global Allocation Map (GAM), Secondary Global Allocation Map (SGAM), Differential Change Map (DCM), and Bulk Change Map (BCM), are allocated to disk. The Allocation Map is the default view for the SQL Server Internals Viewer and automatically shows GAM and SGAM pages in blue and green, respectively, in the right pane of the screen. You can use the View menu to choose which allocation units you’d like to view or use the Database Explorer in the left pane to view the pages and extents of a given database or database object such as a table.
• Database Explorer—The Database Explorer, which is located in the left pane of the SQL Server Internals Viewer, lets you select a specific database, table, or index to view. You can also drill down to the Index Allocation Map (IAM), the root page, or the first page of an index or table, and then see their representation on the Allocation Map.
• Page Viewer—To view the entire contents of an 8K data page, including the headers and offset table (which are usually visible only via the Database Consistency Checker—DBCC—PAGE command), use the Page Viewer, which is shown in Figure 1. To access the Page Viewer, click a specific page in the Allocation Map or enter a page number in the Go to Page box at the top of the Allocation Map. The Page Viewer shows the page type, the number of records on a page, and where each record is physically located on the page. It represents data on each page as a hexadecimal, but you can see a plain-text translation of any human-readable data (for example, data stored in data types such as NVARCHAR, VARCHAR, and INT) by clicking the data.
• SQL Editor—You can use the SQL Editor to execute a query and immediately see the effect of the query in the Allocation Map. The SQL Editor displays the affected records from the transaction log, as well as query results and messages. Pages that are affected are shown in the SQL Editor and can then be displayed in the Page Viewer or Allocation Map.

I encourage you to check out Danny’s blog, which includes good information about SQL Server storage behavior, tips, and tricks, at sqlblogcasts.com/blogs/danny/default.aspx. And as always, we want to hear your feedback on the Tool Time discussion forum at sqlforums.windowsitpro.com/web/forum/categories.aspx?catid=169&entercat=y.