SQL Server Dizinleri: Veritabanı Performansını Artırma

SQL Server ile çalışmaya ilk başladığımda, tüm yavaş sorguların aynı olmadığını fark ettim. Bazı sorgular kötü tasarım yüzünden yavaştı, bazıları ise sadece verimsizdi. Ancak bir keşif, veritabanı performansına yaklaşımımı tamamen değiştirdi: dizinler. Kocaman bir ders kitabında dizin olmadan belirli bir bölümü bulmaya çalıştığınızı düşünün; yüzlerce sayfayı çevirir, değerli zaman kaybedersiniz. Bu senaryo, doğru şekilde dizinlenmemiş veritabanlarında olan bitenin birebir karşılığıdır.

Bu yazıda SQL Server dizinlerinin neden önemli olduğunu, nasıl çalıştıklarını ve veritabanlarınızın performansını en üst düzeye çıkarmak için bunlardan nasıl yararlanabileceğinizi açıklayacağım. SQL Server'da SQL becerilerinizi geliştirmek istiyorsanız, veri işleme ve analiz için T-SQL'i öğrenmek üzere Introduction to SQL Server ve Intermediate SQL Server kurslarımızı almanızı öneririm.

SQL Server Dizinlerini Anlamak

SQL Server dizinleri, tüm veri kümelerini taramadan veritabanlarındaki tabloları verimli bir şekilde sorgulamanıza olanak tanır. Aşağıda SQL Server dizinlerinin tanımı ve veritabanı yönetimindeki önemi yer almaktadır.

SQL Server dizinleri nedir?

Dizinler, SQL Server'ın satırları daha verimli bulup getirmesini sağlayan, hem zaman hem de hesaplama kaynaklarından tasarruf ettiren özel veri yapılarıdır. Dizinler, veriyi daha hızlı aramalara imkân tanıyacak şekilde düzenleyerek çalışır. Tüm tabloyu satır satır taramak (table scan olarak bilinen işlem) yerine, SQL Server gerekli satırları çok daha hızlı bulmak için dizini kullanabilir.

SQL Server'da iki temel dizin türü vardır:

• Kümelenmiş (clustered) dizinler: Kümelenmiş bir dizin, bir tablodaki verinin fiziksel sırasını belirler. Bir tabloya kümelenmiş dizin oluşturduğunuzda satırlar, dizinlenen sütunun sırasına göre diskte saklanır. Bu, özellikle aralık sorguları için faydalıdır; satırlara verimli şekilde sıralı erişim sağlar.

• Kümelenmemiş (non-clustered) dizinler: Kümelenmemiş dizinler verinin fiziksel sırasını etkilemez. Bunun yerine, gerçek veri satırlarına işaretçiler içeren ayrı bir yapı oluştururlar. Kümelenmiş dizinde yer almayan sütunlara göre filtreleyen veya sıralayan sorguların performansını artırmak için kullanışlıdırlar.

Veri yönetiminde SQL Server'ın temel ve ileri düzey kullanımını anlamak için SQL Server Tutorial içeriğimize göz atın.

Dizinler neden önemlidir?

Dizinler, sorgu performansını ve genel veritabanı verimliliğini artırmak için önemlidir. Dizin kullanmanın başlıca faydaları şunlardır:

• Daha hızlı sorgu yürütme: Dizinler, veriyi bulup getirme süresini azaltır. Örneğin, milyonlarca satırlık bir tabloda belirli bir müşteriye ait tüm siparişleri arıyorsanız, müşteri sütunundaki bir dizin SQL Server'ın bu satırları neredeyse anında bulmasını sağlar.

• Kaynakların verimli kullanımı: Dizinlerle SQL Server görevleri daha az CPU ve bellek kaynağı kullanarak tamamlayabilir. Bu verimlilik, özellikle yoğun yük altında genel sistem performansının daha iyi olmasına dönüşür.

