Part 2 of a blog series visually demonstrating the layout of objects on data pages in SQL Server
Part 1
In Part 1 of this series, I introduced a little demo app that renders the layout of pages in SQL Server files by object. Today I’ll put that app through its paces to show, in vivid color (well, teal, anyway) the destructive power of the famous Re-Index Then Shrink anti-pattern for index maintenance.
This one is very easy to demo, so let’s go!
First, I created a demo database VizDemo1, with a single 200 MB data file. Into that database I placed a canonical table – highly simplified for this example – clustered on an ever-increasing integer, using identity():
USE VizDemo1
GO
CREATE TABLE dbo.SampleCustomers (
id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED,
buncha char(500) default 'A',
big char(500) default 'B',
vals char(500) default 'C'
);
GO
Then we populate that table with some dummy data:
INSERT dbo.SampleCustomers DEFAULT VALUES;
GO 40000
And finally, fire up the little visualizer app and process the database:
The small color bands at the top left corner of the image are the system tables and such that are in every “empty” database to make it run. The blue/green/teal area is the new table we created and populated with sample data, and the gray area represents empty regions in the file.
As expected, the table started writing into the first available space, and, because the cluster key is increasing, pages were allocated to the end of the page sequence in order, and we end up with a crazy-perfect, contiguous linked list on disk.
You can see small darker bars at intervals within the table – most of the pages in the index are “type 1” pages, which are the leaf-level/rows in the clustered index. Those bars are “type 2” index pages that have the upper level(s) of the index. The reason they are darker is that those are a disruption in the leaf level linked list, and the app shades such disruptions as a way to see fragmentation. The list has to “hop over” those pages and then continue on the other side. It’s technically fragmentation, but at this point not harmful at all – but remember that darker color that shows a break in the page order.
A side note: in the midst of the gray area you can see one orange line (and another in the sea of teal). Those are “type 11” PFS pages, which happen on a fixed interval in every file. I don’t think they ever move – they track file allocation and free space metadata. They are like rocks in the stream…
Now, what happens if we re-index this bad boy? Well, a re-index operation has to write all the pages for the object into new, blank pages in the file, and then abandon the old pages. I run:
-- This "moves" all the data toward the end of the file, into free areas
ALTER INDEX ALL ON dbo.SampleCustomers REBUILD;
GO
Then re-analyze the file. As expected, the table has “moved” toward the end of the file, and left free space toward the beginning. It’s still not fragmented, because we had enough room, and it was written in order into that new area by the rebuild:
We can see the gray area near the top is all the “abandoned” pages where the index was, and the data has all moved down into the free area. Ah, but that seems wasteful to some people, am I right? All that empty space – the file could be smaller!
Let’s see the damage that Shrink File does. Imagine that I do this:
DBCC SHRINKFILE (N'VizDemo1' , 70)
GO
First, before we shrink, let’s just scroll down and look at the end of the file:
We have two conditions – the gray part of the file is sort of OK to shrink. There’s just a lone PFS page out there, and removing that does no harm. But once we get into that blue area, the data has to be moved back up into the beginning of the file. Here’s where the problem lies, as I learned from Mr. Paul Randal – the shrink routine will move a page at a time back into that free space, starting from the end, going backward. That makes the pages land in approximately reverse order from the correct index order. Perfect fragmentation. Let’s see if this tool proves him right. Shrink, then re-analyze:
Yep, it’s not immediately apparent, perhaps, but that teal color is a darker shade that indicates every page is a fragment boundary in most of the index – perfect fragmentation! Here’s a better view:
So, how can we clean that up? Well, with a rebuild. But … we need that bigger file. In fact, practically any database in production needs this overhead of available space to be able to perform index maintenance. It’s not “wasted” space at all.
ALTER INDEX ALL ON dbo.SampleCustomers REBUILD;
GO
After the rebuild, the index is back toward the end of the file, but it’s also back in order:
Now, in light of this information, imagine nightly re-indexing on a database with … AutoShrink! <shudder>