SQL Server Index Fragmentation ve Page Splits

Bilindiği gibi SQL Server’in dosya tabanında Page denen 8KB ’lık alanlar mevcuttur ve bunlardan 8 tanesi (8 X 8K=64K) bir extent oluşturur. Data ve Index verileri page ’lerde tutulur ve Read / Write işlemleri extent bazında gerçekleşir. Her page ’de kaç satırın bulunacağı satırın uzunluğuna bağlı olarak değişebilir. Eğer uzun bir row gelecek olursa veya mevcut row çok uzun bir veriyle güncellenecek olursa SQL Server değişikliği yapmak için mevcut satırların yerini değiştirebilir eğer değişiklik sayfaya sığmıyorsa SQL Server yeni bir sayfa (data page) oluşturup satırları buraya taşır. Bu şekilde öndeki veya sonraki sayfada öteleme işlemleri yapmak zorunda kalır. Bu işleme page split denilir.

Page Split işlemi canlı sistemlerde bazen performans sorununa neden olabilmektedir. 

Peki Page Split i Perfmon üzerinde nasıl monitör ederiz ? İşte bu tarz performans problemlerinde Perfmon aracını kullanarak Page Split leri yakalayabiliriz.

Örnek tablomuzu oluşturalım.

USE PSplit_Demo
GO
IF object_id('PageSplit_Table') IS NOT NULL
BEGIN
	DROP TABLE PageSplit_Table
END
GO
CREATE TABLE PageSplit_Table (col_1 INT,col_2 char(3950))
GO
CREATE UNIQUE CLUSTERED INDEX CIX_PageSplit_Table_col_1 ON PageSplit_Table(col_1)

Şimdi ise Perfmon u açalım. Sonra + (plus) işaretine basıyoruz ve SQL Server:Access Methods içerisinde Page Splits/sec i ekliyoruz monitörümüze.

Daha sonra aşağıda ki INSERT bloğumuzu çalıştırıyoruz.

USE PSplit_Demo
GO
SET NOCOUNT ON
GO
DECLARE @i INT
SET @i=0
WHILE (@i<5000)
BEGIN
	INSERT INTO  PageSplit_Table VALUES (@i,'___')
	INSERT INTO  PageSplit_Table VALUES ([email protected],'___')
	SET @[email protected]+1
END
GO
SET NOCOUNT OFF
GO

Insert işlemini yaparken monitörü açalım.

Page Split/sec in fırladığını Perfmon üzerinde yakalıyoruz bu şekilde.

Şimdi ise Index üzerinde Fragmntasyonu gözlemleyelim. Peki Fragmantasyon nedir ?

İndex ’imizi oluşturduk. Ve bir süre boyunca tabloda sürekli update, delete ve insert işlemlerini gerçekleştirdik. Delete ettiğimizi düşünelim. Index ‘in bir page ’inden biraz veri silmiş olduk. Sonra başka bir kayıt insert etmek istedik. Ve bu kayıt sildiğimiz page ’de yeterli yer olmadığı için sql server gitti bu kaydı yeni bir boş page’e yazdı. Bu şekilde index ’ler zamanla diskte dağınık halde tutulmaya başlar ve fragmentation arttıkça index ’in performansı azalır. Yoğun Update,Insert ve Delete içeren tablolar da index ’lerin çabuk fragmante olmalarını engellemek için index’e fill factor koyabilirsiniz.

Fragmantasyonu öğreneceğimiz scriptimiz aşağıda ,

USE PSplit_Demo
GO
 
SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,record_count AS table_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

Göründüğü üzere CIX_PageSplit_Table_col_1 isimli index in %99,98 fragmantasyonu mevcut ve bu demek oluyor ki bizim index imiz dağılmış duurmda şu anda. Yani Index kendi performansını çok fazla düşürmüş durumda. Peki bu ındex ‘in toparlaması için ne yapmalıyız sorusundan önce bu tarz dağılmış index ile işlem yapılan query lerde ki istatistiklerimiz ile aynı işlemi dağılmamış index ler üzerinde yapıp arada ki farkıda gözlemleyelim.

USE PSplit_Demo
GO

SET STATISTICS IO ON
GO

SELECT * FROM PageSplit_Table

SET STATISTICS IO OFF
GO

Burada istatistik sonuclarını alıyoruz ;,

