Indexes & Snapshots

Several weeks ago while at SQL Saturday #271 in Albuqurque, NM, I had someone ask me if you took a snapshot of a database, would any index fragmentation go right along with it. I was fairly confident that the answer was “Yes, it will since a snapshot is at the page level” but I wasn’t quite sure and to be honest, I had never looked into it for myself.

Thus a blog post is born! Basically what we’ll do is:

    1. Create a dummy database
    2. Insert a bunch of data
    3. Delete 1/2 of the data
    4. Take a snapshot
    5. Rebuild one of the indexes
    6. Restore from the snapshot

And if all goes well, we should see that the index fragmentation from the data being deleted is restored when the database is restored from using the snapshot.

Here we go!

First let’s create our dummy database and table.

Now I’m going to load some data.  We need to pump in some data so that we can easily see things are fragmented.  This should explode my database to 400+ MB in size. Give or take.

Just to check, you can run this code to ensure that the database has grown. The size column found in sys.master_files represents the number of 8KB pages the files are, thus we multiple that sum by 8192 (the true size of each page) and then divide by 1,024,000 for the number of bytes in a megabyte.

You can see here that it pushed the database to a little over 400MB. Perfect!

snapshotindex_1

Now that we have some data to play with, let’s see if we can get things fragmented so that we can take a snapshot. We can see from this that there isn’t any fragmentation at this point.  This code will provide the fragmentation levels for all indexes, which in our case is just two.

snapshotindex_3

Now that we’ve shown that things are nice and tidy, let’s create some chaos!!

And then let’s check to make sure that we have fragmentation.

snapshotindex_4

Now let’s take a snapshot!! This code will create the snapshot and should work for you, assuming that you have a “C:\Temp” directory.  If you don’t, just adjust the script to a folder location.  We’ll clean stuff up at the end of this.

Now that the snapshot is created, let’s rebuild the non-clustered index and check the fragmentation.  Once the rebuild is complete, we’ll restore the database using the snapshot that we just took.  If our theory is true, we would expect to see the fragmentation reappear.

Let’s check the fragmentation.

snapshotindex_5

Since we rebuilt the non-clustered index, we would expect for the fragmentation to be 0.  Finally, let’s restore the database using the snapshot that we took earlier.

And check the fragmentation again.

snapshotindex_6

Voila!  The fragmentation is once back again thus our theory is correct.

So, in conclusion, if you have fragmentation in your database and you take a snapshot, you’ll be taking the fragmentation right along with it.

Of course, we should leave the environment as we found it, so here is some code to clean things up if you want.

© 2014, John Morehouse. All rights reserved.

7 Replies to “Indexes & Snapshots”

Leave a Reply