SQL Server – Adım Adım Eksik Index’lerin Belirlenip Oluşturulması Operasyonu

Bir önceki makalemizde sistemde olmayan ama olması tavsiye edilen index’leri nasıl sorgulayabileceğimizi incelemiştik. (Bkz. Bir önceki makalemde)
Bugünkü makalemde ise belirlediğimiz bu missing index’leri adım adım analiz edip create edip etmemeye karar vereceğiz. Ve create ettikten sonrada nasıl monitör edebileceğimizi görüyor olacağız. Yani baştan sona bir missing index operasyonunun nasıl yapıldığını görüyor olacağız.
Makale şu alt başlıklar altında yer alacaktır;
1. Missing Index’lerin sorgulanması ve create edilecek index’e karar verilmesi.
2. Create edilmesi düşünülen index’in ve bulunduğu tablonun analizi
a. Tablo kayıt sayısı ve boyut analizi
b. Tablonun üzerinde bulunan diğer index’lerin kullanım istatistikleri
c. Tablonun üzerinde bulunan index’lerin hangi kolonlar üzerinde bulunduklarının kontrolü
d. Tablonun üzerinde bulunan index’lerin Include Column içerip içermediklerinin kontrolü
3. Tablonun query stats’larına bakılıp ilgili missing index’e sebep olan script’in bulunması.
4. Bulunan script’in şu anki IO değerlerinin sorgulanması.
5. Index’in create edilmesi
6. Sorgunun IO değerine tekrar bakılması ve ne kadar düştüğünün incelenmesi.
7. Create edilen index’in kullanım istatistiklerinin monitör edilmesi

1. Missing Index’lerin sorgulanması ve create edilecek index’e karar verilmesi

Missing Index’lerin nasıl sorgulanabileceği ile ilgili detay bilgiye şu makaleden erişebilirsiniz. Bu makalemde detaya girmeden direk missing index sorgusunu çalıştıracağız.
Daha öncesinde bir Missing Index durumu oluşturmak için AdventureWorks te bulunan Person.Address tablosuna City ve PostalCode üzerinden select çekelim. Bu kolonlar üzerinde tanımla olan bir index olmadığı için bir missing index kaydı oluşacaktır.
–Aşağıdaki select’i birden fazla kez çalıştırınız
select * from Person.Address where City=‘Duvall’ and PostalCode=‘98019’

Şimdi AdventureWorks DB’si için Missing Index sorgumuzu çalıştıralım.
select DB_NAME(id.database_id) as databaseName,
id.statement as TableName,
id.equality_columns,
id.inequality_columns,
id.included_columns,
gs.last_user_seek,
gs.user_seeks,
gs.last_user_scan,
gs.user_scans,
gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans)/100 as ImprovementValue
from sys.dm_db_missing_index_group_stats gs
INNER JOIN sys.dm_db_missing_index_groups ig on gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details id on id.index_handle = ig.index_handle
where DB_NAME(id.database_id) = ‘AdventureWorks’
order by avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)/100 desc

Sorgu sonucunda aşağıdaki gibi bir sonuç çıkacaktır.

Gördüğünüz gibi Person.Address tablosu üzerinde City ve PostalCode kolonları için bir missing index önerisi çıktı. Bu index olsaydı 28 kez seek edileceğini ve en son 10.12.2010 14:33 tarihinde seek edildiği bilgilerine de bu sorgu sonucunda erişebiliyoruz.
Dolayısıyla üzerinde çalışacağımız ve create etmek istediğimiz index’i belirledik. Person.Address tablosu için City ve PostalCode kolonları.
Şimdi gerçekten böyle bir index’e ihtiyaç var mı diye detay analizlerimize başlayalım.
2. Create edilmesi düşünülen index’in ve bulunduğu tablonun analizi

