SQL Internals Viewer
SQL Internals Viewer is a free tool for looking at how the SQL Server Storage Engine
structures, allocates, and organises data internally. It can be downloaded from
I started looking into internals to get a better understanding of how SQL Server
worked. SQL Internals Viewer started as a tool to assist me with some of the tasks
necessary for looking at internals, including things like decoding page addresses
and querying system tables. Over time, the application evolved to include more features
to help visualise and navigate around SQL Server internals. If you would like more information on internals see
www.sqlinternalsviewer.com/resources.html
for a list of further resources.
Below, I will go over the key features of the application including:
- Allocation Map
- Database Explorer
- Page Viewer
- SQL Query Editor with Transaction Log Viewer
Allocation Map
SQL Server stores data in 8KB pages. Eight contiguous pages make up an extent. Certain types of pages are used for tracking allocations within a database. These pages include the GAM, SGAM, DCM, and BCM pages for global allocations and IAM pages for allocation units.
The Allocation Map displays database allocations. It is located at the right hand side of the main window. Initially the Allocation Map displays the GAM and SGAM pages, coloured blue and green respectively.
Hovering over a page will show where it is allocated. The different allocation pages can be displayed using the 'View' menu. The 'View all allocation units' option can be selected to show the location of all the different allocation units on the Allocation Map.
Database Browser
The Database Browser allows you to navigate through the databases, indexes and tables on the server. It is located on the left hand side of the main window. Selecting an index or table in the Database Browser will display its IAM on the Allocation Map.
The Database Browser shows the location of the first IAM and the entry points to the index or table, the Root Page and the First Page. Clicking on them will show where they are located on the Allocation Map.
Page Viewer
Clicking on a page on the Allocation Map or entering a page address in the 'Go to page' box will open a page in the Page Viewer. The Page Viewer displays the different parts of a page, including the header and the offset table. The header stores information about the page, including things like the page type and how many records it contains. The offset table stores where each record is located in the page.
As well as the header and the offset table the Page Viewer displays a hex-dump of the data and an interpretation of the record selected in the offset table. The record interpretation colourises the data in the hex-dump showing where the various elements are located. Different records on the page can be selected by selecting different slots in the offset table.
Selecting data in the hex-dump will decode it to any appropriate data type.
Links to other pages can be clicked on in both the header and row interpretation to navigate to that page. The Page Viewer also keeps a history of pages that have been visited.
SQL Query Editor with Transaction Log Viewer
The application has a query editor so queries can be executed. Once the query has finished, the effects on the allocation map can be examined.
Along with the query results and any messages, the query editor can also display records from the transaction log. This information can be used to see what pages have been changed and then the page can be viewed in the Page Viewer.
Conclusion
I hope you have fun with the application. Get in touch at
info@sqlinternalsviewer.com if you have any issues, comments or questions.