SQL Server İndeksleri: Bilmeniz Gereken Her Şey

İngilizce “index” kelimesinin Türkçe karşılığı “indeks”tir ve çoğul hali de yine “indeksler”dir. Ancak günlük kullanımda bazen hatalı olarak “indexler” de denilebiliyor. Teknik olarak doğru kullanım “indeksler”dir. Bu bilgiyi açıkladıktan sonra devam etmek isterim.

Bir DBA olarak indeks kavramını anlatırken, öncelikle veritabanlarında verinin nasıl organize edildiğini ve sorguların nasıl çalıştığını anlamak önemlidir. Veritabanları, veriyi tablolar halinde saklar ve her tabloda satırlar ve sütunlar bulunur. Bir sorgu çalıştırdığınızda, veritabanı istenen veriyi bulmak için tablonun tamamını taramak zorunda kalabilir. Tablo büyüdükçe, bu tarama işlemi çok zaman alabilir ve performans sorunlarına yol açabilir.

İşte tam bu noktada indeksler devreye girer. İndeks, bir tablodaki belirli sütunlardaki verileri ve bu verilerin bulunduğu satırların konumlarını içeren bir veri yapısıdır. Kitaptaki indeks gibi düşünebilirsiniz. Aradığınız konuyu bulmak için tüm kitabı taramak yerine, indekse bakıp konunun hangi sayfada olduğunu hızlıca bulabilirsiniz.

İndeksler, veritabanının belirli bir değere sahip satırları hızlı bir şekilde bulmasını sağlar. Sorgu çalıştırıldığında, veritabanı önce indekse bakar ve aradığı değeri indekste bulursa, doğrudan ilgili satıra erişebilir. Bu sayede, tablonun tamamını taramak gerekmez ve sorgu çok daha hızlı çalışır.

İndekslerin Avantajları:

• Hızlı Sorgu Performansı: İndeksler, sorguların performansını önemli ölçüde artırır, özellikle büyük tablolarda.
• Azaltılmış Disk I/O: İndeksler, diskten okunan veri miktarını azaltarak sunucu yükünü hafifletir.
• Geliştirilmiş Kaynak Kullanımı: Doğru kullanıldığında, indeksler sunucu kaynaklarının daha verimli kullanılmasını sağlar.

İndekslerin Dezavantajları:

• Ek Disk Alanı: İndeksler disk alanı tüketir.
• Yavaşlatılmış Yazma İşlemleri: Veri ekleme, güncelleme ve silme işlemleri indeksler nedeniyle biraz yavaşlayabilir.

İndeks Oluştururken Dikkat Edilmesi Gerekenler:

• Sık Kullanılan Sorgular: İndeksler, sık kullanılan sorgulardaki sütunlar için oluşturulmalıdır.
• Yüksek Seçicilik: İndekslenen sütundaki değerlerin çeşitliliği ne kadar yüksekse, indeks o kadar etkili olur.
• İndeks Bakımı: İndeksler zamanla parçalanabilir ve performanslarını kaybedebilirler. Bu nedenle düzenli bakım gereklidir.

SQL Server’da birçok indeks türü mevcuttur. Bunları temel olarak Clustered ve Non-Clustered olmak üzere ikiye ayırabiliriz. Bunların altında ise farklı özelliklere ve kullanım senaryolarına sahip çeşitli indeks türleri bulunur.

1. Clustered İndeks:

• Bir tabloda yalnızca bir clustered indeks bulunabilir.
• Veriyi fiziksel olarak diskte sıralar.
• Telefon rehberi gibi düşünebilirsiniz; veriler indekste belirtilen sütuna göre sıralıdır.
• Genellikle birincil anahtar üzerinde oluşturulur.
• Aralık tabanlı sorgular için idealdir.

2. Non-Clustered İndeks:

• Bir tabloda birden fazla non-clustered indeks bulunabilir.
• Veriyi fiziksel olarak sıralamaz, bunun yerine bir “kitap indeksi” gibi çalışır ve veriye işaretçiler içerir.
• Eşitlik ve eşitsizlik aramaları için uygundur.

Non-Clustered İndeks Çeşitleri:

• B-tree: En yaygın non-clustered indeks türüdür. Dengeli bir ağaç yapısı kullanır ve çok çeşitli sorgu türleri için uygundur.
• Full-text: Metin verilerinde kelime veya ifade aramaları için kullanılır. Kelime kökleri, eş anlamlılar ve yakınlık aramaları gibi gelişmiş arama özellikleri sunar.
• Spatial: Coğrafi ve geometrik veriler için kullanılır. Uzamsal veriler üzerindeki sorguları hızlandırır.
• XML: XML verileri indekslemek için kullanılır. XML verileri üzerinde XPath sorguları yapmanızı sağlar.
• Columnstore: Büyük veri kümeleri için tasarlanmıştır. Veriyi sütun bazında depolar ve sıkıştırır, bu da analitik sorguların performansını önemli ölçüde artırır. Clustered ve non-clustered columnstore indeksleri mevcuttur.
• Filtered: Belirli bir filtreye uyan verileri indeksler. Bu, nadiren kullanılan verileri indekslemekten kaçınarak indeks boyutunu ve bakım maliyetini azaltır.
• Unique: Yinelenen değerlere izin vermez. Birincil anahtar ve benzersiz kısıtlamalar için kullanılır.

Hash İndeksleri (Memory-Optimized Tablolar):

• Sadece bellek içi optimize edilmiş tablolar için geçerlidir.
• Çok hızlı arama sağlar, ancak disk tabanlı tablolarda kullanılamaz.

İndeks Oluşturma Sözdizimi:

Aşağıda farklı indeks türleri için örnek oluşturma scriptleri verilmiştir:

-- Clustered İndeks
CREATE CLUSTERED INDEX IX_Orders_OrderID ON Orders(OrderID);

-- Non-Clustered B-tree İndeks
CREATE NONCLUSTERED INDEX IX_Customers_CustomerID ON Customers(CustomerID);

-- Full-text İndeks (önce full-text kataloğu oluşturulmalıdır)
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Products(ProductName) KEY INDEX PK_Products ON ftCatalog;

-- Spatial İndeks
CREATE SPATIAL INDEX IX_Locations_GeoLocation ON Locations(GeoLocation) USING GEOMETRY_GRID WITH (GRIDS = (LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM));

-- XML İndeks
CREATE PRIMARY XML INDEX IX_ProductDescriptions_XMLData ON ProductDescriptions(XMLData);

-- Columnstore İndeks
CREATE CLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore ON Sales;

-- Filtered İndeks
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_Filtered ON Orders(OrderDate) WHERE OrderStatus = 'Shipped';

-- Unique İndeks
CREATE UNIQUE NONCLUSTERED INDEX IX_Customers_Email ON Customers(Email);


-- Hash İndeks (Memory-Optimized Tablo)
CREATE TABLE MemoryOptimizedTable (
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    Data VARCHAR(255)
) WITH (MEMORY_OPTIMIZED = ON);

Bu scriptler sadece örnektir ve ihtiyaçlarınıza göre özelleştirilmelidir. İndeks oluştururken veri dağılımını, sorgu kalıplarını ve performans gereksinimlerini dikkate almak önemlidir.

SQL Server’da indeks oluştururken, indeksi primary filegroup dışında farklı bir filegroup’ta oluşturabilirsiniz. Varsayılan olarak, indeks, tablonun bulunduğu filegroup’ta oluşturulur. Bu genellikle primary filegroup olur.

Farklı Filegroup’ta İndeks Oluşturmanın Avantajları:

• I/O Performansının Artırılması: İndeks ve tablo verilerini farklı fiziksel disklere yayarak, okuma ve yazma işlemleri paralel olarak gerçekleştirilebilir. Bu, özellikle yoğun okuma/yazma işlemleri olan sistemlerde performansı önemli ölçüde artırabilir.
• Yönetilebilirlik: Yedekleme ve geri yükleme işlemleri daha esnek hale gelir. Belirli bir filegroup’u yedekleyebilir veya geri yükleyebilirsiniz. Bu, büyük tablolar için yedekleme ve geri yükleme sürelerini kısaltabilir.
• Alan Yönetimi: Disk alanı dolduğunda, sadece ilgili filegroup’a yeni disk ekleyerek sorunu çözebilirsiniz. Bu, tüm veritabanını taşımaktan daha kolay ve hızlıdır.
• Bakım İşlemlerinin Etkisinin Azaltılması: İndeks yeniden oluşturma veya yeniden düzenleme gibi bakım işlemleri, sadece ilgili filegroup’u etkiler. Bu, diğer filegroup’larda bulunan tablolar üzerindeki etkileri en aza indirir.

Farklı Filegroup’ta İndeks Oluşturmanın Dezavantajları:

• Karmaşıklık: Filegroup yönetimi, veritabanı yönetimini biraz daha karmaşık hale getirebilir.
• Yanlış Yapılandırma Riski: Filegroup’lar doğru şekilde yapılandırılmazsa, performans düşüşlerine neden olabilir. Örneğin, indeks ve tablo verileri aynı fiziksel diskte bulunuyorsa, performans avantajı elde edemezsiniz.
• Planlama Gerekliliği: Filegroup’ları kullanmadan önce, veri erişim modellerinizi ve büyüme tahminlerinizi dikkatlice planlamanız gerekir.

İndeks Oluşturma Sırasında Filegroup Belirtme:

Farklı bir filegroup’ta indeks oluşturmak için ON ifadesinden sonra FILEGROUP filegroup_adı eklemeniz yeterlidir.

-- Örnek: SalesOrderDetail tablosundaki ProductID sütununa IX_SalesOrderDetail_ProductID adında, 
-- FG2 adlı filegroup'ta non-clustered bir indeks oluşturma:

CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductID
ON SalesOrderDetail (ProductID)
ON FG2; -- FG2 filegroup'unu belirttik.