Bu başlık altında aşağıdaki detay incelemeleri yapacağız.
· Tablo kayıt ve boyut analizi = Tablonun kayıt sayısına bakarak index’in gerekliliğinin ilk sorgulamasını yapıyoruz. Eğer ufak bir tablo ise index’in create edilmesinin çok gerekli olmadığını düşünebiliriz. Ya da boyutu çok büyükse oluşturacağımız index’in de iyi bir yer kaplayacağını düşünerek create edip etmemeyi tekrar değerlendirebiliriz. Ama ben kendi çalışmalarımda bu adıma sadece kontrol için bakıyorum. Çoğu durumda bu adım index create edip etmeme kararıma çok fazla etki etmiyor.
· Tablonun üzerinde bulunan diğer index’lerin kullanım istatistikleri = Tabloda bulunan diğer index’lerin kullanım istatistiklerine bakıyoruz. Bu kontrol sırasında diğer index’lerin kullanım rakamlarına göre bu yeni oluşturacağımız index’in cluster index ya da non-cluster index olup olmayacağına karar verebiliriz.
· Tablonun üzerinde bulunan index’lerin hangi kolonlar üzerinde bulunduklarının kontrolü = Tablonun hali hazırda sahip olduğu index’lerin hangi kolonlar üzerine tanımlandıklarına bakıyoruz. Belkide bizim oluşturmak istediğimiz index’i başka bir index farklı kolon sıralaması ile cover etmekte. Eğer böyle bir durum var ise index create etmemeyi düşünebiliriz.
· Tablonun üzerinde bulunan index’lerin Include Column içerip içermediklerinin kontrolü = sp_helpindex sistem procedure’ü included column’ları vermemekte. Dolayısıyla son adım olarak bu kontrolü de yapıyoruz.

Yukarıdaki analizlerin tamamı için aşağıdaki sorgu bloğunu kullanabiliriz. Parametre olarak hangi tabloyu analiz etmek istediğimizi belirtiyoruz.
declare @tablename varchar(100)=‘Person.Address’

–1. Tablonun kayıt sayısına ve size ına bakılır.
exec(‘sp_spaceused ”’+@tablename+”’ ‘)

–2. Tablonun diğer index lerinin Index Usage Statistics lerine bakılır.
select OBJECT_SCHEMA_NAME(us.object_id),OBJECT_NAME(us.object_id) as tableName,
i.index_id,
i.name as indexName,
us.last_user_seek,
us.user_seeks,
CASE us.user_seeks WHEN 0 THEN 0
ELSE us.user_seeks*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS SeekPercentage,
us.last_user_scan,
us.user_scans,
CASE us.user_scans WHEN 0 THEN 0
ELSE us.user_scans*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS ScanPercentage,
us.last_user_lookup,
us.user_lookups,
us.last_user_update,
us.user_updates,
CASE us.user_scans + us.user_seeks WHEN 0 THEN 0
ELSE us.user_updates*1.0/(us.user_scans + us.user_seeks)*100.0 END as UpdatesPercentage
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id=us.object_id and i.index_id = us.index_id
WHERE us.database_id = DB_ID() and OBJECT_SCHEMA_NAME(us.object_id)+‘.’+OBJECT_NAME(us.object_id) = @tablename
–3. Diğer index’lerin hangi kolonlar üzerinde olduğuna bakılır.
exec(‘sp_helpindex ”’+@tablename+”’ ‘)

–4. Diğer index lerden included column içeren var mı yok mu diye bakılır.
select * from sys.index_columns where OBJECT_ID(@tablename)=object_id and is_included_column!=0
Sorgu sonucu şu şekilde bir sonuç olacaktır.

3. Tablonun query stats’larına bakılıp ilgili missing index’e sebep olan script’in bulunması
Missing index’in hangi sorgu sonucunda oluştuğunu bilmek istiyorum. Çünkü bizim yaptığımız örneğin tersine reel de ben hangi sorgudan bu index’in missing olarak belirlendiğini bilmiyorum. Bunun için sys.dm_exec_query_stats dmv’sini kullanıyorum.
select
st.

