Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The SQL Internals Viewer

By Danny Gould,

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 www.sqlinternalsviewer.com.

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:
  1. Allocation Map
  2. Database Explorer
  3. Page Viewer
  4. 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.

Allocation Map

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.

Allocation Map showing allocation units

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.

Allocation Map displaying IAM

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.

Page Viewer

Selecting data in the hex-dump will decode it to any appropriate data type.

Data selected on the hex-dump

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.

SQL Query Editor

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.

Total article views: 11930 | Views in the last 30 days: 12
 
Related Articles
FORUM

SQL Internals Viewer - new version

SQL Internals Viewer is a tool for examining SQL Server internals

FORUM

Multidimensional database internal representation.

Multidimensional database internal representation.

BLOG

A Graphical View of the Physical Allocation of a Database

First saw information about a tool called SQL Internals Viewer in a blog post here at Kalen Delaney'...

FORUM

Database Allocation

my database allocation is only 15% free,

FORUM

Internal implementation of Query execution

What is the internal implementation of Query execution?

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones