THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

DYK: Restoring a Database Retains Index Fragmentation

I am not sure where the “myth” that restoring a database removes the index fragmentation, or that restoring a database causes a database to become fragmented came from recently, but twice in the last few days I have seen questions asked about the impact of restoring a database has on index fragmentation.  The first time was a quick Tweet from Jorge Segarra following SQL Saturday that prompted some discussion on twitter that I disagreed with and so set out to prove that a restored copy of a database has exactly the same fragmentation as the source database at the time of the backup.  To do this, I used AdventureWorks in a isolated VM on my laptop.

First I took a backup of the AdventureWorks database and then restored it to AdventureWorks3 on the same instance (I already had AdventureWorks2 on the instance from some other operation I was testing, but I have no idea what at this point in time).  Then I ran the following query which uses the EXCEPT operator to find Index stats from the source AdventureWorks database that aren’t in the restored AdventureWorks3 database and then unions the result set with the reverse EXCEPT to find index stats from the restored AdventureWorks3 database that aren’t in the source AdventureWorks database. 

SELECT * FROM
(
  
SELECT  
      
OBJECT_ID, index_id, partition_number, index_type_desc
      
alloc_unit_type_desc, index_depth, index_level,
      
avg_fragmentation_in_percent, fragment_count
      
avg_fragment_size_in_pages, page_count
      
avg_page_space_used_in_percent, record_count,
      
ghost_record_count, version_ghost_record_count
      
min_record_size_in_bytes, max_record_size_in_bytes
      
avg_record_size_in_bytes, forwarded_record_count 
  
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'DETAILED')
EXCEPT
   SELECT 
      
OBJECT_ID, index_id, partition_number, index_type_desc
      
alloc_unit_type_desc, index_depth, index_level,
      
avg_fragmentation_in_percent, fragment_count
      
avg_fragment_size_in_pages, page_count
      
avg_page_space_used_in_percent, record_count,
      
ghost_record_count, version_ghost_record_count
      
min_record_size_in_bytes, max_record_size_in_bytes
      
avg_record_size_in_bytes, forwarded_record_count 
  
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks3'),NULL,NULL,NULL,'DETAILED')
UNION ALL
  
SELECT 
      
OBJECT_ID, index_id, partition_number, index_type_desc
      
alloc_unit_type_desc, index_depth, index_level,
      
avg_fragmentation_in_percent, fragment_count
      
avg_fragment_size_in_pages, page_count
      
avg_page_space_used_in_percent, record_count,
      
ghost_record_count, version_ghost_record_count
      
min_record_size_in_bytes, max_record_size_in_bytes
      
avg_record_size_in_bytes, forwarded_record_count 
  
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks3'),NULL,NULL,NULL,'DETAILED')
EXCEPT
   SELECT 
      
OBJECT_ID, index_id, partition_number, index_type_desc
      
alloc_unit_type_desc, index_depth, index_level,
      
avg_fragmentation_in_percent, fragment_count
      
avg_fragment_size_in_pages, page_count
      
avg_page_space_used_in_percent, record_count,
      
ghost_record_count, version_ghost_record_count
      
min_record_size_in_bytes, max_record_size_in_bytes
      
avg_record_size_in_bytes, forwarded_record_count 
  
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'DETAILED')
)
AS tab

The result returned is an empty result set, proving that index fragmentation is identical between the source and restored databases which is exactly what I expected to occur.  This has nothing to do with file system fragmentation of the data file, which is an entirely different subject, this is specific to logical fragmentation of indexes, and heaps inside the database file.

Published Wednesday, January 27, 2010 9:43 PM by Jonathan Kehayias

Comments

 

Aaron Bertrand said:

I am not sure where the myth comes from.  As you have proven, a restore copies the existing data pages (including fragmentation) exactly as existed in the source.  This has always been my understanding.

January 27, 2010 11:06 PM
 

Mika said:

There are also some situations where this is quite useful. For example I have some large databases from which I monitor fragmentation with simple showcontig and cursor based scheduled task. As these databases are also used 24/7 and I'd hate to disturb the users by running any extra tasks in the server, I've set up a scheduled database copy and restore routine to get a copy of the database up and running on a secondary server. Since the fragmentation remains in the restored database I can run my fragmentation monitoring tasks against it.

January 28, 2010 7:31 AM
 

Jonathan Kehayias said:

Aaron,

Its always been my understanding as well, and I was initially surprised to hear the question on Saturday and then again to see it by email yesterday morning.  I would guess that it is something local to Tampa, because both instances came from local people, one from SQL Saturday #32, which also had some discussion on twitter Saturday night, and then again through a local group mailing list yesterday.  

I always expected it to work exactly like it does, but since I went through the efforts to prove it I figured I'd blog it.

January 28, 2010 8:35 AM
 

noeldr said:

Never heard that one before. Restore knows only about pages and LSNs there is nothing it does about fragmentation. But hey there is always a first time I guess

January 28, 2010 4:19 PM
 

TiborKaraszi said:

The BACKUP command used to be called DUMP. IT wasn't really that bad. SQL Server just dumps the pages into the storage container (backup dive) page numbers and all. Then the restore process need to put back all pages in the same location (inside each file) as they were when backup was produced. If this wasn't the case, backup would need to unwind linked list etc - that wouldn't not be quick. I usually was that you get a binary copy when doing backup and restore (perhaps some of the recovery wirh *could* produce a difference but I doubt it considering automatic page repair for db mirroring.

January 29, 2010 12:30 PM
 

Jorge Segarra said:

Jonathan, don't worry too much it's not a growing pandemic. My tweet and the user group mailer question came from one and the same source. I was just trying to ping as many resources as possible. Also I knew you'd answer on the list which is where Nathan (who originally asked the question) would also easily see. Thanks again for the thorough verification of the answer though!

February 5, 2010 2:27 PM
 

Joe Hayes said:

very good.  Thanks :-)

February 27, 2010 9:37 AM
 

Pei Zhu said:

Does the "detach, copy the mdf, ldf to different location then reattach" make difference?

June 8, 2010 4:22 PM
 

Jonathan Kehayias said:

Pei,

No it doesn't make a difference.  The data internally in the database files doesn't change by copying them to a different location.  The pages stay in the same order internally.

June 18, 2010 3:39 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement