Bu makalede index fragmentation yapısına değinmiş olacağız. Öncelikle index yapıları neden fragmentation olur. Kullanıcılar tabloya insert update ve delete işlemleri sonucu index yapımızın olduğu ilgili page içerisinde alan kalmayabilir. Bunun için sql server page yapımıza sığmayan yeni değerler farklı bir page üzerinde durmaktadır. Buda verilerimizin dağınık bir şekilde durmasına ve index yapımızın bozulmasına sebebiyet verecektir. İlgili makaledeki script ile kullanım durumlarını aynı zamanda fragmantation oranlarını bulabiliriz.
Aşağıdaki sorgu veritabanı altında bulunan belirtilen tablo ve tablo altındaki index fragmentation oranlarını artandan azalan yapıya doğru vermektedir. Aynı index page count değerlerini vermektedir.
SELECT
dbschemas.[name] as 'SchemaName',
dbtables.[name] as 'TableName',
dbindexes.[name] as 'IndexName',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS indexstats
INNER JOIN sys.tables dbtables on dbtables.object_id = indexstats.object_id
INNER JOIN sys.schemas dbschemas on dbtables.schema_id = dbschemas.schema_id
INNER JOIN sys.indexes AS dbindexes ON dbindexes.object_id = indexstats.object_id
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and --dbtables.[name]='Address' and
indexstats.avg_fragmentation_in_percent >=0
ORDER BY indexstats.avg_fragmentation_in_percent desc
GO
dbtables.[name]=’Address’ kısmı yorum satırı olarak değiştirilirse veritabanı altında bulunan tüm tablolar için sonuç dönmektedir. Sadece tablo ismi belirtilmesi yeterli aynı tablo ismindeki tüm schema’larımız dönmektedir.
Aşağıdaki sorgu veritabanı altında bulunan tüm tablo ve tablo altındaki index fragmentation oranlarını artandan azalan yapıya doğru vermektedir. Aynı zamanda index page count sayısını vermektedir.
select dbschemas.name as SemaAdi,t.name AS TabloAdi, i.name AS IndexAdi,avg_fragmentation_in_percent,page_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) ips
join sys.tables t on ips.object_id=t.object_id
INNER JOIN sys.schemas dbschemas on t.schema_id = dbschemas.schema_id
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
Aşağıdaki sorgu yardımıyla veritabanı altında bulunan tablolardaki tüm index’lerimizin bozulma oranlarını bulmuş olacağız. Aynı zamanda indexslerin türünüde görmüş olacağız. exec sp_MSforeachdb procedured yapısını kullanarak tüm instance altında veritabanları için yapılabilir.
use [AdventureWorks2016]
SELECT S.name as 'Schema',
T.name as 'Table',
indexstats.avg_fragmentation_in_percent,
ind.type_desc,
ind.name as Index_Nmae,
indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sysobjects so ON so.id=ind.object_id
INNER JOIN sysusers su on su.uid=so.uid
INNER JOIN sys.tables T on T.object_id = indexstats.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
WHERE indexstats.avg_fragmentation_in_percent > 5
AND ind.name is not null --HEAP'lerde index olmuyor!...
ORDER BY indexstats.avg_fragmentation_in_percent DESCTüm veritabanların da kontrol işlemi yapmak için yukarıdaki kod bloğunda aşağıdaki gibi değişikliklerin yapılması gerekmektedir.
exec sp_MSforeachdb'
SELECT ''?'' as databasename,S.name as ''Schema'',
T.name as ''Table'',.........'
Sadece non-clustered indekslerle sınırlamak isterseniz, IX.type_desc = 'NONCLUSTERED' filtresini ekleyebilirsiniz.
use [AdventureWorks2016]
SELECT S.name as 'Schema',
T.name as 'Table',
indexstats.avg_fragmentation_in_percent,
ind.type_desc,
ind.name as Index_Nmae,
indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sysobjects so ON so.id=ind.object_id
INNER JOIN sysusers su on su.uid=so.uid
INNER JOIN sys.tables T on T.object_id = indexstats.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
WHERE indexstats.avg_fragmentation_in_percent > 5
AND
ind.type_desc='NONCLUSTERED'
AND ind.name is not null --HEAP'lerde index olmuyor!...
ORDER BY indexstats.avg_fragmentation_in_percent DESC
Aşağıdaki komut yukarıda paylaşılmış olduğum tüm komutları kapsamaktadır. Bize ilgili indexslerin rebuild komutlarını vermektedir.
use [AdventureWorks2016]
SELECT S.name as 'Schema',
T.name as 'Table',
indexstats.avg_fragmentation_in_percent,
ind.type_desc,
ind.name as Index_Nmae,
indexstats.page_count,
'ALTER INDEX '+ind.name+ ' ON ['+ OBJECT_SCHEMA_NAME(ind.object_id)+'].'+ OBJECT_NAME(ind.object_id)
+'REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)' as Rebuild_Komut
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sysobjects so ON so.id=ind.object_id
INNER JOIN sysusers su on su.uid=so.uid
INNER JOIN sys.tables T on T.object_id = indexstats.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
WHERE indexstats.avg_fragmentation_in_percent > 5
AND ind.name is not null --HEAP'lerde index olmuyor!...
ORDER BY indexstats.avg_fragmentation_in_percent DESCREBUILD PARTITION = ALL ifadesi, tüm bölümlerin yeniden oluşturulmasını sağlar. Yani, index’in veya tablonun tüm bölümleri yeniden yapılandırılır ve optimize edilir. Bu, genellikle büyük ve partitioned tablolarla çalışan veritabanlarında, her bölümdeki fragmantasyonu gidermek ve performansı artırmak için kullanılır.
Bu makalede kısaca Indexs’lerin fragmentation ne olduğunu ve fragmentation oranlarını bulma scriptlerini görmüş olduk. Başka bir makalede görüşmek dileğiyle..
“(Mümin) kardeşinle münakaşa etme, onun hoşuna gitmeyecek şakalar yapma ve ona yerine getirmeyeceğin bir söz verme.” (Tirmizî, Birr, 58.)
