SQL Server Index Maintenance – Index Defragmentation

SQL Server da kullanılan Heap, Clustered ya da NonClustered Index’ler insert,update,delete yapıldıkça otomatik olarak güncellenirler ve yapılan bu işlemler sonucunda fragmante olurlar. Bu da Index’in performansını olumsuz etkiler. Belirli aralıklarla fragmante olan bu indexleri bulup drop-create, ReOrganize ya da Rebuild işlemleriyle fragmante oranlarının düşürülmesi gerekmektedir. Bugünkü makalemde bu defragmentation işlemlerini inceliyor olacağız.

 

Makalemizdeki ana başlıklarımız şu şekilde olacak;

 

1.       Fragmentation oranlarının belirlenmesi – Rebuild – ReOrganize Kararı

2.       Reorganize Index

3.       Rebuild Index

4.       Defragmentation Script’i

5.       Özet

6.       Kaynaklar

 

1. Fragmentation Oranlarının Belirlenmesi – Rebuild – ReOrganize Kararı

 

Defrag işlemini başlamadan önce ilk yapılması gereken işlem hangi Index’in ne kadar fragmante olduğunu  bulmaktadır. Bulunan oranlara göre Index’in Rebuild ya da Reorganize edilmesine karar verilir. Her firmaya göre bu oranlar farklılık gösterebileceği gibi piyasada kabul görmüş oranlar ve alınacak aksiyon aşağıdaki gibidir.

 

Fragmante Oranı	İşlem
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

 

(*) Rebuild index operasyonu online ya da offline yapılabilmektedir. Reorganize ise her daim online olarak işletilir.

Index’lerin fragmentation oranlarını bulmak için aşağıdaki script’i kullanabiliriz.

 

 

Use AdventureWorks

GO

SELECT

      ps.object_id,

      i.name as IndexName,

      OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,

      OBJECT_NAME (ps.object_id) as ObjectName,

      ps.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’) ps

INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id

WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0

ORDER BY avg_fragmentation_in_percent desc

 

 

Bu script’i AdventureWorks DB’sinde çalıştırdığımızda aşağıdaki gibi bir sonuç ortaya çıkacaktır.

 

 

Bu sorgu sonucunda örneğin Sales.Individual table’ındaki XMLPROPERTY_Individual_Demographics Index’in %99 oranında fragmante olduğunu görüyoruz. Fragmante oranı %30 dan fazla olduğu için bu index’i  ReBuild edeceğiz. 

 

2.Reorganize Index

 

Bir index’i reorganize etmek, clustered veya nonclustered Index’te bulunan leaf level page’lerin tekrardan fiziksel sıralamaya sokulması demektir. Bu da index üzerinde yapılan sorgulamaların daha performanslı çalışmasını sağlar. Reorganize işlemi sırasında eski page’ler kullanılır ve yeni hiç bir page allocate edilmez. Dolayısıyla reorganize yapmak için ekstra bir disk alanına ihtiyaç duyulmaz.

Örnek kullanımı aşağıdaki gibidir.

 

Use AdventureWorks

GO

ALTER INDEX [PXML_Individual_Demographics]

      ON [Sales].[Individual] REORGANIZE

 

Reorganize minimum sistem kaynağı tükettiği ve online olarak yapılıp blocking lere sebep olmadığı için %30 dan az fragmante olmuş index’lerde kullanımı tercih edilir.

 

Eğer index %30 dan fazla fragmante olduysa daha iyi bir sonuç almak için Reorganize yerine Rebuild kullanılmalıdır. 

 

3.Rebuild Index

 

Rebuild işlemi aslında index’i drop edip tekrar create etmektir. Dolayısıyla fragmante tamamiyle kaldırılır ve index fill factor değeri göz önünde tutularak index page’leri tekrar allocate edilir. Index row’ları birbirini takip eden page’lerin içine sırasıyla kayıt edilir. Bu da bir index sorgulamasında gerekli kayıdı getirmek için daha az page okunacağından dolayı performans artışı sağlar.

 

Örnek kullanımı aşağıdaki gibidir.

 

ALTER INDEX [PXML_Individual_Demographics]

      ON [Sales].[Individual]  REBUILD   WITH (ONLINE = ON)

 

Tekrar bir index create i söz konusu olduğu için ekstra disk alanına ihtiyaç duymaktadır. 

 

4. Defragmentation Script’i

 

