THE SQL Server Blog Spot on the Web

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

Merrill Aldrich

Public Release, SQL Server File Layout Viewer

Version 1.0 is Now Available!

Edit 9 October 2013: A new version is out! Please download 1.2 from here. 

I’ve been working off and on, as my real job permits, on this visualization tool for SQL Server data files. This is an educational or exploratory tool where you can more readily see how the individual data pages in MDF/NDF files are organized, where your tables and indexes live, what effect operations like index rebuild or index reorganize have on the physical layout of the data pages.

FileLayoutViewerR1

The viewer will scan a whole database, using only SQL and DBCC commands, and will render a color-coded representation of all the data pages represented in colored bands. Each partition of each index or heap in the database is assigned a color, so that you can see where all the bits and pieces of an object are located in the files. Above the colored bands there are grayscale or white pixels that show the page type in SQL Server (most are white, which are data pages. Unused/empty regions of the file show as gray). In the image above, for example, all the bright green areas are one index, all the purple areas are one index, and so on.

There is mouse-over functionality. If you move the mouse cursor over the graph, then details about each page populate the text fields at right, including the object and index the page belongs to, the page type, whether the page represents a fragment, where the previous and next pages are for the same object, etc.

Why?

Why create something like this? I am a visual person, and I have a theory that many issues we have in computing come down to not being able to see what’s going on. This is especially true as we learn about unfamiliar technology – we have to develop a mental model of structures like B-trees or linked lists or files in order to understand what’s happening. I hope this tool, combined with other knowledge, will help people form an accurate understanding of how data file internals work in SQL Server, faster than working purely in the abstract with tools like DBCC Page or DBCC Ind.

Instructions

  1. Download the tool and unzip it. The package includes both an executable and the source code. If you don’t want the source, the .exe file is a standalone program and will run all on its own, so you are welcome to discard the source folder.
  2. Validate you have the required prerequisites from the Prereq’s section below.
  3. Locate a non-production/test database to analyze. The database can be local or on a remote server. I suggest something of a reasonable size, because scanning a really huge data set can take quite a long time.
  4. Run SQLFileLayoutViewer.exe and select a database to scan. If the database is on a remote server, type the SQL Server name/instance name into the dialog.
  5. Click Analyze.
  6. Examine the resulting graph, and mouse over it with the cursor to view detailed information about each page.

Disclaimer

This is a freeware tool provided for your fun, education and entertainment. However, there is no warranty of any kind and you use it at your sole risk. The tool is free but offered under the GNU General Public License 3. If successful, and people are interested, I’ll move this work to some sort of open source project.

Prerequisites

The app requires .NET Framework 4.0 and the SQL Server management tools. I’ve tested it on Windows 7, Windows Server 2008 R2 and Windows 8. It can be run against a database on a local or remote SQL instance. I believe it will work on any database in SQL Server 2005 or later, but have not tested every possible scenario.

Risks?

I believe this tool to be relatively risk free, but I would avoid running it against live production data. The tool’s data collection is simple: it will issue a few system table selects to get things like object names, and then it will execute a DBCC PAGE statement against every page in the database. All other processing after that is done locally in the application itself. It does not modify the database.

Bugs?

I would love to hear about bugs you come across, or additional features you think would be valuable. Please contact me through this site. Note that I am a DBA first, and an amateur .NET developer a distant second, so please be gentle.

Enjoy!

Published Friday, March 01, 2013 2:36 PM by merrillaldrich

Attachment(s): SQLFileLayoutViewerR1.zip

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

Comments

 

merrillaldrich said:

Shout-back to Brent Ozar for featuring a preview version on his web cast http://www.brentozar.com/archive/2013/02/how-does-sql-server-store-data/

March 1, 2013 5:05 PM
 

Jason said:

Merrill, thanks for sharing this, I can't wait to get some in-depth file layout learning on!

March 2, 2013 8:25 AM
 

csm said:

As Jason said, thank you for sharing this!!. It will be very helpful in my courses.

March 4, 2013 9:29 AM
 

Andy "SQLBek" said:

Wow, this is a fantastic tool!  Thanks for taking the time to share it - looking forward to experimenting with it this week!

March 4, 2013 9:32 AM
 

merrillaldrich said:

You all are quite welcome - I've had fun and learned a lot working with it.

March 4, 2013 12:50 PM
 

asava samuel said:

merrillaldrich

Here is an ORM that works with SQL Server

https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

March 5, 2013 12:13 PM
 

Davide Mauri said:

Great, thanks a lot!!!!

March 6, 2013 4:10 PM
 

Régis Baccaro said:

This is a great tool. Thank you !

March 7, 2013 1:00 AM
 

Merrill Aldrich said:

Early in my career as a DBA, I have to admit I didn’t quite “get” what all the fuss was about with the

March 12, 2013 1:08 AM
 

MudLuck said:

Thanks so much for giving me a tool the drool set in management can wrap there head around. You have done much to promote our industry. All the best..  

June 10, 2013 11:56 AM
 

Mudluck said:

Quick question dealing with a test host on different domain.  Is there a way to pass sql login credentials under SQL Server Instance.  Will try to hack just thought to help the next guy..

June 10, 2013 12:26 PM
 

merrillaldrich said:

Mudluck - SQL auth is not available in the released version but I do have that on my list for future enhancements. If you are up to hacking a little c#, you could modify the section in the source that creates the connection string for the DB - it's nothing complex.

June 10, 2013 5:00 PM
 

Merrill Aldrich said:

Back in the spring I released a 1.0 version of a utility that will show the structure of SQL Server files

August 8, 2013 3:28 PM
 

Merrill Aldrich said:

Just ahead of presenting it at SQL Saturday in my home town of Minneapolis / Saint Paul, I’m happy to

October 9, 2013 4:09 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement