Mssql server veritabanlarında tablolar üzerinde yapılan insert,delete,update işlemleri zamanla index yapımızın bozulmasına sebep olacaktır. Buda bize performans anlamında ciddi sıkıntılara sebebiyet verecektir. Microsoft SQL Server’da rebuild ve reorganize işlemleri, indekslerin bakımını yapmak için kullanılan iki farklı yaklaşımdır. Her iki işlem de indekslerin performansını iyileştirerek sorgu sürelerini kısaltmaya yardımcı olur, ancak her biri farklı şekilde çalışır ve farklı durumlarda tercih edilir. İşte her iki işlemin detaylı açıklamaları:
1. İndex Rebuild
Rebuild işlemi, bir indeksin sıfırdan yeniden oluşturulması anlamına gelir. Bu işlem, indeksin bütünlüğünü yeniden sağlar ve fiziksel olarak yeniden düzenler.
Rebuild oluşturma işlemini yarıda iptal ederseniz, geri alınması gerekir (ve çevrimdışı yapılıyorsa, bu biraz zaman alabilir).
Ne Zaman Yapılır?
- İndeksin yüksek oranda bozulduğu durumlarda (örneğin, %30’dan fazla fragmentasyonu varsa).
- Genellikle, performans sorunları yaşandığında veya veritabanı büyüdüğünde tercih edilir.
Süreç:
- İlgili indeksin tamamen silinmesi ve ardından yeniden oluşturulması yapılır.
- Rebuild işlemi, veritabanı tabloyu kilitleyebilir. Bu nedenle, genellikle bakım penceresinde yapılması önerilir.
- İndeksin tüm verileri sıralanır ve disk alanı daha verimli bir şekilde yeniden tahsis edilir.
Avantajları:
- İndeksin bozulmuş yapısını tamamen ortadan kaldırır.
- Veri sayısı arttıkça indeks performansını artırabilir.
- Daha az fragmantasyon, daha hızlı veri erişimi sağlar.
Dezavantajları:
- Zaman alıcı olabilir, özellikle büyük veritabanlarında.
- Disk alanı kullanımı geçici olarak artabilir (çünkü eski ve yeni indeks bir süre paralel olarak var olabilir).
- Yüksek sistem kaynağı tüketebilir.
-- Tek bir indeksi rebuild etmek için
ALTER INDEX [index_name] ON [table_name] REBUILD;
ALTER INDEX Index_Name ON Table_Name REBUILD WITH (FILLFACTOR = 70)
Genel Olarak:
- Clustered index rebuild sırasında tablo tamamen kilitlenebilir ve veri yazma işlemleri engellenebilir.
- Non-clustered index rebuild sırasında tablo üzerinde veri yazma işlemleri daha az etkilenir, ancak indeks üzerinde kilitler olabilir ve bu da yazma performansını geçici olarak etkileyebilir.
- Clustered Index olan tablolarda, rebuild işlemi sırasında tablo kilitlenebilir. Bu, tabloyu tamamen kilitleyerek, veri ekleme, silme veya güncelleme işlemlerine engel olabilir. Özellikle, clustered index rebuild sırasında tablo üzerinde Shared Lock veya Exclusive Lock türleri uygulanabilir.
- Non-clustered index rebuild işlemi sırasında genellikle sadece ilgili indeksin üzerinde işlem yapılır, tablonun tamamı kilitlenmez, ancak yine de belirli indeks blokları üzerinde kilitler olabilir.
Not: Shared Lock ve Exclusive Lock SQL Server’da veri erişimi sırasında kullanılan iki farklı kilit türüdür. Her ikisi de veritabanı yönetim sistemi (DBMS) tarafından, verilerin doğru ve tutarlı bir şekilde erişilmesini sağlamak amacıyla kullanılır. Bu kilitler, işlem izolasyonu ve veri bütünlüğü için kritik öneme sahiptir. Shared Lock, verinin okunmasına izin verir ancak değiştirilmesine izin vermez. Bu kilit türü, bir işlem veriyi okurken başkalarının da aynı veriyi okumasına izin verir, fakat veriye yazma işlemi yapılmasına engel olur. SELECT sorguları, genellikle Shared Lock kullanır. Örneğin, SELECT sorguları veriyi okurken başka işlemler de aynı satırları okuyabilir.
Not: Exclusive Lock, verinin hem okunmasını hem de değiştirilmesini engeller. Bir işlem bir veriye Exclusive Lock uyguladığında, diğer işlemler bu veriye ne okuma ne de yazma yapabilir. Yani, bu kilit, veriyi tamamen kontrol altına alır. UPDATE, DELETE gibi işlemler genellikle Exclusive Lock kullanır.
Not: İndeks rebuild işlemi sırasında LDF (Transaction Log) dosyasının büyümesi mümkündür. Ancak, reorganize işlemi genellikle daha az log kullanır. Rebuild işlemi, tam yeniden yapılandırma yaptığı için büyük miktarda transaction log verisi oluşturur. Bu, log dosyasının büyümesine yol açabilir. Full recovery model kullanıyorsanız, rebuild işlemi sırasında log dosyasındaki kayıtlar birikir ve log dosyasının büyümesine neden olur. Bu durumda, log dosyasının büyümesini izlemek ve gerektiğinde log yedeği almak önemlidir. Log dosyası büyürse backup alınması ve ardından shrink işleminin yapılması önerilmektedir.
Not: Reorganize işlemi genellikle daha küçük bir etki yapar. Çünkü bu işlem, indeks üzerinde küçük düzenlemeler yapar ve genellikle transaction log üzerindeki etki daha az olur.
Not: Rebuild işlemini yarıda kesersen indeks bozulabilir veya silinebilir. Reorganize işlemi daha güvenlidir, işlem tamamlanmasa bile indeks kısmen optimize edilir. İşlem yaparken OLTP sistemlerde (canlı veritabanlarında) dikkatli olmalıyız, çünkü bu işlemler sorgu performansını geçici olarak düşürebilir.
Eğer büyük bir veritabanı üzerinde çalışıyorsan, online index rebuild veya periyodik bakım penceresi kullanarak bu riskleri minimize edebilirsin.
Not: Index rebuild işlemlerinde performans kaybı daha fazla olduğu için sıkıntı yaşayan sistemlerde Index Rebuild öncesi AG’yi asenkron moduna alınır. Otomatik failover işlemi veritabanında herhangi bir sorun anında veya transaction log dolması gibi sebeplerden dolayı geçmez sunucuda bulunan donanımsal bir hatadan dolayı failover işlemi gerçekleşmektedir.
2. İndex Reorganize
Reorganize işlemi, indeksin verilerini yeniden düzenler, ancak bunu daha hafif bir şekilde yapar. Bu işlemde indeks sıfırdan oluşturulmaz, sadece mevcut indeks üzerinde küçük düzeltmeler yapılır.
Bu işlem her zaman çevrimiçidir ve iptal ederseniz olduğu yerde durabilir (geri almak için devasa bir işlemi yoktur).
Ne Zaman Yapılır?
- İndeksin daha düşük oranda bozulduğu (genellikle %5 ile %30 arasında fragmentasyon varsa) durumlarda tercih edilir.
- Veritabanı yoğunluğunun düşük olduğu zamanlarda, performansı hızlı bir şekilde artırmak için kullanılır.
- Reorganize işlemi, indeksin içindeki veri bloklarını defragment eder ve boş alanları temizler.
- Tablo kilitlemesi yapmaz, dolayısıyla işlem genellikle çalışan sistemlerde daha az etki yaratır.
- Sadece belli bloklar üzerinde işlem yapıldığı için, genellikle daha hızlıdır ve daha az kaynak kullanır.
Avantajları:
- Daha az disk alanı ve işlem gücü gerektirir.
- Çalışan sistemlerde, çevrimiçi yapılabilir.
- Küçük fragmentasyonlar için etkili çözüm sunar.
Dezavantajları:
- Büyük miktarda fragmentasyon olduğunda verimli olmayabilir.
- İndeksin yapısını tamamen yeniden düzenlemez, sadece mevcut düzeni iyileştirir.
-- Tek bir indeksi reorganize etmek için
ALTER INDEX [index_name] ON [table_name] REORGANIZE;
Rebuil ve Reorganize işlemi SSMS arayüzünde yapılabilir. İlgili tablo altında bulunan index yapımıza gelip sağ tıklayıp Reorganize-Rebuild işlemi yapılabilmektedir.
Hangi işlemi yapacaksak ilgili bölüme tıkladıktan sonra Script bölümünden işlemin script’i alınır.
Gelen scriptte ilgili bölümler doldurulabilir. Yeni bir index oluşturuluyormuş gibi fill factor ve diğer ayarlamalar yapılabilir.
USE [AdventureWorks2014]
GO
ALTER INDEX [AK_Person_rowguid] ON [Person].[Person] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Tablo altında bulunan tüm indexlerin rebuild ve reorganize edilmesi isteniyorsa aşağıdaki ekran resmindeki bölümlere tıklanır.
Rebuild ve Reorganize Karşılaştırması
| Özellik | Rebuild | Reorganize |
| Fragmentasyon Seviyesi | Yüksek fragmentasyon (> %30) | Düşük – orta fragmentasyon (%5 – %30) |
| Durum Bilgisi | kesintiye uğrarsa sıfırdan yeniden başlamak gerekir | İşlemin kesintiye uğradığı yerden yeniden başlatılabilir |
| Performans İyileştirmesi | Tam iyileştirme sağlar | Küçük iyileştirmeler sağlar |
| Zaman ve Kaynak Kullanımı | Yüksek kaynak tüketir, uzun sürer | Düşük kaynak tüketir, daha hızlı, yalnızca yeniden düzenlenen bloklar için trn log üretir. |
| Veritabanı Erişilebilirliği | Tablo kilitlenebilir. Hem çevrimiçi hem de çevrimdışı olabilir (ayrıca SQL Server sürümüne de bağlıdır) | Çevrimiçi yapılabilir. |
| Disk Alanı Kullanımı | Geçici olarak artabilir. yeni dizin oluşturmak için yeterli boş alana ihtiyaç var | Az artış, verimli kullanılır. yerinde çalışır, bu nedenle ekstra alana gerek yoktur |
| İndeks Yapısı | Tamamen yeniden oluşturulur. İstatistikler otomatik güncellenir. | Sadece yeniden düzenlenir. İstatistik güncellemesi manuel olarak yapılmaktadır. |
Dizinlerinizdeki parçalanma düzeyini belirledikten sonra parçalanmayı ele almak için kullanabileceğiniz iki farklı seçeneğiniz olur. Dizini ya yeniden düzenleyebilirsiniz ya da yeniden oluşturabilirsiniz. Microsoft’un yönergeleri, parçalanma düzeyi < %10 ise hiçbir şey yapmamanızı, %10 ile %30 arasındaysa yeniden düzenlemenizi ve > %30 ise yeniden oluşturmanızı önermektedir.
Kısacası;
- Rebuild işlemi, yüksek düzeyde fragmentasyon yaşanıyorsa ve veritabanı üzerinde ciddi performans düşüşü varsa tercih edilmelidir.
- Reorganize işlemi, daha düşük seviyelerdeki fragmentasyonu gidermek ve işlem süresini kısa tutmak için daha uygundur. Ayrıca, veritabanı çevrimiçi iken yapılabilir.
1-Aşağıdaki sorgu yardımıyla veritabanı altında bulunan tablolardaki tüm index’lerimizin bozulma oranlarını bulmuş olacağız. Hızlı sonuç dönüyor.
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
2-İkinci komutumuz ise tablodaki indexs’in türüne ve fragmentation oranlarına ulaşabiliriz.
SELECT
OBJECT_NAME(IX.object_id) AS TableName,
IX.name AS IndexName,
IX.type_desc AS IndexType,
IPS.avg_fragmentation_in_percent AS Fragmentation
FROM
sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') AS IPS
INNER JOIN sys.indexes AS IX
ON IPS.object_id = IX.object_id
AND IPS.index_id = IX.index_id
WHERE
IPS.avg_fragmentation_in_percent > 10 -- Bu yüzdeyi istediğiniz gibi değiştirebilirsiniz
ORDER BY
IPS.avg_fragmentation_in_percent DESC;
Bu sorgu tüm indeksleri kontrol eder, sadece non-clustered indekslerle sınırlamak isterseniz, IX.type_desc = 'NONCLUSTERED' filtresini ekleyebilirsiniz.
3. Aşağıdaki komut veritabanı bazında çalışmaktadır. Rebuild oranımız 30’ı geçtiğinde oluşacak sorgu olarak karşımıza çıkmaktadır. Bize ilgili indexslerin rebuild komutlarını vermektedir.
SELECT indexstats.avg_fragmentation_in_percent,type_desc,ind.name, 'ALTER INDEX '+ind.name+ ' ON ['+ OBJECT_SCHEMA_NAME(ind.object_id)+'].'+ OBJECT_NAME(ind.object_id) +' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)'
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sysobjects so ON so.id=ind.object_id
INNER JOIN sysusers su on su.uid=so.uid
WHERE indexstats.avg_fragmentation_in_percent > 30
AND ind.name is not null --HEAP'lerde index olmuyor!...
ORDER BY indexstats.avg_fragmentation_in_percent DESC
REBUILD PARTITION = ALL ifadesi, tüm bölümlerin yeniden oluşturulmasını sağlar. Yani, index’in veya tablonun tüm bölümleri yeniden yapılandırılır ve optimize edilir. Bu, genellikle büyük ve partitioned tablolarla çalışan veritabanlarında, her bölümdeki fragmantasyonu gidermek ve performansı artırmak için kullanılır.
Yukarıda görülen fragmentation kısmındaki değerler göre tablo altında bulunan index yapımıza müdahale edebiliriz.
Rebuild ve Reorganize çevrimiçi veya çevrimdışı olabilir (veritabanı sürümüne bağlı olarak) Dikkat edilmesi gereken hususlar:
- Reorganize her zaman çevrimiçi bir işlemdir, SQL Server’ın hangi sürümünü kullanıyor olursanız olun. Bir şema mod kilidi gerektirmez, bu nedenle daha iyi eşzamanlılık sağlayabilir. Yeniden düzenleme yalnızca dizinin yaprak düzeyini birleştirir . Büyük tablolarda rebuild’dan daha uzun sürebilir. Ancak yukarıda da söylediğim gibi, bir süreliğine yeniden düzenleyebilmeniz ve ardından büyük bir geri alma işlemiyle karşılaşmadan durabilmeniz güzeldir.
Enterprise Edition kullanıyorsanız , Paralellik kullanarak rebuild oluşturmayı benimseyin ve bakım pencereniz sırasında kişilerin veritabanına erişmesi gerekirse, buna izin veren dizinler için ONLINE seçeneğini kullanın.
Database Mirroring veya AlwaysOn Kullanılabilirlik Gruplarınız varsa , özellikle rebuild işlemlerinde dikkatli olunması gerekmektedir. Dizin bakımıyla birden fazla G/Ç üretmek kolaydır ve bu, secondary veya mirror database o kadar geride bırakmak anlamına gelebilir ki yetişemezler ve kopmaya sebebiyet vereceklerdir.
Bu makalede index reorganize ve rebuild işlemlerini detaylı bir şekilde görmüş olduk.
Başka bir makalede görüşmek dileğiyle..
Onlar, siz birbirinizi namaza çağırdığınızda onu alay ve oyun (konusu) edinirler. Bu, gerçekten onların akıl erdirmeyen bir topluluk olmalarındandır. Maide Suresi, 58. Ayet