• Sıralama ve gruplaştırmanın iyileştirilmesi: Dizinler, sıralama (ORDER BY) ve gruplaştırma (GROUP BY) içeren işlemleri hızlandırabilir. Veriyi sıralı tutarak SQL Server'ın sonuçları ilave sıralama yükü olmadan hızlıca getirmesini sağlar.

Dizin uygulamalarına şu kullanım örneğini düşünün. Kullanıcıların sıkça ürünleri ada göre aradığı büyük bir e-ticaret platformu hayal edin. Ürün adı sütununda kümelenmemiş bir dizin oluşturarak bu aramaları neredeyse anlık hâle getirebilir, kullanıcı deneyimini iyileştirip sunucu yükünü azaltabilirsiniz.

Sıralama, gruplaştırma ve tabloları birleştirme konularını öğrenmek için DataCamp’in SQL Server Fundamentals beceri yoluna göz atmanızı öneririm.

SQL Server Dizin Türleri

Her birinin kendine özgü özellikleri ve kullanım alanları olan farklı SQL Server dizin türleri vardır. Yaygın dizin türlerini tartışalım.

Kümelenmiş ve kümelenmemiş dizinler

SQL Server'daki iki ana dizin türü kümelenmiş ve kümelenmemiş dizinlerdir.

Kümelenmiş dizin

Kümelenmiş bir dizin, bir tablodaki satırların fiziksel sırasını belirler ve tabloyu dizin sütun(lar)ına göre fiilen sıralar. Veri fiziksel olarak yalnızca tek bir şekilde sıralanabileceği için, tablo başına yalnızca bir kümelenmiş dizin olabilir. Kümelenmiş dizin, bir değer aralığı döndüren veya veriyi dizinli sütuna göre sıralaması gereken sorgular için idealdir. 

Örneğin, OrderDate sütununa kümelenmiş dizin uygulanmış bir satış tablosu fayda sağlar. Belirli bir tarih aralığındaki tüm siparişleri getiren sorgular çok daha hızlı çalışır.

Kümelenmemiş dizin

Kümelenmemiş bir dizin, tablodaki gerçek satırlara işaret eden ayrı bir yapı oluşturur. Bir tabloda farklı sorgu desenleri için kullanılabilecek birden fazla kümelenmemiş dizin olabilir. Kümelenmemiş dizinler, gerçek veriye başvuru (satır bulucu) içerir; bu, bir satır kimliği veya kümelenmiş dizin anahtarı olabilir. Bu tür dizinler, arama koşullarında veya birleştirmelerde sıklıkla kullanılan sütunlara (ör. EmailAddress, ProductName) uygulanır.

Örneğin, bir envanter tablosundaki ProductName sütununa kümelenmemiş bir dizin eklemek, tablonun yapısını değiştirmeden belirli ürünler için yapılan aramaları hızlandırır.

Benzersiz ve bileşik dizinler

SQL Server, her biri belirli amaçlara hizmet eden benzersiz ve bileşik dizinleri de destekler.

Benzersiz dizinler

Benzersiz bir dizin, dizinlenen sütun(lar)daki tüm değerlerin birbirinden farklı olmasını sağlar. Yinelenen değerleri engelleyerek veri bütünlüğünü uygular ve genellikle e-posta adresleri veya kullanıcı adları gibi doğal olarak benzersiz sütunlara uygulanır. Örneğin, bir kullanıcı tablosundaki Email sütununa benzersiz bir dizin, iki kullanıcının aynı e-posta adresine sahip olmasını engeller.

Bileşik dizinler

Bileşik bir dizin, birden fazla sütunu kapsar ve bu sütunların birleşimine göre filtreleyen veya sıralayan sorguları optimize eder. Bileşik dizindeki sütunların sırası önemlidir; SQL Server soldaki en baştaki sütundan başlayarak işler. Bileşik dizinler, birden çok koşulu içeren karmaşık sorguları destekler. Örneğin, bir müşteri tablosunda LastName ve FirstName üzerinde oluşturulan bir bileşik dizin, müşterileri tam ada göre aramayı hızlandırır.

