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.

USE tempdb
GO
IF DB_ID('SnapshotIndexDemo') IS NOT NULL
	BEGIN 
		DROP DATABASE SnapshotIndexDemo
	END	
GO
CREATE DATABASE SnapshotIndexDemo
GO
USE SnapshotIndexDemo
GO		
CREATE TABLE [dbo].[Sharknado](
	[CustomerID] [INT] IDENTITY(1,1) PRIMARY KEY,
	[CustomerName] [VARCHAR](8000) NULL,
) ON [PRIMARY]
GO
-- Create us a NCL just because
CREATE NONCLUSTERED INDEX IX_Sharknado on Sharknado (CustomerID) include (CustomerName);
GO

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.

DECLARE @x INT = 1
WHILE @x < 50000
BEGIN
	INSERT Sharknado (CustomerName)
	SELECT REPLICATE('c', 5000)

	SET @x = @x + 1
END
GO

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.

SELECT Database_ID,
	db_name(database_id) 
	, (SUM(size)*8192)/1024000 as 'Size in MB'
FROM sys.master_files
WHERE database_id = db_id('SnapshotIndexDemo')
GROUP BY Database_ID

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

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.

SELECT index_type_Desc,fragment_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id('SnapshotIndexDemo')
	, object_id('Sharknado'), NULL, NULL, 'LIMITED')
GO

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

-- This will delete every even row by using the modulo ("Mod") operator
-- Modulo - http://technet.microsoft.com/en-us/library/ms190279.aspx
Use SnapshotIndexDemo
GO
DELETE dbo.Sharknado
WHERE CustomerID % 2 = 0
GO

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

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.

USE tempdb
GO
CREATE DATABASE SnapShotIndexDemo_ss
ON
(
NAME = SnapShotIndexDemo,
FILENAME = 'c:\Temp\SnapShotIndexDemo.ss'
)
AS SNAPSHOT OF SnapShotIndexDemo
GO

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.

USE [SnapshotIndexDemo]
GO
ALTER INDEX [IX_Sharknado] ON [dbo].[Sharknado] REBUILD
GO

Let’s check the fragmentation.

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.

USE tempdb
GO
RESTORE DATABASE SnapShotIndexDemo
FROM DATABASE_SNAPSHOT ='SnapShotIndexDemo_ss'
GO

And check the fragmentation again.

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.

USE [tempdb]
GO
-- Clean up the snapshot
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SnapShotIndexDemo_ss')
DROP DATABASE [SnapShotIndexDemo_ss]
GO
-- Clean up the database
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SnapShotIndexDemo')
DROP DATABASE [SnapShotIndexDemo]
GO

© 2014, John Morehouse. All rights reserved.