SQL Server 2005+ ile Index Maintenance

SQL Server üzerinde veritabanı geliştirirken indexleme yapmak performans için kaçınılmazdır. Ancak bazı durumlarda bu indexler kendilerini otomatik olarak güncellemezler. Ve kayıtlar arttıkça performans düşmeye başlar.

İşte bu gibi durumlarda örneğin iki tabloyu JOIN’lerken sorguların düzgün çalışmadığını görebiliriz. Veya kayıtların işlenmesinin çok uzun sürdüğüne şahit olabiliriz. Bunun sebebi veritabanı üzerindeki index tablolarının çok şişmiş olmasıdır. Buna parçalanma (fragmentation) denilmektedir. Bu durumu çözmek için iki yöntem kullanılabilir; index tablosunu silmeden reorganizasyon (REORGANIZE) ve silip yeniden inşa (REBUILD). Reorganizasyon işlemi Windows üzerindeki Disk Birleştiricisi (Defrag) adlı uygulamanın yaptığı işleme benzer. Önce Disk Birleştiricisi üzerine kısa bir gözden geçirme yapalım;

Dosyalar disk üzerinde bir bütün olarak bulunmaz, çeşitli kümeler üzerinde belli miktardaki bölümleri gruplar halinde bulunabilir. Bu grupların miktarları dosya sisteminin türüne göre değişir. NTFS, FAT32, v.b. şeklinde farklı tiplerde, dosya sistemleri mevcuttur. Konumuzun dışına çıkmamak için her dosya sisteminin de minimum bir boyutu olduğunu söyleyelim ve asıl konumuza devam edelim. Minimum birim büyüklüğü 4 kB olan bir dosya sisteminde 300 kB büyüklüğündeki bir dosya bu birimlere ardışık olarak yazılamayabilir. Böylece dosya sisteminde dosyanın hangi parçasının ne kadarının hangi sektörlere yazıldığının listesi (index) tutulur. Bilindiği gibi bir diskteki parçalanma büyüdükçe diskin okuma hızı azalmaya başlar, çünkü bir dosyanın onlarca parçası diskin herhangi konumlarına dağılmış olabilir. İşte işletim sistemi de Disk Birleştiricisi gibi bir uygulama ile bu parçaları birleştirerek dosya parçalarının sayısını minimize etmeye çalışır, bunu yaptıkça da indexleri düzenler, amaç okuma sırasında disk iğnesinin bambaşka yerlere konumlanarak kaybettiği zamanı kazanmaktır. Ancak hiçbir zaman kusursuz bir birleştirme gerçekleştirilemez. Kusursuz birleştirme, ancak dosyaların yeni formatlanmış başka boş bir diske kopyalanmasıyla gerçekleştirilebilir. Bu da diğerine göre daha fazla zaman almaktadır.

Aynen bu şekilde veritabanları da kayıtları yani satırları indexlemektedir. Burada amaç milyonlarca kayıt içeren tablolara en kısa sürede ulaşmaktır. Örneğin 30 alan içeren bir tabloda 4. alana göre sıralama veya arama yapmak için milyonlarca kaydı okuyarak 4. alanındaki değeri karşılaştırmak yerine bu kayıtları 4. alana göre indexlemek ve istenildiğinde bu indexleri kullanarak hızlıca konumlanmak daha az maliyetlidir. Ancak artan kayıtlarda dosya sistemindeki farklara benzer bir durum olarak, index tiplerindeki farklar göze hissedilmeye başlamaktadır.

Bu noktaya gelindiğinde yapılacak şey indexlere el atmaktır. Ancak REORGANIZE mı REBUILD mi etmeliyiz kararı tecrübe gerektiren bir karardır. REBUILD işlemi daha uzun sürmektedir, milyonlarca kayıt üzerinde veritabanının geçici bir süreliğine epey yavaşlamasına sebep olacaktır ve tüm indexler silinecektir. REORGANIZE ise daha hızlı yapılmaktadır, ama parçalanmanın nispeten daha az olduğu indexlerde yapılması daha iyidir, çok fazla parçalanma olan indexlerde etkisi azdır.

Bu kadar açıklamadan sonra SQL Server üzerinde bir veritabanında bu işlemi nasıl yapabileceğimize bakalım. Aşağıdaki küçük sorgu ile hangi indexlerin ne oranda parçalandığını, hangilerine dokunmaya gerek olmadığını, hangilerini yeniden organize etmemiz gerektiğini ve hangilerini yeniden inşa etmemiz gerektiğini tespit edebilir, bu işlemleri gerçekleştirmek için çalıştırmamız gereken SQL sorgularını üretebilirsiniz:

USE [XXX]

DECLARE @MinFragmentationInPercent FLOAT,
        @MaxFragmentationInPercent FLOAT;

SELECT @MinFragmentationInPercent = 5,
       @MaxFragmentationInPercent = 30;

SELECT
  CASE
    WHEN avg_fragmentation_in_percent BETWEEN @MinFragmentationInPercent AND @MaxFragmentationInPercent THEN
      'ALTER INDEX ' + i.name + ' ON ' + t .name + ' REORGANIZE;'
    WHEN avg_fragmentation_in_percent > @MaxFragmentationInPercent THEN
      'ALTER INDEX ' + i.name + ' ON ' + t .name + ' REBUILD with(ONLINE=ON);'
    ELSE
      'PRINT ''INDEX ' + i.name + ' ON TABLE ' + t .name + ' ' + CAST(avg_fragmentation_in_percent AS VARCHAR) + '%'''
  END AS SQL_To_Execute
FROM
  sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a INNER JOIN
  sys.indexes AS i ON a.object_id = i.object_id AND a.index_id = i.index_id INNER JOIN
  sys.tables AS t ON t.object_id = i.object_id
WHERE
  (i.name IS NOT NULL);

Burada [XXX] veritabanı üzerinde ki tüm indexlerde, parçalanma oranı %5 – %30 arasındakileri yeniden organize eden, %30’dan büyük olanları yeniden inşa eden ve %5’in altındakilerin de parçalanma oranını görüntüleyen sorgular üretilmektedir. Bu sorguyu bir veritabanında çalıştırdığımda çıkan çıktının bir kısmı aşağıda görülmekte:

...
PRINT 'INDEX PK__aspnet_P__CD67DC5900200768 ON TABLE aspnet_PersonalizationAllUsers 0%'
PRINT 'INDEX PK__aspnet_P__CD67DC5900200768 ON TABLE aspnet_PersonalizationAllUsers 0%'
ALTER INDEX aspnet_Applications_Index ON aspnet_Applications REBUILD with(ONLINE=ON);
ALTER INDEX PK__aspnet_A__C93A4C9820C1E124 ON aspnet_Applications REORGANIZE;
PRINT 'INDEX aspnet_Membership_index ON TABLE aspnet_Membership 2.27273%'
...

Buna göre yaklaşık %0 parçalanan PK__aspnet_P__CD67DC5900200768 ve %2,27 oranında parçalanan aspnet_Membership_index üzerinde bir işlem yapmaya gerek yok ama %30 üzerinde parçalanan aspnet_Applications_Index yeniden inşa edilmeli, %5-%30 arası parçalanan PK__aspnet_A__C93A4C9820C1E124 yeniden organize edilmeli. Bu işlemleri yapacak sorgular çıktı olarak görülüyor. Tek yapmak gereken bunları çalıştırmak; Kopyala yapıştır ile bunu basitçe yapabiliriz.