SQL Server Dizinlerini Oluşturma ve Yönetme

SQL Server'da bir dizin oluşturmak için SQL Server Management Studio'yu (SSMS) kullanırız. Daha fazla açıklamadan önce, SQL Server, PostgreSQL ve MySQL lehçeleri arasındaki farkları anlamak için blog yazımıza göz atın. SQL Server dizinlerini oluşturma ve yönetme adımlarını ele alacağım.

SQL Server dizini nasıl oluşturulur

Aşağıda hem SQL Server Management Studio (SSMS) hem de SQL kodu kullanarak dizin oluşturma adım adım kılavuzu yer almaktadır.

SSMS kullanarak dizin oluşturma

1. Nesne Gezgini'ni açın: SSMS'i başlatın ve SQL Server örneğinize bağlanın. Dizin oluşturmak istediğiniz tabloyu içeren veritabanını genişletin.

Dizini oluşturmak için Nesne Gezgini'ni açın ve tabloya gidin. Görsel: Yazar.

2. Tabloyu bulun: Tables klasörüne gidin, tablonuzu bulun ve genişletin.

Dizin oluşturmak için tabloyu bulun ve genişletin. Görsel: Yazar.

3. Indexes klasörünü açın: Indexes klasörüne sağ tıklayın ve New Index seçeneğini tıklayın.

Index klasörünü açın ve yeni bir dizin oluşturmak için sağ tıklayın. Görsel: Yazar.

4. Dizin türünü seçin: Oluşturmak istediğiniz dizin türünü seçin (ör. Clustered Columnstore Index veya Non-Clustered Index).
5. Dizin özelliklerini tanımlayın: Dizin adını belirtin, ardından Add düğmesine tıklayıp istediğiniz sütunları seçerek dizine sütun ekleyin. Benzersizlik ve sıralama düzeni gibi ek seçenekleri yapılandırın.

Dizin türünü seçin ve özellikleri tanımlayın. Görsel: Yazar.

6. Dizini oluşturun: OK düğmesine tıklayarak dizini oluşturun.

OK'e tıklayarak dizini oluşturun. Görsel: Yazar.

SQL kodu kullanarak dizin oluşturma

Dizinleri doğrudan T-SQL komutlarıyla da oluşturabilirsiniz. Aşağıda, sorgular kullanarak farklı dizin türleri oluşturma örnekleri yer almaktadır:

-- Create a non-clustered index on the Product column
CREATE NONCLUSTERED INDEX IX_Sales_Product
ON Sales (Product);

-- Create a unique index on the combination of CustomerName and OrderDate
CREATE UNIQUE NONCLUSTERED INDEX IX_Sales_CustomerName_OrderDate
ON Sales (CustomerName, OrderDate);

-- Create a clustered index on the OrderDate column
CREATE CLUSTERED INDEX IX_Sales_OrderDate
ON Sales (OrderDate);Altyapı:

SQL Server dizini nasıl silinir

Basit bir yöntemle dizini silebilirsiniz. Dikkatli olun; bir dizini kaldırmak sorgu performansını etkileyebilir.

SSMS kullanarak SQL Server dizini silme

1. Object Explorer içinde tablonun altındaki Indexes klasörüne gidin.
2. Silmek istediğiniz dizine sağ tıklayın ve Delete seçeneğini tıklayın.

Silmek istediğiniz dizine gidin. Görsel: Yazar.

3. İletişim kutusunda silmeyi onaylayın.

İletişim kutusunda silmeyi onaylayın ve OK'e tıklayın. Görsel: Yazar.

T-SQL kullanarak dizin silme

SQL Server'da bir tablodan mevcut dizinleri kaldırmak için aşağıdaki komutları kullanabilirsiniz.

-- Drop a non-clustered index
DROP INDEX IX_Sales_Product ON Sales;

-- Drop a clustered index
DROP INDEX IX_Sales_OrderDate ON Sales;Altyapı:

SQL Server dizini nasıl yeniden düzenlenir

Bir dizini yeniden düzenlemek, dizinin yaprak düzeyi sayfalarındaki parçalanmayı giderir; tam yeniden oluşturma yapmadan performansı artırır.

SSMS kullanarak SQL Server dizinini yeniden düzenleme

1. Dizine sağ tıklayın, ardından Reorganize All seçeneğine gidin.

Indexes'e sağ tıklayın, ardından Reorganize All'ı seçin. Görsel: Yazar.

2. Yeniden düzenlenecek dizin(ler)i seçin ve onaylayın.

Yeniden düzenlenecek dizinleri seçin ve onaylayın. Görsel: Yazar.

T-SQL kullanarak dizin yeniden düzenleme

SQL Server'da dizinleri yeniden düzenlemek için aşağıdaki sorguları da kullanabilirsiniz.

-- Reorganize a single index
ALTER INDEX IX_Sales_Product
ON Sales
REORGANIZE;

-- Reorganize all indexes on the table
ALTER INDEX ALL
ON Sales
REORGANIZE;Altyapı:

SQL Server dizini nasıl yeniden oluşturulur

Bir dizini yeniden oluşturmak, onu sıfırdan yeniden yaratır; parçalanmayı ortadan kaldırır ve depolamayı optimize eder.

SSMS kullanarak dizin yeniden oluşturma

1. Dizine sağ tıklayın, ardından Rebuild All seçeneğine gidin.

Dizine sağ tıklayın, ardından Rebuild All'a gidin. Görsel: Yazar

2. Yeniden oluşturulacak dizin(ler)i seçin ve onaylayın.

Yeniden oluşturulacak dizinleri seçin ve onaylayın. Görsel: Yazar

T-SQL kullanarak dizin yeniden oluşturma

Aşağıdaki sorgu, tablodaki dizinleri yeniden oluşturacaktır.

-- Rebuild a single index
ALTER INDEX IX_Sales_Product
ON Sales
REBUILD;

-- Rebuild all indexes on the table
ALTER INDEX ALL
ON Sales
REBUILD;Altyapı:

Dizin Yönetimi İçin En İyi Uygulamalar

SQL Server dizinlerini sürdürmek ve optimize etmek, en yüksek veritabanı performansını sağlamak için önemlidir. Etkili dizin yönetimi için ipuçları aşağıdadır.

• Düzenli dizin bakımı yapın: Zamanla performansı düşürebilen parçalanmayı azaltmak için dizinlerinizi düzenli olarak yeniden düzenleyin veya yeniden oluşturun. Bu görevleri otomatikleştirmek için bakım betikleri veya SQL Server Maintenance Plans gibi araçları kullanın.

• Dizin kullanımını analiz edin: sys.dm_db_index_usage_stats gibi Dinamik Yönetim Görünümlerini (DMV) kullanarak kullanılmayan veya az kullanılan dizinleri belirleyin. Kayda değer performans faydası sağlamayan dizinleri, gereksiz ek yükten kaçınmak için kaldırın.

• Aşırı dizinlemeden kaçının: Çok fazla dizin, yazma işlemlerini (INSERT, UPDATE, DELETE) yavaşlatabilir; çünkü ilgili tüm dizinlerin güncellenmesi gerekir. SELECT sorgularında, birleştirmelerde veya filtreleme koşullarında sıkça kullanılan sütunları dizinlemeye odaklanın.

• Parçalanma düzeylerini izleyin: sys.dm_db_index_physical_stats ile aşırı parçalanmış dizinleri belirleyin. %30'un altındaki parçalanmalar için yeniden düzenleyin, %30'un üzerindekiler için yeniden oluşturun.

• Filtreli dizinlerden yararlanın: Aktif kullanıcılar veya belirli tarih aralıkları gibi veri alt kümeleri üzerinde çalışan sorguları optimize etmek için filtreli dizinler kullanın.