Ben instance’larımda aşağıda yazmış olduğum SP’yi kullanmaktayım. Bu SP parametre olarak aldığı DB’de bulunan index’lerin fragmante oranlarını sorguluyor, çıkan sonuca göre fragmante oranı %30 dan fazla olanları Rebuild, az olanları ReOrganize ediyor. Sonuç olarakta kaç Index’i rebuild, kaç index’i Reorganize ettiği bilgisini dönüyor.

 

CREATE PROC [INDEX_MAINTENANCE] @DBName VARCHAR(100)

AS BEGIN

            SET NOCOUNT ON;

            DECLARE

                  @OBJECT_ID INT,

                  @INDEX_NAME sysname,

                  @SCHEMA_NAME sysname,

                  @OBJECT_NAME sysname,

                  @AVG_FRAG float,

                  @command varchar(8000),

                  @RebuildCount int,

                  @ReOrganizeCount int

 

            CREATE TABLE #tempIM (

                  [ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,

                  [INDEX_NAME] sysname NULL,

                  [OBJECT_ID] INT NULL,

                  [SCHEMA_NAME] sysname NULL,

                  [OBJECT_NAME] sysname NULL,

                  [AVG_FRAG] float

            )          

            SELECT @RebuildCount=0,@ReOrganizeCount=0

           

            –Get Fragentation values

            SELECT @command=

                  ‘Use ‘ + @DBName + ‘;

                  INSERT INTO #tempIM (OBJECT_ID, INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG)

                  SELECT

                        ps.object_id,

                        i.name as IndexName,

                        OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,

                        OBJECT_NAME (ps.object_id) as ObjectName,

                        ps.avg_fragmentation_in_percent

                  FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, ”LIMITED”) ps

                  INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id

                  WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0

                        and ps.database_id=DB_ID(”’+@DBName+”’)

                  ORDER BY avg_fragmentation_in_percent desc

                  ‘

           

            exec(@command)

            DECLARE c CURSOR FAST_FORWARD FOR

                  SELECT OBJECT_ID,INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG

                  FROM #tempIM

            OPEN c

            FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG

            WHILE @@FETCH_STATUS = 0

            BEGIN

                  –Reorganize or Rebuild

                  IF @AVG_FRAG>30 BEGIN

                        SELECT @command = ‘Use ‘ + @DBName + ‘; ALTER INDEX [‘ + @INDEX_NAME +‘] ON [‘

                                                 + @SCHEMA_NAME + ‘].[‘ + @OBJECT_NAME + ‘] REBUILD   WITH (ONLINE = ON  )’;

                        SET @RebuildCount = @RebuildCount+1

                  END ELSE BEGIN

                        SELECT @command = ‘Use ‘ + @DBName + ‘; ALTER INDEX [‘ + @INDEX_NAME +‘] ON [‘

                                                 + @SCHEMA_NAME + ‘].[‘ + @OBJECT_NAME + ‘] REORGANIZE ‘;

                        SET @ReOrganizeCount = @ReOrganizeCount+1

                  END

                                                

                  BEGIN TRY

                        EXEC (@command); 

                  END TRY

                  BEGIN CATCH

                  END CATCH

                 

                  FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG

            END

            CLOSE c

            DEALLOCATE c

           

            DROP TABLE #tempIM

           

            SELECT cast(@RebuildCount as varchar(5))+‘ index Rebuild,’+cast(@ReOrganizeCount as varchar(5))+‘ index Reorganize edilmistir.’ as Result

END        

 

 

Bu SP’yi master yada DBA scriptlerinizi allocate ettiğiniz bir DB’ye create edip daha sonra şu şekilde çağırabilirsiniz.

 

exec master.dbo.INDEX_MAINTENANCE ‘AdventureWorks’

 

Hatta her gece çalışacak bir job vasıtasıyla istediğiniz DB’leri ekleyerek otomatik Index Maintenance yapılmasını sağlayabilirsiniz. 

 

5.Özet

 

Makalemizde Index fragmentation oranlarını nasıl sorgulayacağımızı, bu oranlara göre Rebuild, Reorganize kararını, Rebuild ve ReOrganize ın nasıl işlediklerini gördük. Daha performanslı bir sistem için sizde periyodik olarak index fragmentation larınıza bakmalı ve ihtiyacı olan index’leri defrag etmelisiniz.

 

6.Kaynaklar

 

http://technet.microsoft.com/en-us/library/ms189858.aspx

http://msdn.microsoft.com/en-us/library/ms179542.aspx