Bu makalede MSSQL Server Eksik Index’leri Tespit Etmek konusunu detaylı bir şekilde görmüş olacağız.
Missing index olan bir sorguda execution planda bu ifade görülmüyorsa sebebi ilgili planın implicit veya explicit conversion yapmasından dolayıdır. Bu ifade kaldırılırsa sorgularımız missing index uyarısı vermektedir.
1-Aşağıdaki verdiğiniz SQL sorgusu, SQL Server’daki eksik indeksleri belirlemek ve bu eksik indekslerin sorgu performansını nasıl etkileyebileceği hakkında bilgi almak için kullanılır. Veritabanı bazlı çalışmaktadır.
Sorgu, mevcut veritabanında eksik indekslerin hangi sorgularda, hangi sütunlarda eksik olduğunu ve bu eksik indekslerin kullanıcı sorguları üzerindeki olası etkisini gösterir. Sonuçlar, eksik indekslerin sorgu performansını nasıl iyileştirebileceğini anlamak için kullanılabilir.
SELECT
TOP 100
mid.statement,
equality_columns,
inequality_columns,
included_columns,
user_seeks,
user_scans,
avg_total_user_cost,
avg_user_impact,
sys_info.sqlserver_start_time AS last_reset_time,
'CREATE INDEX IX_' +
REPLACE(REPLACE(mid.statement, '[', ''), ']', '') + '_' +
REPLACE(equality_columns, ',', '') +
CASE WHEN inequality_columns IS NOT NULL THEN '' + REPLACE(inequality_columns, ',', '') ELSE '' END +
' ON ' + mid.statement +
' (' + equality_columns +
CASE WHEN inequality_columns IS NOT NULL THEN ', ' + inequality_columns ELSE '' END + ')' +
CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' + included_columns + ')' ELSE '' END +
' WITH (MAXDOP = 8, ONLINE = ON)' -- MAXDOP ve ONLINE parametreleri eklendi
AS CreateIndexScript
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mid.index_handle = mig.index_handle
CROSS JOIN sys.dm_os_sys_info sys_info -- SQL Server’ın en son başlatılma zamanını almak için
WHERE database_id = DB_ID()
ORDER BY (user_seeks + user_scans) * avg_total_user_cost * avg_user_impact DESC;
Yukarıdaki bazı kolonların açıklaması:
- statement: Eksik indeksin olduğu tablo ve şema adı.
- equality_columns: Eksik indekste eşitlik koşulunda (WHERE koşulunda) kullanılacak olan sütunlar. Where = ifadesindeki kolon diyebiliriz.
- inequality_columns: Eksik indekste eşitsizlik koşulunda (>, <, >=, <=) kullanılacak olan sütunlar.
- included_columns: Eksik indekste anahtar (key) olarak yer almayan ancak dahil edilmesi gereken ek sütunlar.
- user_seeks: Eksik indeksin kullanıcılar tarafından kaç kez seek (arama) işlemiyle kullanıldığı.
- user_scans: Eksik indeksin kullanıcılar tarafından kaç kez scan (tarama) işlemiyle kullanıldığı.
- avg_total_user_cost: Eksik indeksin, bu indeksin eksik olduğu sorgularda ortalama toplam maliyeti.
- avg_user_impact: Eksik indeksin, sorgulara olan ortalama etkisi.
- last_reset_time: Makinanın en son ne zaman restart aldığını görebiliriz.
Yukarıdaki komutu tüm instance bazlı çalıştırmak için aşağıdaki komut kullanılmaktadır.
EXEC sp_MSforeachdb '
USE [?];
IF DB_ID(''?'') > 4 AND DATABASEPROPERTYEX(''?'', ''Status'') = ''ONLINE''
BEGIN
SELECT
TOP 100
mid.statement,
equality_columns,
inequality_columns,
included_columns,
user_seeks,
user_scans,
avg_total_user_cost,
avg_user_impact,
sys_info.sqlserver_start_time AS last_reset_time,
''CREATE INDEX IX_'' +
REPLACE(REPLACE(mid.statement, ''['', ''''), ''"'', '''') + ''_'' +
REPLACE(REPLACE(REPLACE(equality_columns, '', '', ''''), ''['', ''''), ''"'', '''') +
CASE WHEN inequality_columns IS NOT NULL THEN REPLACE(REPLACE(REPLACE(inequality_columns, '', '', ''''), ''['', ''''), ''"'', '''') ELSE '''' END +
'' ON '' + mid.statement +
'' ('' + equality_columns +
CASE WHEN inequality_columns IS NOT NULL THEN '', '' + inequality_columns ELSE '''' END + '')'' +
CASE WHEN included_columns IS NOT NULL THEN '' INCLUDE ('' + included_columns + '')'' ELSE '''' END +
'' WITH (MAXDOP = 8, ONLINE = ON)'' -- MAXDOP ve ONLINE parametreleri eklendi
AS CreateIndexScript
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mid.index_handle = mig.index_handle
CROSS JOIN sys.dm_os_sys_info sys_info
WHERE database_id = DB_ID()
ORDER BY (user_seeks + user_scans) * avg_total_user_cost * avg_user_impact DESC;
END
';2-Aşağıdaki komut tüm veritabanları için geçerli olacaktır. Bu kod bloğunda tüm veritabanlarımızdaki eksik indexs’i bize veriyor biz sadece bu kısmı yorumlayıp ona göre aksiyon almamız lazım yoksa her indexs’i oluşturmaya çalışırsak performans olarak sıkıntı yaşarız. Yukarıdaki tablo açıklamaları aşağıdaki kod bloğunda yer almaktadır.
SELECT [EstIndexUses]
,[EstIndexImpact%]
,[EstAvgQueryCost]
,[DbName]
,[SchemaName]
,[TableName]
,[CreateIndex]
,[EqualityColumns]
,[InequalityColumns]
,[IncludedColumns]
,[UniqueCompiles]
,[LastUserSeek]
FROM (
SELECT migs.user_seeks AS [EstIndexUses]
,migs.avg_user_impact AS [EstIndexImpact%]
,migs.avg_total_user_cost AS [EstAvgQueryCost]
,db_name(mid.database_id) AS [DbName]
,OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS [SchemaName]
,OBJECT_NAME(mid.object_id, mid.database_id) AS [TableName]
,'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE
WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN '_'
ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE
WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ') WITH (MAXDOP =?, FILLFACTOR=?, ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [CreateIndex]
,mid.equality_columns AS EqualityColumns
,mid.inequality_columns AS InequalityColumns
,mid.included_columns AS IncludedColumns
,migs.unique_compiles AS UniqueCompiles
,migs.last_user_seek AS LastUserSeek
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
) AS a
WHERE 1 = 1
--AND [EstIndexUses] > 1000
--AND [EstIndexImpact%] > 10
--AND [EstAvgQueryCost] > 1
--AND DbName IN ('DatabaseName')
ORDER BY [EstIndexUses] DESC
,[EstAvgQueryCost] DESC
,[EstIndexImpact%] DESC
OPTION (RECOMPILE);
Sütun Açıklamaları:
EstIndexUses Bu eksik index’e karşılık gelen sorguların kaç kez çalıştığı tahmini. (user_seeks)
EstIndexImpact% Bu index’in sorgu maliyetini ne kadar azaltabileceği (0–100 arası yüzdelik değer). Yüksekse önemli bir index. (avg_user_impact)
EstAvgQueryCost İlgili sorguların ortalama maliyeti (CPU, IO gibi). (avg_total_user_cost)
DbName Index’in önerildiği veritabanı adı
SchemaName Tabloya ait şema adı
TableName Index’in önerildiği tablo adı
CreateIndex SQL Server’ın önerdiği index’i oluşturmak için kullanılabilecek otomatik oluşturulmuş CREATE INDEX komutu
EqualityColumns Sorgularda = ile kullanılan kolonlar. Bu kolonlar index’in arama (seek) kısmını oluşturur.
InequalityColumns Sorgularda <, >, BETWEEN gibi operatörlerle kullanılan kolonlar.
IncludedColumns SELECT deyiminde geçen ama filtreleme ya da sıralamada kullanılmayan kolonlar. Sadece performans için INCLUDE kısmına eklenir.
UniqueCompiles Bu index önerisinin kaç farklı execution plan’da kullanıldığını gösterir.
LastUserSeek Bu önerinin son kez ne zaman tetiklendiğini gösterir. Eğer yakınsa hâlâ geçerli olabilir.
SSMS arayüzünde missing indexleri yukarıdaki kod bloğu gibi arayüz ekranından görebiliriz. Bunun için ilgili instance üzerine sağ tıklayıp report bölümünden görebiliriz. Performance Dashboard bölümünden detaylı bir şekilde görülebilir. Burada ilgili bölümlere tıklayarak detaylı bilgiler elde edilebilir.
Aşağıdaki komut cpu’yu en çok yoran eksik index önerilerini vermektedir.
SELECT
qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
q.[text] AS query_text,
p.query_plan,
qs_cpu.execution_count,
DB_NAME(q.dbid) AS database_name
FROM
(SELECT TOP 500 qs.plan_handle, qs.total_worker_time, qs.execution_count
FROM sys.dm_exec_query_stats qs
ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(qs_cpu.plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(qs_cpu.plan_handle) p
WHERE p.query_plan.exist('declare namespace qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:MissingIndexes')=1 -- Bu satır, sadece eksik indeks önerisi olan planları filtreler
ORDER BY total_cpu_time_ms DESC;Bu makalede mssql server’da eksik indexs’ler nasıl tespit edilir görmüş olduk. Başka bir makalede görüşmek dileğiyle.
“Yavrucuğum, namazını özenle kıl, iyi olanı emret, kötü olana karşı koy, başına gelene sabret. İşte bunlar, kararlılık gerektiren işlerdendir.”Lokman-17