Ayrıca, dizinlerinizi yönetirken yaygın tuzaklardan kaçınmak için aşağıdaki uygulamalara da dikkat etmelisiniz:

• Aşırı dizinleme: Her sorgu için dizin oluşturmayın; bu durum yazma işlemlerini ve depolamayı olumsuz etkiler.

• Bakımı ihmal etme: Dizinleri yeniden oluşturmayı veya yeniden düzenlemeyi ihmal etmek, parçalanmanın artmasına neden olur.

• Yazmalar için dizinlemeyi göz ardı etmek: Dizinler okumayı hızlandırırken, yazmaları belirgin şekilde yavaşlatabilir.

• Bileşik dizinleri test etmemek: Bileşik dizinlerde uygunsuz sütun sırası, dizinleri etkisiz hâle getirebilir.

SQL Server'da işlem yönetimi ve hata işleme hakkında daha fazla bilgi edinerek sorgu performansını iyileştirmek için DataCamp’in SQL Server for Database Administrators beceri yoluna göz atın.

Performansla İlgili Hususlar

Dizinler, veritabanı performansını optimize etmek için kullanışlıdır ancak bazı ödünler de beraberinde gelebilir. Etkilerini anlamak ve bunları etkili şekilde yönetmek, iyi performans gösteren bir veritabanı sistemi sürdürmek için kritik öneme sahiptir.

Dizinlerin sorgu performansına etkisi

Dizinler, veriyi getirmek için gereken zamanı azaltarak sorgu yürütme planlarını ve performansı önemli ölçüde etkiler. SQL Server'ın satırları hızlıca bulmasını sağlar, maliyetli tam tablo taramalarına olan ihtiyacı azaltır. Etkili dizinler mevcut olduğunda yürütme planlarında sıklıkla Table Scan yerine Index Seek gibi işlemler görülür.

Dizin performansını ölçmek için kullanılan araçlar şunlardır:

• sys.dm_db_index_usage_stats, dizinlerin sorgular tarafından nasıl kullanıldığını izler.

• sys.dm_db_index_operational_stats, sayfa okuma ve yazma gibi işlemsel metrikleri sağlar.

• Query Store, sorgu performansını analiz eder ve eksik veya kullanılmayan dizinlerden kaynaklanan verimsizlikleri belirler.

Örneğin, aşağıdaki sorgu, kullanıcı tanımlı tablolardaki dizinlerin nasıl kullanıldığına dair istatistikleri getirir; arama, tarama, arama (lookup) ve güncelleme ayrıntılarını içerir ve etkinliklerini ile performanslarını değerlendirmenize yardımcı olur.

-- Retrieve index usage statistics for user tables
SELECT 
    OBJECT_NAME(IXS.OBJECT_ID) AS TableName, 
    I.name AS IndexName,
    IXS.user_seeks AS Seeks,
    IXS.user_scans AS Scans,
    IXS.user_lookups AS Lookups,
    IXS.user_updates AS Updates
-- DMV providing index usage stats
FROM 
    sys.dm_db_index_usage_stats IXS         
JOIN 
    sys.indexes I ON I.OBJECT_ID = IXS.OBJECT_ID 
                 AND I.index_id = IXS.index_id
WHERE 
    OBJECTPROPERTY(IXS.OBJECT_ID, 'IsUserTable') = 1;Altyapı:

Dizin kullanımını ve depolamayı dengelemek

Dizinler veritabanı performansını artırsa da özellikle büyük veri kümelerinde disk alanı tükettikleri için depolama maliyetleri getirir. Dizinleri uygularken bu etkenleri dengelemeyi bilmek önemlidir. Daha fazla dizin, özellikle yüksek işlem hacimli ortamlarda yazma işlemlerini yavaşlatabilir. Dizin kullanımını dengelemek için aşağıdaki stratejileri göz önünde bulundurun.

• Yalnızca önemli olanı dizinleyin: WHERE, JOIN ve ORDER BY ifadelerinde sıkça kullanılan sütunları dizinlemeye odaklanın. Nadir sorgulanan sütunları dizinlemekten kaçının.

