MSSQL Server Kullanılmayan Index’leri Tespit Etmek

Çoğu kişi performansı arttırır düşüncesi ile tüm kolunlara index atmak ister. Kullanılmayan indexslerin silinmesinin bazı avantajları vardı. Kullanılmayan indeksler gereksiz disk alanı kaplar. Büyük indeksleri silerek veritabanı boyutunu küçültebilirsiniz. Her INSERT, UPDATE, DELETE işlemi ilgili tablodaki tüm indeksleri güncellemek zorundadır. Kullanılmayan indeksler, gereksiz güncellemeler yaparak bu işlemleri yavaşlatır. Özellikle yüksek yazma (write-heavy) iş yükü olan sistemlerde indeksleri temizlemek büyük performans artışı sağlar.

Bir başka karşılaşacağımız olay servis restart olursa servis tüm veritabanı altında bulunan indexs’leri okur buda sistemin geç ayağa kalkmasına sebebiyet verecektir. Veritabanı yedekleme ve geri yükleme işlemleri daha az disk alanı kaplar ve hızlanır.

Eğer bir indeksi tamamen silmekten emin değilseniz, önce DISABLE ederek test edebilirsiniz.

Aşağıdaki script sonucunda kullanılmayan indexsleri ve boyutlarını veritabanı altında görmekteyiz. Veritabanı bazlı çalışan bir yapı olarak karşımıza çıkmaktadır. Aşağıdaki script sistem en son restart olduktan sonra değerleri almaktadır.

use DB_NAME
SELECT 
    'DROP INDEX ' + 
    ISNULL(OBJECT_SCHEMA_NAME(i.object_id), 'dbo') + '.' + 
    ISNULL(OBJECT_NAME(i.object_id), 'UNKNOWN_TABLE') + '.' + 
    ISNULL(i.name, 'UNKNOWN_INDEX') AS Drop_Index_Command,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    p.reserved_page_count * 8 / 1024 AS IndexSizeMB, -- MB olarak indeks boyutu
    DB_NAME() AS DatabaseName
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s 
    ON i.object_id = s.object_id AND i.index_id = s.index_id
LEFT JOIN sys.dm_db_partition_stats p 
    ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE 
    i.is_primary_key = 0  -- Birincil anahtarları hariç tut
    AND i.is_unique = 0    -- Benzersiz indeksleri hariç tut
    AND i.object_id > 100  -- Sistem tablolarını hariç tut
    AND ISNULL(s.user_updates, 0) <> 0  -- Eğer NULL ise 0 kabul et
    AND ISNULL(s.user_seeks, 0) = 0
    AND ISNULL(s.user_scans, 0) = 0
    AND ISNULL(s.user_lookups, 0) = 0
    AND OBJECT_SCHEMA_NAME(i.object_id) NOT LIKE 'sys%'  -- Sistem şemalarını hariç tut
ORDER BY IndexSizeMB DESC;

Aşağıdaki komut ile tüm instance altında bulunan veritabanlarında kullanılmayan indexsleri ve boyutlarını bulmaktadır. Aşağıdaki komut aynı zamanda veritabanı altında bulunan sistem tablosu veya geçici bir tabloya ait indexs yapılarını almamaktadır.

EXEC sp_MSforeachdb '
USE [?];

SELECT 
    ''USE [?]; DROP INDEX '' + 
    ISNULL(OBJECT_SCHEMA_NAME(i.object_id), ''dbo'') + ''.'' + 
    ISNULL(OBJECT_NAME(i.object_id), ''UNKNOWN_TABLE'') + ''.'' + 
    ISNULL(i.name, ''UNKNOWN_INDEX'') AS Drop_Index_Command,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    p.reserved_page_count * 8/1024 AS IndexSizeMB, -- Sayfa sayısını 8 KB ile çarpıyoruz
    DB_NAME() AS DatabaseName
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s 
    ON i.object_id = s.object_id AND i.index_id = s.index_id
LEFT JOIN sys.dm_db_partition_stats p 
    ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE 
    i.is_primary_key = 0  -- Birincil anahtarları hariç tut
    AND i.is_unique = 0    -- Benzersiz indeksleri hariç tut
    AND i.object_id > 100  -- Sistem tablolarını hariç tut
    AND ISNULL(s.user_updates, 0) <> 0  -- Eğer NULL ise 0 kabul et
    AND ISNULL(s.user_seeks, 0) = 0
    AND ISNULL(s.user_scans, 0) = 0
    AND ISNULL(s.user_lookups, 0) = 0
    AND OBJECT_SCHEMA_NAME(i.object_id) NOT LIKE ''sys%''  -- Sistem şemalarını hariç tut
ORDER BY IndexSizeMB DESC;
';

Komut:

ALTER INDEX INDEX_NAME ON TABLO_NAME DISABLE

Genellikle Indexs silme önerilmediği için kullanılmayan indexslerin disable edilmesi daha faydalıdır. Çünkü herhangi bir geri dönüş olursa size bu hemen hızlı bir şekilde indexsi aktif etmeniz demek olacaktır.

Aşağıdaki komut instance altında bulunan tüm veritabanlarının kullanılmayan indexslerin disable edildiği kodlar görülmektedir.

EXEC sp_MSforeachdb '
USE [?];

SELECT 
    ''ALTER INDEX '' + 
    QUOTENAME(ISNULL(i.name, ''UNKNOWN_INDEX'')) + 
    '' ON '' + 
    QUOTENAME(ISNULL(OBJECT_SCHEMA_NAME(i.object_id), ''dbo'')) + ''.'' + 
    QUOTENAME(ISNULL(OBJECT_NAME(i.object_id), ''UNKNOWN_TABLE'')) + 
    '' DISABLE;'' AS Disable_Index_Command,  -- DISABLE yerine DROPkomutu oluşturulabilir
    i.type_desc AS Index_Type,  -- Clustered veya Non-Clustered olup olmadığını gösterir
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    p.reserved_page_count * 8 / 1024 AS IndexSizeMB, -- MB cinsinden indeks boyutu
    DB_NAME() AS DatabaseName
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s 
    ON i.object_id = s.object_id AND i.index_id = s.index_id
LEFT JOIN sys.dm_db_partition_stats p 
    ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE 
    i.is_primary_key = 0  -- Birincil anahtarları hariç tut
    AND i.is_unique = 0    -- Benzersiz indeksleri hariç tut
    AND i.object_id > 100  -- Sistem tablolarını hariç tut
    AND ISNULL(s.user_updates, 0) <> 0  -- Eğer NULL ise 0 kabul et
    AND ISNULL(s.user_seeks, 0) = 0
    AND ISNULL(s.user_scans, 0) = 0
    AND ISNULL(s.user_lookups, 0) = 0
    AND OBJECT_SCHEMA_NAME(i.object_id) NOT LIKE ''sys%''  -- Sistem şemalarını hariç tut
ORDER BY IndexSizeMB DESC;

';