MSSQL Server Index Kullanım Durumu

Bu makale ile indexs’lerin fragmentation oranını ne kadar scan-seek yaptığını tüm bilgilere ulaşabiliriz. Bu komut ile index yapılarımızın seek-scan işlemlerini anlık takipte edebiliriz.

İndexs’te page count bize 1000 üzerinde indexs’in kullanılıp kullanılmadığını göstermektedir. İlgili veritabanı ilgili şema adı ve tablo adı şeklinde yapmak lazım. Burada önemli olan bizim için seek ve scan işlemlerinin sayısı yeni bir indexs mi oluşturmak istiyoruz veya mevcut bir indexs in nasıl kullanıldığınımı görmek istiyoruz bu komutu kullanarak takip’i düzenli bir şekilde yapabiliriz. Execution planında maliyetli kısma baktıktan sonra bu komutla tablomuzu anlık olarak takip edebiliriz. Bu sorguda indexs le ilgili tüm bilgiler fragmentasyon oranı fill factor değeri vb. birden fazla bilgileri görebiliriz.

1-Aşağıdaki komut veritabanı bazlı çalışmaktadır. Gerçek sistemde en çok kullandığımız sorgudur.

select
 db_name(ps.database_id) as [database name]
,object_name(ps.object_id) as [object name]
,i.name as [index name]
,ps.index_id
,ps.index_type_desc
,ps.alloc_unit_type_desc
,i.fill_factor
,ps.avg_fragmentation_in_percent --0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.
,ISNULL(ps.fragment_count,'') fragment_count --NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.
 ,ps.page_count
,ps.page_count*8*ps.avg_page_space_used_in_percent/100 avg_size_kb
,ps.record_count --NULL when mode = LIMITED.
 ,ISNULL(us.user_seeks,'') user_seeks
,ISNULL(us.user_scans,'') user_scans
,ISNULL(us.user_lookups,'') user_lookups
,ISNULL(us.user_updates,'') user_updates
from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('SchemeName.Table'), null, null ,'LIMITED') as ps
 left outer join sys.dm_db_index_usage_stats as us with (nolock) on us.database_id=ps.database_id and us.object_id=ps.object_id and us.index_id=ps.index_id
left outer join sys.indexes as i with (nolock) on ps.[object_id] = i.[object_id] and ps.index_id = i.index_id
where ps.database_id = db_id() and ps.page_count > 1000
and object_name(ps.object_id) ='Table'
--order by ps.avg_fragmentation_in_percent desc option (recompile);

Yorum satırındaki order by ifadesi aktif edilirse Fragmentation değerlerini büyükten küçüğe doğru sıralamaktadır.

Yukarıdaki dönen sonuçta ilk 3. satırın alloc_init_type_Desc kısmına bakıldığında birden fazla değer olduğu görülür. Tablo altında index bölüme baktığımızda 1. satır hariç diğer satırlar görünmez. Şimdi 2. ve 3. satırda dönen ifadelerin ne olduğunu anlayalım.

LOB veri yapıları, SQL Server’da gerçek bir index değil, sistemsel veri yapısıdır. Bu yüzden:

SSMS’de (Management Studio) “Indexes” altında görünmez.
Ama sistem kataloglarını (sys.partitions, sys.allocation_units, sys.indexes) sorguladığınızda görünür çünkü SQL Server fiziksel veri yönetimi düzeyinde bu alanları gerçekten kullanır.

SQL Server bir tabloyu depolarken 3 temel allocation unit (tahsis birimi) kullanabilir:

Allocation Unit	Açıklama
IN_ROW_DATA	Normal 8 KB’lık satır içi veri
LOB_DATA	Satır dışı, büyük veri (MAX, TEXT vb.)
ROW_OVERFLOW_DATA	Satırda 8 KB limiti aşılırsa kalan veri

Tabloda başka index olmasa bile:

VARCHAR(MAX), NVARCHAR(MAX) gibi LOB veri tipleri varsa,
SQL Server bu veriler için LOB_DATA allocation unit oluşturur.
Bu görünmeyen ama sistem tarafından kullanılan bir yapıdır.
Bu yüzden sys.allocation_units gibi sorgularda extra bir yapı olarak görünür.

2-Aşağıdaki bir başka komut ise veritabanı altında bulunan tabloların ve tabloya bağlı indexs’lerin ne kadar kullanıldığını kullanılan ifadelerin en son ne zaman olduğunu aşağıdaki script ile görebiliriz. Veritabanı altında bulunan tüm tablolar için sonuç döndürmektedir.