• Filtreli dizinler kullanın: Belirli sorguları optimize etmek ve alan tasarrufu yapmak için veri alt kümeleri için dizinler oluşturun.

• Sıkıştırmadan yararlanın: Dizinlerin depolama ayak izini azaltmak için veri sıkıştırmayı etkinleştirin.

• Eski verileri arşivleyin: Nadiren erişilen verileri bir arşiv tablosuna taşıyın ve ana tabloda dizinlemeyi azaltın.

Yukarıdaki stratejiler, depolama ve bakım maliyetlerini kontrol altında tutarken en uygun sorgu performansını sağlar.

Dizin Sorunlarını Giderme

Düzgün bakımı yapılmadığında dizinler zamanla bozulabilir ve veritabanı verimliliğini düşürür. Bu sorunları nasıl tespit edip çözeceğimizi tartışalım.

• Parçalanma: Zamanla dizinler parçalanır ve veri getirme verimsizleşir. Belirtileri arasında artan sorgu yürütme süreleri ve yüksek disk G/Ç bulunur. Bu sorunu çözmek için sys.dm_db_index_physical_stats ile parçalanmayı belirleyin. %30'un altındaki parçalanmalar için dizinleri yeniden düzenleyin, %30'un üzerindekileri yeniden oluşturun

• Güncellenmemiş istatistikler: Eski istatistikler, optimal olmayan sorgu yürütme planlarına yol açar. Bu nedenle, uygun dizinlere sahip olmanıza rağmen yavaş sorgular yaşayabilirsiniz. Bu problemi çözmek için kritik dizinler için istatistikleri manuel olarak güncelleyin veya AUTO_UPDATE_STATISTICS'i etkinleştirin.

• Kullanılmayan veya gereksiz dizinler: Bazı dizinler kullanılmadan kalır, depolama tüketir ve yazma işlemlerini yavaşlatır. Belirtileri, yüksek dizin bakım maliyetleri ve düşük sorgu faydasıdır. Bu sorunu çözmek için sys.dm_db_index_usage_stats ile kullanılmayan dizinleri belirleyin ve gerekli olanları kaldırın.

• Eksik dizinler: Uygun dizinlerin eksikliği tablo taramalarına neden olabilir ve sorguları yavaşlatır. Bu tür durumlarda, sorgu yürütme planlarında Index Seek yerine Table Scan görülür. Bu sorunu çözmek için eksik dizin DMV'si sys.dm_db_missing_index_details'i kullanarak fırsatları belirleyin ve sorgu desenlerine göre önerilen dizinleri oluşturun.

• Çakışan dizinler: Benzer sütun yapısına sahip birden fazla dizin, kaynakları boşa harcar; depolama kullanımını artırır ve gereksiz bakım işlemlerine yol açar. Çakışan dizinleri her zaman tek, iyi tasarlanmış bir dizinde birleştirin.

Veritabanı verimliliğini artırmak için SQL Server'da dizin sorunlarını gidermeyi daha kapsamlı öğrenmek üzere SQL Server Developer kariyer yolumuzu deneyin.

Sonuç

SQL Server dizinlerinde ustalaşmak, verimli sorgular yazarak veritabanı performansını iyileştirmek istiyorsanız önemlidir. Dizinlerle çalışırken, en iyi veritabanı performansı için dizinlerinizi yönetebilmek adına SQL Server dizin türlerini de anlamalısınız.

Yetkin bir veri analisti olmak için Associate Data Analyst in SQL kariyer yolumuzu öneririm. Reporting in SQL kursumuz da etkili veri sunumu için karmaşık raporlar ve panolar oluşturma konusunda yetkinlik kazanmanıza yardımcı olur. SQL Server becerilerinizi sergilemeyi gerektiren bir mülakata hazırlanıyorsanız, pratik yapıp mülakatta başarılı olmak için Top 30 SQL Server Interview Questions blog yazımıza göz atmanızı öneririm.