-- Clustered index örneği:
CREATE CLUSTERED INDEX IX_SalesOrderDetail_SalesOrderID
ON SalesOrderDetail (SalesOrderID)
ON FG1; -- FG1 filegroup'unu belirttik.

• Filegroup’u belirtmezseniz, indeks tablonun bulunduğu filegroup’ta oluşturulur.
• Filegroup’un önceden oluşturulmuş olması gerekir.
• tempdb veritabanında filegroup kullanamazsınız

Sonuç olarak, farklı filegroup’larda indeks oluşturmak, performans, yönetilebilirlik ve alan yönetimi açısından avantajlar sağlayabilir. Ancak, bu avantajları elde etmek için dikkatli planlama ve doğru yapılandırma gereklidir. Eğer filegroup yönetiminin getirdiği karmaşıklıktan kaçınmak istiyorsanız, indeksleri varsayılan filegroup’ta bırakabilirsiniz.

SQL Server’da indeks performansını izlemek ve analiz etmek için çeşitli araçlar ve yöntemler mevcuttur. Bu araçlar, indeks kullanım istatistiklerini, parçalanma düzeyini ve performans darboğazlarını belirlemenize yardımcı olur. İşte en yaygın kullanılan bazıları:

1. SQL Server Management Studio (SSMS):

• Object Explorer: İndekslerin özelliklerini, boyutunu ve parçalanma oranını görüntüleyebilirsiniz.
• Activity Monitor: Çalışan sorguları, kaynak kullanımını ve indeks kullanımını gerçek zamanlı olarak izleyebilirsiniz.
• Database Engine Tuning Advisor (DTA): Veritabanı iş yükünüzü analiz ederek eksik veya kullanılmayan indeksleri tespit eder ve yeni indeksler önerir.

2. Dynamic Management Views (DMV’ler) ve Functions (DMF’ler):

• sys.dm_db_index_usage_stats: İndekslerin arama, tarama, güncelleme ve silme istatistiklerini sağlar. Bu DMV, hangi indekslerin sık kullanıldığını ve hangilerinin nadiren veya hiç kullanılmadığını belirlemenize yardımcı olur.
• sys.dm_db_index_physical_stats: İndekslerin fiziksel özelliklerini, parçalanma düzeyini ve doluluk oranını gösterir. Bu DMV, indeks bakım işlemlerinin gerekli olup olmadığını belirlemek için kullanılır.
• sys.dm_db_missing_index_group_stats: Eksik indekslerin performans üzerindeki etkisini tahmin eder ve eksik indeks önerileri sunar.
• sys.dm_db_missing_index_details: Eksik indeks önerileri hakkında ayrıntılı bilgi sağlar.

3. Performans Sayaçları:

• Page Life Expectancy (PLE): Buffer pool’da bir sayfanın ortalama kalma süresini gösterir. Düşük PLE değeri, bellek yetersizliğine ve disk I/O işlemlerinin artmasına işaret edebilir.
• Buffer Cache Hit Ratio: Verilerin buffer pool’dan okunma oranını gösterir. Yüksek bir oran, verilerin bellekten okunduğunu ve disk I/O işlemlerinin az olduğunu gösterir.
• Page Reads/sec: Saniyede diskten okunan sayfa sayısını gösterir. Yüksek bir değer, disk I/O darboğazına işaret edebilir.

4. SQL Server Profiler (Eski Sürümlerde):

• SQL Server Profiler, veritabanı etkinliğini izlemek için kullanılan bir araçtır. Çalışan sorguları, indeks kullanımını ve performans istatistiklerini yakalayabilirsiniz. SQL Server Profiler, SQL Server 2012 ve sonraki sürümlerinde kullanımdan kaldırılmıştır ve yerine Extended Events önerilmektedir.

5. Extended Events:

• Extended Events, SQL Server Profiler’ın daha güçlü ve esnek bir alternatifidir. İndeks kullanımı, sorgu performansı ve diğer veritabanı etkinlikleri hakkında ayrıntılı bilgi toplayabilirsiniz.

6. Üçüncü Parti Araçlar:

• ApexSQL Monitor
• SentryOne SQL Sentry
• SolarWinds Database Performance Analyzer

Bu araçlar, indeks performansını izlemek ve analiz etmek için daha gelişmiş özellikler sunar.

Örnek DMV Kullanımı:

-- İndeks kullanım istatistiklerini görüntüleme
SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('VeritabanıAdı');

-- İndeks parçalanma istatistiklerini görüntüleme
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID('VeritabanıAdı'), OBJECT_ID('TabloAdı'), NULL, NULL, 'DETAILED');

-- Eksik indeks önerilerini görüntüleme
SELECT * FROM sys.dm_db_missing_index_group_stats;

SELECT * FROM sys.dm_db_missing_index_details(mgstats.index_handle);

Bu araçları ve yöntemleri kullanarak indeks performansını düzenli olarak izlemeniz ve gerektiğinde indeks bakım işlemleri (yeniden oluşturma, yeniden düzenleme) yapmanız, veritabanınızın performansını optimize etmenize yardımcı olacaktır.