SQL Server Index Bakımı– Index Defragmentation

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. 

(*) 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 [veritabani]

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

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

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.

Use [veritabani]
GO

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 'VeriTabanıAdı'

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

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

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