SQL Server Index Rehberi: Index Türleri, Tasarım Kuralları, Eksik ve Kullanılmayan Index’ler, Bakım

Index’ler, SQL Server’ın bir sorguyu yanıtlamak için ne kadar veri okuması gerektiğini belirler. İyi bir index milyonlarca satırlık bir taramayı birkaç sayfa okumasına indirir; eksik bir index ise aynı sorgunun her çalıştığında tablonun tamamını okumasına yol açar. Fazla index’in bedeli ise terstir: her insert, update ve delete hepsini güncellemek zorundadır ve buffer pool kimsenin okumadığı yapılarla dolar.

Bu rehberde SQL Server index’lerinin nasıl kurulduğunu, index türlerini ve hangisinin ne zaman kullanılacağını, key ve include kolonlarının nasıl tasarlanacağını, bazı sorguların neden hiçbir index’i kullanamadığını, eksik ve kullanılmayan index’lerin nasıl bulunacağını ve index bakımında hâlâ neyin önemli olduğunu anlatıyoruz.

SQL Server’da Index Nasıl Çalışır?

Rowstore bir index bir B-tree‘dir: bir root sayfası, ara seviyeler ve 8 KB’lık sayfalardan oluşan bir leaf seviyesi. Key’ler sıralı tutulur; bu sayede SQL Server tablo ne kadar büyük olursa olsun root’tan doğru leaf sayfasına birkaç okumayla ulaşır. Bu gezinme bir index seek‘tir. Leaf seviyesini baştan sona okumak ise bir index scan‘dir.

Bir tablo iki şekilde saklanır:

• Heap: clustered index yoktur. Satırlar belirli bir sırada saklanmaz ve bir satır tanımlayıcısıyla (RID) bulunur.
• Clustered index: index’in leaf seviyesi tablonun kendisidir. Satırlar clustered key sırasında saklanır ve bir tablonun yalnızca bir clustered index’i olabilir.

Nonclustered index, leaf seviyesinde index key’lerini ve satıra geri dönen bir işaretçiyi tutan ayrı bir B-tree’dir: bu işaretçi clustered key’dir, heap’te ise RID. Bir sorgu nonclustered index’te olmayan kolonlara ihtiyaç duyduğunda SQL Server her satır için bu işaretçiyi izler; buna key lookup (ya da RID lookup) denir. Birkaç lookup ucuzdur; binlercesi ise optimizer’ın tabloyu taramayı seçmesinin sık görülen nedenidir.

Index Türleri ve Ne Zaman Kullanılırlar?

Clustered Index Key’inin Seçimi

Clustered key, satır işaretçisi olarak her nonclustered index’e kopyalanır; bu yüzden seçimi tablodaki tüm index’leri etkiler. İyi bir clustered key:

• Dardır: int ya da bigint idealdir; geniş bir bileşik key her nonclustered index’i büyütür.
• Benzersizdir: aksi halde SQL Server tekrarlanan değerlere gizli, 4 baytlık bir uniquifier ekler.
• Değişmez: clustered key’i güncellemek satırı taşır ve her nonclustered index’i günceller.
• Sürekli artandır: yeni satırlar sona eklenir; bu da tablonun ortasında page split oluşmasını önler.

Identity ya da sequence kolonu genellikle dördünü de karşılar. NEWID() ile üretilen rastgele bir uniqueidentifier son ikisini karşılamaz ve yoğun page split ile fragmentation’a yol açar; GUID gerekiyorsa NEWSEQUENTIALID()‘yi düşünün ya da GUID’i bir nonclustered index’te tutun.

Nonclustered Index Tasarımı

Key kolonlarının sırası

Bileşik bir index’te kolon sırası önemlidir. SQL Server ilk kolon üzerinde seek yapabilir; sonraki kolonlarda ise ancak öncekiler eşitlikle kullanıldığı sürece. Pratik kural:

1. Önce = ile karşılaştırılan kolonlar.
2. Ardından aralık için (>, BETWEEN, LIKE 'abc%') ya da ORDER BY için kullanılan kolon.
3. Sorgunun yalnızca döndürdüğü diğer kolonlar INCLUDE‘a.

-- Sorgu: WHERE CustomerID = @c AND OrderDate >= @from ORDER BY OrderDate
-- döndürülenler: OrderID, Status, TotalAmount
CREATE NONCLUSTERED INDEX IX_Orders_Customer_OrderDate
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (Status, TotalAmount)
WITH (ONLINE = ON);   -- ONLINE, Enterprise sürüm gerektirir

Covering index ve INCLUDE

Include edilen kolonlar yalnızca leaf seviyesinde saklanır. Key’i genişletmeden index’i bir sorgu için covering hale getirir, böylece key lookup gerekmez. Her kolonu “ne olur ne olmaz” diye eklemeyin: SELECT * için covering bir index, tablonun ikinci bir kopyasıdır.

Filtered index

Küçük bir alt küme üzerindeki filtered index daha küçüktür, bakımı daha ucuzdur ve istatistikleri daha isabetlidir:

CREATE NONCLUSTERED INDEX IX_Orders_Open
ON dbo.Orders (CustomerID, OrderDate)
WHERE Status = 'Open';

Sahadan bir örnek: bir kamu kurumunda yeni sürümle gelen bir takip ekranı SQL Server CPU kullanımını %100’e kilitlemişti. SARGable olmayan bir koşulu düzeltilen view ve yalnızca açık kayıtları içeren tek bir filtered index, CPU kullanımını yarım günlük bir çalışmayla %5’e indirdi. Ayrıntıları başarı hikayemizde bulabilirsiniz.

Sorgu Index’inizi Neden Kullanmıyor?

Bir index ancak koşul sargable ise, yani SQL Server onu bir seek aralığına çevirebiliyorsa işe yarar. Seek’in scan’e dönüşmesinin yaygın nedenleri:

Sargable bir koşulda bile, koşul tablonun büyük bir kısmını döndürüyorsa optimizer scan’i seçebilir; çünkü çok sayıda key lookup her şeyi bir kez okumaktan pahalıya gelir. Bu, index’i hint ile zorlamak için değil, covering yapmak için bir nedendir.

Eksik Index’leri Bulmak

SQL Server, sorguları derlerken optimizer’ın keşke olsaydı dediği index’leri kaydeder:

SELECT TOP (25)
    DB_NAME(d.database_id)                         AS database_name,
    d.statement                                    AS table_name,
    d.equality_columns, d.inequality_columns, d.included_columns,
    s.user_seeks, s.user_scans,
    CAST(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS bigint) AS improvement_score
FROM sys.dm_db_missing_index_details     AS d
JOIN sys.dm_db_missing_index_groups      AS g ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handle
ORDER BY improvement_score DESC;

Kullanılmayan ve Tekrarlanan Index’leri Bulmak

Her index’in bir yazma maliyeti vardır. Güncellenen ama hiç okunmayan index’ler saf yüktür:

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS table_name,
    i.name                                  AS index_name,
    i.type_desc,
    ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0) AS reads,
    ISNULL(u.user_updates, 0)               AS writes
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS u
       ON u.object_id = i.object_id AND u.index_id = i.index_id AND u.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.type_desc = 'NONCLUSTERED'
  AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
ORDER BY reads ASC, writes DESC;

Kullanım istatistikleri instance yeniden başladığında (bazı sürümlerde index rebuild edildiğinde de) sıfırlanır; bir şeyi silmeden önce ay sonu job’ları dahil en az bir tam iş döngüsünü kapsayan veriye bakın. Ayrıca aynı key kolonlarına sahip tekrarlanan index’leri ve birinin key’lerinin diğerinin soldan başlayan bir öneki olduğu çakışan index’leri arayın; dar olanı genellikle kaldırılabilir.

Columnstore Index’ler

