Pazartesi , 20 Kasım 2017

SQL Serverda Küçük Boyutlu Tablolardaki Yüksek Fragmentation Oranı

İndekslerin fragmantasyon oranlarının düzenli bir şekilde takip edilmesi ve fragmantasyon oranının yüksek olduğu durumlarda indeks üzerinde Rebuild veya Reorganize işlemlerinin yapılması veritabanı performansı için hayati önem taşımaktadır.

Özellikle küçük boyutlu tablolarda fragmantasyon oranını kontrol ettiğimizde fragmantasyon oranının yüksek olduğunu, hatta ilgili indeksimizi Rebuild/Reorganize ettiğimizde dahi bu oranın düşmediği görebiliriz. Şimdi hangi durumlarda böyle bir sorunla karşılaşabileceğimizi ve sebebini bir örnek üzerinde görelim. İlk olarak aşağıdaki gibi örnek bir veritabanı oluşturalım. Daha sonra tablomuza örnek birkaç kayıt ekleyelim.

CREATE DATABASE Test

GO

USE Test
GO

CREATE TABLE Fragmentation(

       Id INT identity(1, 1)

      ,Name CHAR(8000) DEFAULT 'ismailadar.com'
)

INSERT INTO Fragmentation


VALUES (DEFAULT)

GO 8

Yukarıdaki gibi öncelikle Test adında bir veritabanı oluşturduktan sonra Fragmentation adında iki tane kolonu olan bir tablo oluşturduk. Örneğimizde fazla insert yapmamak adına tablomuzdaki her kaydın page’i doldurması için veri tipi olarak char(8000) veri tipini seçtik. Ayrıca Name kolonu için bir default değer tanımladık. Son olarak varsayılan değerleri kullanarak tablomuza 8 tane kayıt ekledik. Böylece kayıtlarımız 8 tane page’de tutulacak şekilde organize edildi. Şimdi aşağıdaki gibi tablomuz üzerinde bir tane Clustered Indeks oluşturalım ve fragmantasyon oranını kontrol edelim.

CREATE CLUSTERED INDEX IXC_Id ON Fragmentation(Id)

Yukarıdaki gibi Clustered İndeksimizi oluşturduktan sonra şimdi aşağıdaki gibi indeksimizin fragmantasyon oranını kontrol edelim.


SELECT DB_NAME(database_id) Db

       ,OBJECT_NAME(object_id) tablo

       ,index_type_desc

       ,alloc_unit_type_desc

       ,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID('Test'), OBJECT_ID('Fragmentation'), 1, NULL, 'Limited')

Yukarıdaki sorgumuzun sonucunda indeksimizin fragmantasyon oranı aşağıdaki gibi görebiliriz.

1

 

Yukarıdaki resimde gördüğümüz gibi indeksimizin fragmantasyon oranı yüzde 50 olarak görünüyor. Şimdi indeksimizi Rebuild ederek tekrar fragmantasyon oranımızı kontrol edelim.

 ALTER INDEX IXC_Id ON Fragmentation REBUILD  

Rebuild işlemini yukarıdaki gibi yaptıktan sonra tekrar fragmantasyon oranını kontrol edelim.

2

Yukarıdaki resimde gördüğümüz gibi Rebuild işlemine rağmen fragmantasyon oranında bir değişlik olmadı. Şimdi tablomuza aşağıdaki gibi 20 tane daha kayıt ekleyip tekrar fragmantasyon oranımızı kontrol edelim.

INSERT INTO Fragmentation
VALUES (DEFAULT)
GO 20

3

Yukarıdaki resimde gördüğümüz üzere tablomuza 20 kayıt daha ekledikten sonra fragmantasyon oranımızın az da olsa düştüğünü görmüş olduk. Şimdi indeksimizi Rebuild edelim ve tekrar fragmantasyon oranını kontrol edelim.

 ALTER INDEX IXC_Id ON Fragmentation REBUILD 

4

Yukarıdaki resimde de gördüğümüz üzere indeksimizi Rebuild ettikten sonra fragmantasyon oranımız sıfırlandı. Bu durumun sebebi ise SQL Server’ın mimarisinden kaynaklanmaktadır. SQL Server mimari gereği bir tablodaki kayıtların boyutu 24 page’den daha az yer kaplıyorsa bu pageler SQL Server tarafından Mixed Extent’lerde tutulur. Bu sebeple tablomuzu ile oluşturduğumuzda 8 tane page kullanıyorduk. Bu sebeple bu pageler diğer tablolar tarafından da kullanılan Mixed Extent bölgesinde tutulmaktaydı. Daha sonra tablomuza 20 kayıt daha ekleyerek tablomuzdaki page sayısının 24’den daha fazla olmasını sağladık. Böylece bir tablo için kullanılan page sayısı 24’ü geçtiği için SQL Server bu indeks tarafından kullanılan pageleri artık Mixed Extent’te değil bu indekse özel yani sadece bu indeksin verilerinin tutulduğu Uniform bir Extent’e taşımıştır.

Yukarıdaki açıklamamıza istinaden SQL Server’ın bir indeks üzerindeki fragmantasyon oranını doğru hesaplayabilmesi için, ilgili indeksin en az üç tane Extent içermesi gerekiyor. Bu sebeple konu başında da bahsettiğimiz gibi rutin indeks bakımlarında bir indeksin Rebuild veya Reorganize edilmesi için en az 3 Extent yani diğer bir ifadeyle en az 24 page içerdiğinden emin olmamız daha kullanışlı olacaktır.

Hakkında ismailadar

Cevapla

E-posta adresiniz yayınlanmayacak. Required fields are marked *

*


*

Şu HTML etiketlerini ve özelliklerini kullanabilirsiniz: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>