THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Visualizing Data File Layout I

Part 1 of a blog series visually demonstrating the layout of objects on data pages in SQL Server

Some years ago a gentleman called Danny Gould created a free tool called Internals Viewer for SQL Server. I’m a visual sort of guy, and I always thought it would be fun and educational to make a simple visualizer, like the one he created, in order to view how objects are laid out in SQL Server files, and to use it to demonstrate how operations like re-index and shrink affect the layout of files.

To that end, and a little bit reinventing the wheel truth be told, I spent this past holiday creating a simple .NET app that renders the file layout of a database into a color-coded bitmap:


Fig 1

The app can scan the pages in a database, grab the header output from DBCC PAGE, parse that, and create a structure with a few key bits of information about every page. It then renders a bitmap from those structures showing a few things (Fig 1):

Each data object (index or table) partition is identified with a unique partition ID in SQL Server. Those IDs are used in this tool to color-code the output by object, from a color lookup table. Each color in the example screenshot represents the pages that are dedicated to a single partition of an object. This screenshot shows AdventureWorks, which doesn’t use the Enterprise Edition partitioning feature, so for this case each color represents one object – every object having exactly one partition in Standard Edition (or in databases that don’t use partitioning).

Unallocated pages are shown as gray gaps. These are regions that are part of the physical file(s), but not used to store anything.

The app flags pages at the end of any fragment of an object using a darker colored band, so it will reveal any non-contiguous structures in the data file(s). Sometimes these happen at the end of a region of the file where one object is stored, but, interestingly, sometimes these can happen in the middle – as shown in the image above where a dark band interrupts a continuous region of the same color.

The app has some very basic mouse-over capability where you can run the mouse over the image and the text fields at right will reveal information about the pages, including the object schema.table.index and partition, and also whether the page represents a fragmentation boundary.

Finally, the app shows what page types are located where in the file using the narrower white/gray/black bands. White represents data or index pages, while other shades of gray or black indicate other kinds of system pages, per Paul Randal’s excellent blog post here.

The Pixels Already Tell a Story

So, what can we learn about the sample database in this image? Here are a few things:

  1. The part of the file shown in the bitmap is fairly dense. There aren’t big regions of unallocated space in the file. A gap in the allocated pages looks like this (enlarged):
    Empty Region
  2. Objects in the file are not contiguous, and may “hop around.” That is, if you follow the linked list of pages that compose an index, a bunch of them will be in a row, and then there will be a page that links to the next page composing the index but it’ll be in a different location in the file. I’ve called these “frag boundaries” – pages that do link to another page, but where that next logical page isn’t the next physical page in the file. In the graphic the frag boundary pages are colored with a darker dithered pattern. You can mouse over these and look in the text fields at the right in the app, and see the page they link to.
    Fragment Boundaries

    Sometimes the end of a fragment will be adjacent to pages from another object, but it can be the case that there’s a fragment boundary in the middle of the pages for one object – it’s just that the linked list goes up to that point in the file, but then the next page in the index (in index order) isn’t the next page in the file, even though the next page in the file is part of the same object. Imagine a page split in the “middle” of an index – the existing page with half the rows stays in place, and a new page with the other half of the rows gets created in the middle of the logical index order but possibly stored in some other location in the physical file.
  3. Right at the very beginning of the file there’s a special sequence of metadata pages that describe the database, allocations, and so on (again, well documented by Paul Randal). In our diagram this shows up as a series of pages at top left with varying page type (the gray and white) indicators:
    Database and file metadata pages

In the next installment, I’ll run some test databases through this and we can see what more severe fragmentation looks like, the effect of GUID cluster keys, shrink, and how the data moves around in a re-index operation.

Here’s a short demo video of the mouse-over working (quality is You-Tube limited):

Animated Screen Cap of Mouse-over
Published Tuesday, January 22, 2013 3:50 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Michal Poziemski said:

This looks very interesting. Will you publish this app?

January 23, 2013 2:48 AM

Scott Whigham said:

Very cool - thanks for sharing. I'd love to try it out - is there a way to download it?

January 23, 2013 10:27 AM

merrillaldrich said:

I got a lot of positive response and inquiries like that, so I am cleaning the code up, adding some basic features, and I should be able to release a public version. I'm not sure exactly when it'll be ready - but check back here.

January 23, 2013 1:40 PM

Merrill Aldrich said:

Part 2 of a blog series visually demonstrating the layout of objects on data pages in SQL Server Part

January 23, 2013 6:40 PM

Davide Mauri said:

Hi Merrill, it looks really interesting!!!!! Looking forward to start using it :)

January 24, 2013 11:51 AM

Russell Tye said:

This is extremely cool. Please let me know when I can try it out. Thanks for sharing. I very much appreciate it.

January 26, 2013 3:46 PM

Merrill Aldrich said:

This is part three of a blog series illustrating a method to render the file structure of a SQL Server

January 29, 2013 12:45 AM

Leave a Comment


This Blog


Privacy Statement