Columnstore index’ler her kolonu yaklaşık bir milyon satırlık sıkıştırılmış segmentlerde ayrı ayrı saklar ve batch mode’da işler. Büyük tabloları tarayıp toplayan sorgularda I/O’yu çoğu zaman on kat azaltırlar.

• Clustered columnstore index, fact tabloları ve büyük geçmiş tabloları için ana depolamadır.
• OLTP tablosu üzerindeki bir nonclustered columnstore index, raporların ayrı bir veri ambarı olmadan analitik sorgu çalıştırmasını sağlar.
• SQL Server 2025, sıralı (ordered) nonclustered columnstore index’leri ve sıralı columnstore için online build’i getiriyor.
• Columnstore tekil satır aramalarında ve sık küçük güncellemelerde zayıftır; bu erişim yolları için rowstore index’leri koruyun.

Index Bakımı: Hâlâ Önemli Olanlar

Yıllarca standart öneri, %5–30 fragmentation üzerindeki index’leri reorganize, %30 üzerindekileri rebuild etmekti. Modern SSD ve flash depolamada mantıksal fragmentation, dönen disklerdeki kadar önemli değil. Hâlâ önemli olanlar:

• İstatistikler: güncel olmayan istatistikler fragmentation’dan çok daha fazla kötü plana yol açar. Özellikle otomatik güncellemenin geç tetiklendiği büyük tablolarda düzenli güncelleyin.
• Sayfa doluluğu: yarı boş sayfalar bellek ve I/O israfıdır. Yalnızca fragmentation yüzdesine değil, avg_page_space_used_in_percent değerine de bakın.
• Fill factor: sürekli artan key’lerde 100’de bırakın; yalnızca ölçülmüş page split’leri olan index’lerde düşürün.
• Maliyet: rebuild’ler büyük miktarda log üretir; bu da AG replica’larını, log backup’ları ve log shipping’i etkiler. Her gece her şeyi değil, fayda göreni rebuild edin.

SELECT
    OBJECT_SCHEMA_NAME(ps.object_id) + '.' + OBJECT_NAME(ps.object_id) AS table_name,
    i.name AS index_name,
    ps.page_count,
    CAST(ps.avg_fragmentation_in_percent   AS decimal(5,1)) AS fragmentation_pct,
    CAST(ps.avg_page_space_used_in_percent AS decimal(5,1)) AS page_density_pct
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ps
JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.page_count > 1000 AND ps.index_level = 0
ORDER BY ps.page_count DESC;

Sık Yapılan Index Hataları

• Yoğun insert alan tablolarda rastgele GUID clustered key’ler.
• Her eksik index önerisini olduğu gibi oluşturmak.
• Gerçek sorgulara uyan birkaç bileşik index yerine her kolona ayrı tek kolonlu index.
• Yalnızca yazma maliyeti ekleyen tekrarlanan ve çakışan index’ler.
• WHERE koşullarında index’li kolonlar üzerinde fonksiyon ve örtük dönüşüm.
• İstatistikler eski kalırken her gece tüm index’leri rebuild etmek.
• Birkaç günlük kullanım istatistiğine bakarak “kullanılmayan” index’leri silmek.

Index Kontrol Listesi

• Her tablonun bilinçli seçilmiş bir clustered index’i var; heap’ler açık bir tercih.
• Clustered key’ler mümkün olduğunca dar, benzersiz, değişmez ve sürekli artan.
• Nonclustered index’ler gerçek sorgu kalıplarına uyuyor; eşitlik kolonları önde, döndürülen kolonlar INCLUDE’da.
• Eksik index önerileri körü körüne uygulanmıyor, incelenip birleştiriliyor.
• Kullanılmayan, tekrarlanan ve çakışan index’ler tam bir iş döngüsü boyunca değerlendiriliyor.
• İstatistikler güncel tutuluyor; rebuild’ler boyut, doluluk ve faydaya göre hedefleniyor.
• Raporlama iş yükleri columnstore için değerlendirildi.