I’ve heard more than one person state that they do not need to defrag their tables, because they’re replicated and the source tables are defragged regularly. Let’s set this myth straight.
Repeat after me: defragmentation processes are not replicated.
Good. Thank you.
For those non-believers, let me prove it:
SET NOCOUNT ON; /* Create publication db */ CREATE DATABASE sandbox_publisher; GO /* Create subscription db */ CREATE DATABASE sandbox_subscriber; GO /* Navigate to publisher db */ USE sandbox_publisher; GO /* Create a table to play with */ CREATE TABLE myTable ( myID UNIQUEIDENTIFIER , myColumn VARCHAR(2000) CONSTRAINT PK_myTable PRIMARY KEY CLUSTERED (myID) ); /* NOTE: please never, ever use this type of clustered index in real-life. The whole purpose is to generate substantial fragmentation for illustrative purposes. */ /* Populate it with some non-sequential records to generate fragmentation */ DECLARE @iteration INT; SET @iteration = 0; WHILE @iteration < 1000 BEGIN INSERT INTO myTable SELECT NewID(), REPLICATE('A', 2000); SET @iteration = @iteration + 1; END /* Now let's verify that we really did make a mess of our clustered index */ SELECT avg_fragmentation_in_percent , page_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') OPTION (MaxDop 1); /* Results: avg_fragmentation_in_percent page_count ---------------------------- -------------------- 99.0157480314961 508 99% fragmented, not too shabby! Your results may vary slightly, based on the guid values generated by NewID(). */ /* Now, let's set up replication */ USE sandbox_publisher; GO /* Enable publication */ EXECUTE SP_REPLICATIONDBOPTION @dbname = N'sandbox_publisher' , @optname = N'publish' , @VALUE = N'true'; GO /* Create our publication */ EXECUTE SP_ADDPUBLICATION @publication = N'myTestPublication' , @sync_method = N'concurrent' , @description = N'Test Publication' , @status = N'active' , @independent_agent = N'true' , @immediate_sync = N'true' , @retention = 0 GO /* Create our snapshot agent */ EXECUTE SP_ADDPUBLICATION_SNAPSHOT @publication = N'myTestPublication' , @frequency_type = 1; /* Add our table as an article */ EXECUTE SP_ADDARTICLE @publication = N'myTestPublication' , @article = N'myTable' , @source_owner = N'dbo' , @source_object = N'myTable' , @type = N'logbased' , @destination_table = N'myTable' , @destination_owner = N'dbo' , @ins_cmd = N'CALL sp_MSins_dbomyTable' , @del_cmd = N'CALL sp_MSdel_dbomyTable' , @upd_cmd = N'MCALL sp_MSupd_dbomyTable'; GO /* Generate an initial snapshot */ EXECUTE sp_startpublication_snapshot @publication = N'myTestPublication'; /* Create our subscription */ EXECUTE SP_ADDSUBSCRIPTION @publication = N'myTestPublication' , @subscriber = N'YourServerName' , @destination_db = N'sandbox_subscriber'; /* At this point, basic transactional replication should be configured. You should now have a copy of myTable in your subscription database. */ USE sandbox_subscriber; GO /* Let's take a look at fragmentation on the subscription database. */ SELECT avg_fragmentation_in_percent , page_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') OPTION (MaxDop 1); /* Results: avg_fragmentation_in_percent page_count ---------------------------- -------------------- 0.29940119760479 334 Less than 1% fragmented! Why is this? During initialization, a copy of the schema and data is sent to the subscriber. The data is sorted before insertion into the destination table. */ USE sandbox_publisher; GO /* Let's insert another 1000 records and see what happens... */ DECLARE @iteration INT; SET @iteration = 0; WHILE @iteration < 1000 BEGIN INSERT INTO sandbox_publisher.dbo.myTable SELECT NewID(), REPLICATE('A', 2000); SET @iteration = @iteration + 1; END SELECT avg_fragmentation_in_percent , page_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') OPTION (MaxDop 1); /* Results: avg_fragmentation_in_percent page_count ---------------------------- -------------------- 98.6193293885602 1014 The publisher is still very fragmented (as expected) */ USE sandbox_subscriber; GO SELECT avg_fragmentation_in_percent , page_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') OPTION (MaxDop 1); /* Results: avg_fragmentation_in_percent page_count ---------------------------- -------------------- 95.4034729315628 979 But now the subscriber is fragmented too! This is because each individual transaction is replicated, so out-of-order inserts affect both the publishing and the subcribing databases equally. */ /* Finally, let's rebuild our index on the publisher and see what happens */ USE sandbox_publisher; GO ALTER INDEX PK_myTable ON myTable Rebuild; SELECT avg_fragmentation_in_percent , page_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') OPTION (MaxDop 1); /* Results: avg_fragmentation_in_percent page_count ---------------------------- -------------------- 0 667 0% fragmentation in the publishing table! What about our subscribing table? */ USE sandbox_subscriber; GO SELECT avg_fragmentation_in_percent , page_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'MyTable'), NULL , NULL, N'Limited') OPTION (MaxDop 1); /* Results: avg_fragmentation_in_percent page_count ---------------------------- -------------------- 95.4034729315628 979 As you can see, the subcribing table was completely unaffected by the defrag activities on the publisher. */ |
So there you have it, proof that defrag operations are not replicated! Now go defrag your replicated tables. 🙂
Oh, and if you need help defragging your indexes, check out my index defrag script (2005, 2008).
Pingback: First round of SQLServerPedia syndicated bloggers | Brent Ozar - SQL Server DBA
Heya. Gentle poke and Hello World 🙂