SQL Server’da Index Recommended Size Değerinin Aşılması

SQL Server’da performansı artırmak için vazgeçilmez olan indeksler, doğru yapılandırılmadığında beklenmedik hatalara ve performans sorunlarına yol açabilir. Özellikle indeks anahtarının maksimum boyut sınırı, SQL Server sürümlerine göre değişiklik gösteren kritik bir konudur. Bu makalede, bu sınırların ne olduğunu, hangi hatalara sebep olabileceğini ve bu sorunları nasıl tespit edip çözebileceğinizi adım adım ele alacağız.

SQL Server sürümleri, indeks türlerine göre farklı maksimum anahtar boyutları belirlemiştir. Sql server 2014 ve daha önceki sürümlerde clustered ve non clustered indexlerde maksimum index boyutu 900 byte iken, Sql server 2016 ve sonraki sürümlerde clustered indexlerde 900 byte non clustered indexlerde ise 1700 byte boyutlarındadır.

Versiyon	Clustered Index Limiti	Non-Clustered Index Limiti
SQL Server 2014 ve Öncesi	900 Byte	900 Byte
SQL Server 2016 ve Sonrası	900 Byte	1700 Byte

Eğer bu limitleri aşan bir kolon üzerinde indeks oluşturmaya çalışırsanız, SQL Server indeks oluşturmanıza izin verir ancak bir uyarı (Warning) fırlatır. Eğer o kolona limiti aşacak uzunlukta bir veri girmeye çalışırsanız, işlem hata verir ve başarısız olur.Bu limitler genellikle varchar, nvarchar, char gibi sabit veya değişken uzunluklu karakter dizileri için kritiktir.

Bu bayt sınırı, indeks oluşturma anında değil, INSERT ve UPDATE işlemleri sırasında kontrol edilir. Mevcut bir indeksin anahtar boyutu sınırı aşıyorsa, aşağıdaki hatayı alırsınız:

Msg 1946, Level 16, State 3
Operation failed. The index entry of length … bytes exceeds the maximum length of … bytes.

Bu hata, veri bütünlüğünü bozabilecek, kritik iş süreçlerini aksatabilecek ve sorun gidermesi zor olabilecek ciddi bir problemdir.

Anahtar boyutu sınırını aşmaktan kaçınmanın en etkili yollarından biri, INCLUDE seçeneğini akıllıca kullanmaktır. INCLUDE ile eklenen sütunlar indeksin anahtar parçası olmaz. Bu sütunlar yalnızca yapraklarda (leaf level) saklanır. İnclude edilen sütunların boyutu 900/1700 bayt sınırına dahil edilmez. Include yapısının sağladığı başka bir avantaj sorgularınız sadece indeksteki sütunları kullanıyorsa, fiziksel tabloya erişmeye gerek kalmadan sonucu getirir (Performans Kazanımı). Anahtar boyutu küçülür, bu da indeksin daha az yer kaplaması ve daha hızlı bakım işlemleri (rebuild, reorganize) anlamına gelir.

• Key Columns: Toplamda 900 veya 1700 byte ile sınırlıdır.
• Included Columns: SQL Server’ın veri tipi limitlerine kadar çıkabilir. Yani bir varchar(max) veya nvarchar(max) bile (bazı istisnalar hariç) teknik olarak eklenebilir. Toplam boyut ise bir veri sayfasının sınırlarını zorlayabilir ancak anahtar boyutu kısıtlamasına takılmaz.

Diyelim ki SQL Server 2014 kullanıyorsunuz (Limit: 900 byte):

CREATE INDEX IX_Ornek 
ON TabloAdi (Ad, Soyad) -- Bu ikisinin toplamı 900 byte'ı geçemez.
INCLUDE (Adres, Ozgecmis); -- Bunlar 900 byte limitine dahil değildir!

Bu yapıda SQL Server sadece Ad ve Soyad alanlarını indeks ağacında sıralar. Adres ve Ozgecmis verilerini ise en alttaki yapraklarda (leaf nodes) saklar.

Neden INCLUDE Kullanmalısınız?

1. Index Covering: Sorgunuzun ihtiyaç duyduğu tüm sütunlar indekste varsa (ya anahtar olarak ya da include olarak), SQL Server asıl tabloya (Clustered Index/Heap) gitmek zorunda kalmaz. Buna “Index Covering” denir ve performansı uçurur.
2. Limitleri Aşmak: Çok uzun bir nvarchar(2000) kolonunuz varsa ve bu kolon üzerinden arama yapmıyor, sadece sonucu ekranda göstermek istiyorsanız bunu INCLUDE kısmına koyarak hata almaktan kurtulursunuz.

Özellik	Anahtar Sütun (Key)	Dahil Edilen Sütun (Include)
Sıralama Yapılır mı?	Evet	Hayır
Limit (SQL 2016+)	1700 Byte (Non-Clustered)	Sınırsız (Veri tipi limitine kadar)
Performans Etkisi	Arama hızını belirler	Diskten okumayı azaltır

Sınırı aşan mevcut indekslerinizi bulmak için aşağıdaki T-SQL komutunu kullanabilirsiniz. Bu komut, SQL Server sürümünüzü otomatik olarak algılar ve uygun sınırı (900 veya 1700) uygular.

