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.
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.
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.
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)