,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN –1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS statement_text,
qs.last_execution_time,
qs.execution_count,
qs.total_logical_reads as total_logical_read,
qs.total_logical_reads/execution_count as avg_logical_read,
qs.total_worker_time/1000000 as total_cpu_time_sn,
qs.total_worker_time/qs.execution_count/1000 as avg_cpu_time_ms,
qp.query_plan,
qs.last_logical_reads,
qs.plan_generation_num
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
where SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN –1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) like ‘%Person.Address%’
Sorgu sonucu aşağıdaki gibi bir sonuç olacaktır.

Gördüğünüz gibi ilk satırda bizi ilgilendiren script’i bulduk. Bu script 28 kez çalıştırılmış ve her çalışmada 236 logical read yapmış.

4.Bulunan script’in şu anki IO değerlerinin sorgulanması

Tanımlayacağımız index’in logical read değerlerinde bir değişiklik yapıp yapmadığını görmek için şu anki yaptığı IO değerine bakmak istiyorum. Bir önceki adımda ilgili script’i bulmuştum. Şimdi bu script’in IO değerlerine bakıyorum.
set statistics IO ON
set statistics profile ON
select * from Person.Address where City=‘Duvall’ and PostalCode=‘98019’

Görüldüğü gibi index creatinden önce bu sorgu 236 logical read yapmakta.
5. Index’in create edilmesi

Bütün analizlerimizi yaptık ve index’i create etmeye karar verdik.
USE [AdventureWorks] GO
CREATE NONCLUSTERED INDEX [IX_Address_City_PostalCode] ON [Person].[Address] ([City],[PostalCode])
6.Sorgunun IO değerine tekrar bakılması ve ne kadar düştüğünün incelenmesi

Index’i create ettikten sonra tekrar IO sorgumuzu çekelim ve yapılan IO da bir iyileştirme olup olmadığını kontrol edelim.
set statistics IO ON
set statistics profile ON
select * from Person.Address where City=‘Duvall’ and PostalCode=‘98019’

Gördüğünüz gibi index create’inden önce 236 olan logical read index create’inden sonra 22’ye düşmüş durumda. Bu durumda 10 kat iyileştirme yaptığımızı düşünebiliriz.
7.Create edilen index’in kullanım istatistiklerinin monitor edilmesi

Missing Index’leri create ettikten sonra periyoduk olarak (ben 1 haftada bir yapıyorum) create ettiğimiz indexlerin usage statistic’lerine bakıp ne kadar seek,scan aldığına, ne kadar update gördüğüne bakmakta fayda var. Bu şekilde create edip kullanılmadığını gördüğümüz index’leri drop etmeyi düşünebiliriz.

select OBJECT_NAME(us.object_id) as tableName,
i.name as indexName,
us.last_user_seek,
us.user_seeks,
CASE us.user_seeks WHEN 0 THEN 0
ELSE us.user_seeks*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS SeekPercentage,
us.last_user_scan,
us.user_scans,
CASE us.user_scans WHEN 0 THEN 0
ELSE us.user_scans*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS ScanPercentage,
us.last_user_lookup,
us.user_lookups,
us.last_user_update,
us.user_updates
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id=us.object_id and i.index_id = us.index_id
WHERE us.database_id = DB_ID(‘AdventureWorks’) and OBJECT_NAME(us.object_id) = ‘Address’

Gördüğünüz gibi create ettiğimiz index çok güzel bir şekilde seek alarak kullanılmakta. Eğer seek+scan değeri update den küçük olsaydı bu index’i drop etmeyi düşünebilirdik.
Adım adım missing index operasyonunu nasıl gerçekleştirdiğimizi incelemeye çalıştık. Index kullanımı büyük projeler için çok önemli olduğundan bu kontrollerin periyodik olarak production ortamları için yapılmasının önemine tekrar vurgu yaparak yazımı noktalamak istiyorum.

Alıntı: Turgay Sahtiyan