SELECT 
    DB_NAME() AS [DATABASE NAME],  -- Veritabanı Adı
    SCHEMA_NAME(T.schema_id) AS [SCHEMA NAME], -- Şema Adı
    OBJECT_NAME(S.object_id) AS [TABLE NAME], -- Tablo Adı
    I.[NAME] AS [INDEX NAME], -- İndeks Adı
    S.USER_SEEKS, 
    S.LAST_USER_SEEK,  -- En son SEEK işlemi
    S.USER_SCANS, 
    S.LAST_USER_SCAN,  -- En son SCAN işlemi
    S.USER_LOOKUPS, 
    S.LAST_USER_LOOKUP,  -- En son LOOKUP işlemi
    S.USER_UPDATES, 
    S.LAST_USER_UPDATE  -- En son UPDATE işlemi
FROM sys.dm_db_index_usage_stats AS S
INNER JOIN sys.indexes AS I 
    ON I.object_id = S.object_id
    AND I.index_id = S.index_id
INNER JOIN sys.tables AS T
    ON T.object_id = S.object_id
WHERE OBJECTPROPERTY(S.object_id, 'IsUserTable') = 1
AND S.database_id = DB_ID();
--AND OBJECT_NAME(S.object_id)='TABLO_ADI'

Sadece tablo adı yazılması isteniyorsa yorum satırına yazılabilir.

Not: Sql servisinin kapanması yukarıdaki değerlerin sıfırlanmasına sebep olur.

Eğer Tablo adı belirtmeniz gerekirse aşağıdaki komut kullanılır.

DECLARE @TableName NVARCHAR(128) = 'YourTableName'; -- Buraya tablo adınızı yazın

SELECT 
    DB_NAME() AS [DATABASE NAME],  -- Veritabanı Adı
    SCHEMA_NAME(T.schema_id) AS [SCHEMA NAME], -- Şema Adı
    OBJECT_NAME(S.object_id) AS [TABLE NAME], -- Tablo Adı
    I.[NAME] AS [INDEX NAME], -- İndeks Adı
    S.USER_SEEKS, 
    S.LAST_USER_SEEK,  -- En son SEEK işlemi
    S.USER_SCANS, 
    S.LAST_USER_SCAN,  -- En son SCAN işlemi
    S.USER_LOOKUPS, 
    S.LAST_USER_LOOKUP,  -- En son LOOKUP işlemi
    S.USER_UPDATES, 
    S.LAST_USER_UPDATE  -- En son UPDATE işlemi
FROM sys.dm_db_index_usage_stats AS S
INNER JOIN sys.indexes AS I 
    ON I.object_id = S.object_id
	AND I.index_id = S.index_id
INNER JOIN sys.tables AS T
    ON T.object_id = S.object_id
WHERE OBJECTPROPERTY(S.object_id   , 'IsUserTable') = 1
AND S.database_id = DB_ID()
AND T.[NAME] = @TableName;  -- Belirtilen tabloya göre filtreleme

Yukarıdaki komutların hepsi sql server’ın en son restart edildikten sonraki durumunu göstermektedir.

USER_LOOKUPS değeri, bir Clustered Index Seek yerine, Non-Clustered Index kullanıldıktan sonra veri satırına ulaşmak için yapılan Key Lookup işlemlerini gösterir. Non-clustered bir indeks kullanıldığında ve sorgunun döndürmesi gereken sütunlardan bazıları bu indeksin içinde yer almıyorsa, SQL Server Key Lookup (RID Lookup) yaparak eksik sütunları almak için tekrar Clustered Index’e bakar. Bu lookup işlemi gerçekleştiğinde LASTUSERLOOKUP güncellenir.

USER_UPDATES değeri, indekste değişiklik yapan UPDATE, INSERT veya DELETE işlemlerinin sayısını gösterir. LASTUSERUPDATE, bu işlemlerden birinin en son ne zaman yapıldığını gösterir. Eğer tabloya bir INSERT, UPDATE veya DELETE işlemi yapılırsa ve bu değişiklik indekslere de yansıyorsa, ilgili indeks güncellenir ve LASTUSERUPDATE güncellenir. Sadece ilgili indeksi etkileyen işlemler sayılır. Eğer bir indeks hiç değişikliğe uğramamışsa, LASTUSERUPDATE NULL dönebilir.

Bu makalede Mssql Server yapımızda indexslerin ne kadar kullanıldığı durumu görmüş olduk. Başka bir makalede görüşmek dileğiyle.

Onlar ki, Allah anıldığı zaman kalpleri ürperir; kendilerine isabet eden musibetlere sabredenler, namazı dosdoğru kılanlar ve rızık olarak verdiklerimizden infak edenlerdir. Hac Suresi, 35. Ayet

Bir yanıt yazın