(10000 rows affected)
Table 'PageSplit_Table'. Scan count 1, logical reads 10041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Şimdi ise aynı tablodan fakat Index i fragmentasyona uğramayan şekilde bir demo yapı hazırlayalım.

USE PSplit_Demo
GO

IF object_id('No_PageSplit_Table') IS NOT NULL

BEGIN
	DROP TABLE No_PageSplit_Table
END
GO

CREATE TABLE No_PageSplit_Table (col_1 INT,col_2 char(3950))
GO

CREATE UNIQUE CLUSTERED INDEX CIX_PageSplit_Table_col_1 ON No_PageSplit_Table(col_1)
GO

USE PSplit_Demo
GO

SET NOCOUNT ON
GO

DECLARE @i INT
SET @i=0
WHILE (@i<10000)
BEGIN
	INSERT INTO  No_PageSplit_Table VALUES (@i,'___')
	SET @[email protected]+1
END
GO

SET NOCOUNT OFF
GO

İkinci demo tablomuzu ve aynı sayıda data mızı aynı şekilde bastık ve şimdi bu iki tabloda ki Index lerin Fragmentasyon oranlarını çekelim.

USE PSplit_Demo
GO
 
SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,record_count AS table_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

Gördüğünüz üzere ilk index ‘imiz %99,98 fragmante olmuşken ikinci index imiz %0,44 fragmantasyona uğramış durumda. Şimdi ise her iki tabloda aynı SELECT işlemini çalıştırıp arada ki perfromans farkını gözlemleyelim.

USE PSplit_Demo
GO

SET STATISTICS IO ON
GO

PRINT 'Page Split'
SELECT * FROM PageSplit_Table WHERE col_2='a'

PRINT 'No Page Split'
SELECT * FROM No_PageSplit_Table WHERE col_2='a'


SET STATISTICS IO OFF
GO

*** İstatistik Verileri :

Page Split

(0 rows affected)
Table 'PageSplit_Table'. Scan count 1, logical reads 10041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
No Page Split

(0 rows affected)
Table 'No_PageSplit_Table'. Scan count 1, logical reads 5024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Gördüğünüz üzere Page Split ‘e uğramış Index 10041 logical Read yaparken, Page Split ‘e uğramamış index de ki logical Read değeri ise 5024 yani neredeyse 2 katı daha performanslı dağılmamış Index. Bu yüzden sistemlerinizde mutlaka Index lerinizin Fragmantasyon değerlerini an ve an kontrol edip Page Split leri takipte kalın.

Peki yukarıda sorduğumuz ama henüz cevabını vermediğimiz sorumuza geldi sıra. Fragmante olmuş index lerimizi nasıl toparlarız.

Bu tarz durumlarda Index ‘lerin Fragmantasyon değerlerine göre Reorganize veya Rebuild işlemleri gerçekleştirmeliyiz. Bizim Page Split e uğramış Index ‘imizin Fragmantasyonu çok yüksek olduğu için REBUILD işlemi uygulayacağız.

ALTER INDEX CIX_PageSplit_Table_col_1 ON PageSplit_Table REBUILD

REBUIL operasyonundan sonra Index ‘imizin tekrar Fragmantasyon değerine bakalım.

USE PSplit_Demo
GO
 
SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,record_count AS table_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

Gördüğünüz üzere Index imiz toparlamış durumda. Bu tarz durumlarda Index bakımlarını periyodik olarak uygulamamız gerekmekte.

Şimdi tekrar her iki tabloda da SELECT scriptimizi çalıştırıp istatistikleri kontrol edelim.

USE PSplit_Demo
GO

SET STATISTICS IO ON
GO

PRINT 'Page Split'
SELECT * FROM PageSplit_Table WHERE col_2='a'

PRINT 'No Page Split'
SELECT * FROM No_PageSplit_Table WHERE col_2='a'


SET STATISTICS IO OFF
GO

*** İstatistik Verileri :

Page Split

(0 rows affected)
Table 'PageSplit_Table'. Scan count 1, logical reads 5020, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
No Page Split

(0 rows affected)
Table 'No_PageSplit_Table'. Scan count 1, logical reads 5024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Görüldüğü üzere değerler aynı hatta dağılmış Index ‘imizin performansı bakım sonrası diğerinden dah da iyi duruma geldi.

Kolay Gelsin.

Leave a Reply

Your email address will not be published. Required fields are marked *