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