A while back, I spent about a month working through several SQL Server performance issues after upgrading three systems from SQL Server 2000 to SQL Server 2005. During that time I would realize that my knowledge of how SQL actually manages data at a low level was sketchy. In the process of resolving said sketchiness, I came across a few resources that proved helpful.

The first thing is to download and install the free SQL Internals Viewer. This tool allows you to visualize how SQL Server organizes data inside data files. You can see that organization down to the file->extent->page->record level. You can how see data is organized in heaps or b-trees (clustered indexes) and how non-clustered indexes are used. Once you you connect to a database with that application and see how all those little boxes it shows fit together, you’ll have a nice conceptual representation that you can later decorate with all the gory details.

Another way to look at pages is DBCC PAGE. Paul Randal shows you how to dump the contents of a page and interpret. You’ll need to turn on trace flag 3604 (DBCC TRACEON (3604)) in order to get the output of the command. SQL Internals Viewer is much better for learning purposes however DBCC PAGE is a handy command to know if you need to check something and don’t have access to SQL Internals Viewer.

Chad Boyd has written an outstanding series of posts that provide a solid understanding of how SQL manages data and all aspects of fragmentation. What I like about these posts is that the visuals he provides are invaluable in illustrating certain principles that are hard to visualize from text-only.

Another great series of posts on storage, indexing and fragmentation is by Paul Randal. I think they are a good supplement to Chad Boyd’s posts as the concepts make more sense after reading Chad Boyd’s posts.

Once you digest all of the above you’ll have a pretty good picture of how SQL Server organizes and manages data within the data files.