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:
- Create a dummy database
- Insert a bunch of data
- Delete 1/2 of the data
- Take a snapshot
- Rebuild one of the indexes
- 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