DECLARE @sqlmajorver int, @sqlcmd NVARCHAR(max)
SELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff)
SET @sqlcmd='SELECT @@servername InstanceName, DB_Name() DBName, 
schema_name (o.schema_id) AS ''SchemaName'',o.name AS TableName, 
i.name AS IndexName, i.type AS IndexType, sum(max_length) AS KeyLength,'
IF @sqlmajorver >= 13  SET @sqlcmd=@sqlcmd+'CASE WHEN i.type=2 THEN 1700 ELSE 900 END'
ELSE SET @sqlcmd=@sqlcmd+'900'
SET @sqlcmd=@sqlcmd+' SizeLimit, count (ic.index_id) AS ''ColumnCount''
FROM sys.indexes i (NOLOCK) 
INNER JOIN sys.objects o (NOLOCK)  ON i.object_id =o.object_id  '
IF @sqlmajorver >= 11 SET @sqlcmd=@sqlcmd+' INNER JOIN sys.tables t (NOLOCK)  ON o.object_id =t.object_id AND t.is_filetable=0 '
IF @sqlmajorver >= 12 SET @sqlcmd=@sqlcmd+' AND t.is_memory_optimized=0 '
SET @sqlcmd=@sqlcmd+' INNER JOIN sys.index_columns ic  (NOLOCK) ON ic.object_id =i.object_id and ic.index_id =i.index_id
INNER JOIN sys.columns c  (NOLOCK) ON c.object_id = ic.object_id and c.column_id = ic.column_id
WHERE o.type =''U'' and i.index_id >0 and ic.is_included_column=0
GROUP BY o.schema_id,o.object_id,o.name,i.object_id,i.name,i.index_id,i.type
HAVING (sum(max_length) > '
IF @sqlmajorver >= 13 SET @sqlcmd=@sqlcmd+'CASE WHEN i.type=2 THEN 1700 ELSE 900 END)'
ELSE SET @sqlcmd=@sqlcmd+'900)'
SET @sqlcmd=@sqlcmd+' ORDER BY 1,2,3,4'
EXEC sp_executesql @sqlcmd

Aşağıdaki resimde dikkat derseniz clustered index olarak oluşturulduğu için SizeLimit değeri 900 byte görülmektedir. Bu yapıda olan indexlerde sadece page sayısı fazla olmaktadır. Rebuild reorganize memoryde kapladığı alanlar konusunda sıkıntı ya sebebiyet vermektedir.

Yukarıdaki yapılardan sonra ilgili index yapısının incelenmesi gerekmektedir. Öncelikle yukarıdaki index boyutunu artıran kolonun bulunması gerekmektedir. İlgili kolonun index için gerekli olup olmadığını sorulması gerekmektedir. Burada şu yöntemler kullanılır: Aynı isimle drop existing on ifadesi ile ilgili kolon çıkarılıp index oluşturulması gerekmektedir. İndex yapısının kullanılıp kullanılmayıp durumuna bakıp kullanılmayan bu yapıda index’in drop edilmesi gerekmektedir.

Büyük VARCHAR, NVARCHAR veya VARBINARY sütunları mümkün olduğunca INCLUDE etmeye çalışın.

Recommended Size değerinin olmasının dezavantajları:

1. Sayfa Başına Daha Az Kayıt (Low Page Density)

SQL Server verileri 8 KB’lık sayfalarda (pages) saklar.

• Küçük Anahtarlar: Bir sayfaya binlerce indeks kaydı sığabilir.
• Büyük Anahtarlar: Anahtar boyutunuz 1700 byte’a yakınsa, bir sayfaya sadece 4-5 kayıt sığabilir. Bu durum, aynı miktarda veriyi okumak için SQL Server’ın çok daha fazla sayfayı hafızaya (Buffer Pool) yüklemesine neden olur, bu da I/O (Girdi/Çıktı) yükünü artırır.

2. İndeks Ağacının Derinleşmesi (Increased Index Depth)

Bir indeks anahtarı ne kadar büyükse, SQL Server o indeksi yönetmek için o kadar çok “seviye” (Level) oluşturur.

• Küçük bir anahtarda kök (root) seviyesinden veriye 2 adımda ulaşıyorken, devasa anahtarlarda bu 5-6 adıma çıkabilir.
• Her bir adım, işlemcinin daha fazla karşılaştırma yapması ve daha fazla bellek sayfası okuması demektir.

3. Bellek (Memory/RAM) Verimsizliği

SQL Server, en sık kullanılan indeks sayfalarını RAM’de (Buffer Pool) tutmaya çalışır.

• Geniş indeksler RAM’de çok yer kaplar.
• Bu durum, diğer önemli verilerin RAM’den atılmasına (Page Life Expectancy düşüşüne) ve sistemin sürekli diskten okuma yapmasına (Disk Thrashing) neden olur.

4. İstatistiklerin Bozulması ve Yanlış Sorgu Planları

SQL Server, indeksler üzerindeki istatistikleri (Histogram) kullanarak sorgu planı oluşturur.

• Çok geniş ve karmaşık anahtarlarda (örneğin 5-6 kolonlu birleşik indeksler), SQL Server’ın benzersizlik (cardinality) tahmini zorlaşır.
• Sonuç: Query Optimizer yanlış plan seçer ve basit bir sorgu dakikalarca sürebilir.

5. Yazma Performansında Düşüş (Fragmentation)

Büyük anahtarlı indekslerde Page Split (Sayfa Bölünmesi) çok daha sık yaşanır.

• Bir sayfaya yeni bir kayıt ekleneceği zaman yer yoksa, SQL Server o sayfayı ikiye böler.
• Anahtar büyükse, sayfa zaten hemen dolduğu için her INSERT veya UPDATE işleminde veritabanı sayfaları fiziksel olarak disk üzerinde darmadağın olur (Fragmentation). Bu da indeks bakım maliyetini ve sorgu süresini artırır.

Başka makalede görüşmek dileğiyle..

Kişi dostunun dini üzeredir. Bu yüzden her biriniz, kiminle dostluk ettiğine dikkat etsin.(Hadis)