MSSQL Server Heap Table Fragmentation Maintenance Oluşturma

Bu makalede heap tablolarda oluşan fragmentation işlemler için neler yapılması gerektiğini ele almış olacağız. Heap tablolar için fragmentation konusu normal index fragmentation’dan farklıdır. Çünkü heap tabloda clustered index yoktur ve asıl problem:

Forwarded records (taşınmış satırlar)
Sayfa dağınıklığı
I/O performans düşüşü

Heap fragmentation düzeltilmesiyle ilgili en doğru yöntem Clustered index oluşturmaktadır. Microsoft’un önerdiği yöntem budur.

CREATE CLUSTERED INDEX IX_Tablo ON dbo.Tablo(ID);

Heap → B-tree yapıya dönüşür.
Forwarded record problemi biter.
En sağlıklı yöntem

Eğer tabloda PK varsa genelde zaten clustered olur. Heap olmasında öncelikle log tablolarının olması veya kullanıcının tablo oluştururken gözden kaçırmasıdır.

Aşağıdaki komut ile hesap tablolar üzerinde fragmentation oranlarını ve Forwarded record sayısını görebiliriz. Bu komut öncesinde veritabanımızda bulunan hesap tabloların satır sayısına ve boyutuna bakılması gerekmektedir. Sayfamızda bulunan ilgili makalede scripti bulabilirsiniz.

Aşağıdaki sorgu geç dönebilmekte bu sebepten heap tabloların kullanım durumu satır sayısına odaklanabilir.

use AdventureWorks2017

SELECT  
    Database_id, OBJECT_SCHEMA_NAME([object_id]) AS SchemaName,
    Object_name([object_id]) AS TableName, index_type_desc, 
    Avg_fragmentation_in_percent, rowmodctr,forwarded_record_count
FROM sys.dm_db_index_physical_stats(db_id(),object_id(''),null,null,'detailed') AS SDDIPS
INNER JOIN sys.sysindexes AS SI ON SDDIPS.[object_id] = SI.id  AND SDDIPS.index_id = SI.indid
--Inner join sysobjects AS SO on  SI.id = SO.id
WHERE index_level = 0 
  AND index_type_desc = 'HEAP' 
 -- AND Avg_fragmentation_in_percent < 100 AND rowmodctr > 100 
  AND forwarded_record_count < 100; --Onemli Bolum

rowmodctr: tabloda son istatistik güncellemesinden sonra değişen satır sayısıdır.

forwarded_record_count: Heap tablolara özel en kritik metriktir. Bir satır UPDATE sonrası sığmazsa başka sayfaya taşınmaktadır. Eski yerlerde Forward pointer kalır. Her select ek IO yapar performans anlamında sıkıntıya sebep verir. forwarded_record_count değeri 1000 üzeriyse REBUILD işlemi önerilmektedir. Çok yüksekse Clustered index oluşturmamız şarttır.

Heap’i rebuild etmek (clustered index eklemeden) için aşağıdaki komut kullanılmaktadır.

ALTER TABLE Person.Address REBUILD

Bu yapıyla Forwarded record’lar temizlenir. Sayfalar yeniden düzenlenmektedir. Ama zamanla tekrar oluşur. Aşağıdaki resimde dikkat etmişseniz Rebuild işlemi yapılmasına rağmen herhangi bir Avg_fragmentation_in_percent değeri değişmedi. Sebebi bu komut sadece Forwarded record’ları temizlemektedir. Avg_fragmentation_in_percent bu değeri her zaman düşürmez. Heap tablolarda görülen fragmentation logical index fragmentation değil extend/page sıralama dağınıklığıdır. Clustered ve non clustered indexlerde rebuild işlemi sonrasında fragmentation düşer. Heap tablolarda ise sadece Forwarded record’lar temizlenmektedir.

Tüm heap tabloları otomatik rebuild eden script:

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql +=
'ALTER TABLE [' + s.name + '].[' + t.name + '] REBUILD;
'
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE NOT EXISTS (
SELECT 1
FROM sys.indexes i
WHERE i.object_id = t.object_id
AND i.type = 1 -- clustered index
);

PRINT @sql;
-- EXEC sp_executesql @sql; -- çalıştırmak için aç

Veritabanındaki tüm heap tabloları rebuild eder. Online on parametresini desteklememektedir. Bu sebepten tablolarda LOCK oluşmasına sebebiyet verecektir.

Heap tablolarda REBUILD işlemi lock oluşturmaktadır. Rebuild işlemi sırasında sql server sch-m(schema modification) lock alır. Bu lock select dahil tüm erişimleri bloklamaktadır. İşlem bitene kadar tablolara kimse giremez. Index backım joblarımızda yapıldığı gibi online değildir. Clustered ve non clustered index için online ifadesi yapılabilirken heap tablolar için bu geçerli değildir. Gerçek ortamlarda yapılan yöntem geçici olarak bir clustered index oluşturup daha sonra tekrardan index yapısı silinmesi Forwarded records temizlenir. Sayfalar yeniden düzenlenir. İşlemin büyük kısmı ONLINE olur.

Ne zaman müdahale edilmelidir. Aşağıdaki komutla heap tablolarda kontrol işlemi yapılmaktadır.

Test ortamı olduğu için forwarded_record_count değeri küçüktür seçilmiştir.

SELECT
OBJECT_NAME(object_id) AS TableName,
forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE index_id = 0
AND forwarded_record_count < 100;

Normal sistemlerde forwarded_record_count > 1000 ise rebuild gerekir.

Gerçek hayatta en doğru yaklaşım performans anlamında kritik olan sistemlerde Heap bırakılması tercih edilmez. Clustered index eklenmesi gerekmektedir.

Heap tablolara clustered index yapısına dönüştürülmeyecekse ilk olarak heap tabloların script ile bulunması gerekmektedir. İlgili makale yardımıyla bulunabilir. Daha sonra Forwarded record>0 olan heap tablolar seçilir. Online clustered index yapıları oluşturulur. Veri yeniden temizlenir. Forwarded record temizlenir. İşlem büyük ölçüde online olmaktadır.

Yukarıda yapılacak işlemler tablo bazlı yapılması ve transaction log büyümesi izlenmelidir.

Bu makalede Heap tablolarda fragmentation oranları yüksek olan tablolar üzerinde REBUILD işlemi nasıl yapılmalı bu işlemin faydasını görmüş olduk. Başka bir makalede görüşmek dileğiyle..

“Öyle ise akrabaya, yoksula ve yolcuya hakkını ver. Bu, Allah’ın hoşnutluğunu kazanmak isteyenler için daha hayırlıdır. İşte onlar kurtuluşa erenlerdir.” Rûm Sûresi, 30-38

Bir yanıt yazın