İndeks bakımı kararı: parçalanma, doluluk, REBUILD, FILLFACTOR, kullanılmayan indeks

Sabit eşikle değil, kanıtla karar vermek: hangi indeks bakım ister, hangisi silinir, hangisine hiç dokunulmaz.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Eski kural: yüzde 5 ve 30

Yıllarca "%5-30 yeniden düzenle, %30 üstü yeniden kur" denildi. Microsoft'un güncel rehberi bu sayıları anmıyor ve kararın yalnız sabit parçalanma ya da doluluk eşiğine dayanmaması gerektiğini yazıyor: önce bakımın gerçekten sorgu performansını iyileştirdiğini ölç, yalnız etkilenen indekslere dokun.

Burası kurgu kopyaBu tarayıcı motoru gerçek sistem görünümü çalıştırmaz. T-SQL modunda sys.dm_*, sys.query_store_*, msdb.dbo.* yazımı, hayali bir portal için elle kurulmuş tablolara yönlenir. Yazım ve sütun adları gerçekle aynıdır, ama yalnız seçili sütunlar var ve sayılar ölçüm değildir. Parametre alan görünümlerde (sys.dm_db_index_physical_stats) parametreler yok sayılır, bütün satırlar döner; süzmek için WHERE yaz. Gerçek sunucuda aynı sorgu için yetki gerekir: SQL Server 2022 ve sonrasında VIEW SERVER PERFORMANCE STATE, öncesinde VIEW SERVER STATE.

İşlem	Ne yapar	Bedel
`REORGANIZE`	Yaprak düzeyini düzenler, sayfaları doluluk değerine sıkıştırır. Her zaman çevrim içi	Az kaynak; rehberin varsayılan önerisi
`REBUILD`	İndeksi yeniden kurar, bütün düzeylerde parçalanmayı giderir, anahtar istatistiklerini tarayarak günceller	İki kopya kadar alan; çevrim dışıysa nesne kilidi

Önce istatistiği deneYeniden kurma sonrası sorgu hızlandıysa sebep çoğu zaman parçalanma değil, güncellenen istatistiktir. UPDATE STATISTICS ... WITH FULLSCAN çok daha ucuzdur; önce onu sına.

Parçalanma ve doluluk

avg_fragmentation_in_percent sıra dışı sayfa oranıdır. avg_page_space_used_in_percent sayfa doluluğudur; rehbere göre birçok iş yükünde doluluğu artırmak parçalanmayı azaltmaktan daha çok kazandırır, çünkü daha az sayfa daha az I/O ve bellek demektir.

SELECT OBJECT_NAME(ips.object_id) AS tablo, i.name AS indeks,
       ips.avg_fragmentation_in_percent AS parcalanma,
       ips.avg_page_space_used_in_percent AS doluluk,
       ips.page_count AS sayfa
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
ORDER BY ips.page_count DESC;

Küçük indeks

Küçük indeksi yeniden kurmak parçalanmayı çoğu zaman düşürmez, yüzde 66 görünse bile. portal_oturum 40 sayfa: dokunma.

FILLFACTOR ve sayfa bölünmesi

Dolu sayfaya satır gelince motor satırların yaklaşık yarısını yeni sayfaya taşır: pahalıdır ve parçalanma üretir. FILLFACTOR yaprak sayfalarda boşluk bırakır. 0 ve 100 aynıdır: tam dolu. Bedeli okumadır: 50 değeri okuma I/O'sunu ve belleği iki katına çıkarır. Rehber, 100 dışında bir değeri yalnız bölünmesi yüksek indekslerde, örneğin ardışık olmayan GUID öncelikli indekste önerir. Sona eklenen artan anahtarda (IDENTITY) boşluk kullanılmaz, işe yaramaz.

-- Çalışmaz, başvuru: motor fiziksel sayfa tutmaz
ALTER INDEX IX_customers_guid ON dbo.bank_customers REBUILD WITH (FILLFACTOR = 85);
ALTER INDEX IX_txns_hesap_zaman ON dbo.bank_txns REORGANIZE;

Kullanılmayan indeks

Yazma maliyeti verir, okuma getirmez. Ama iki tuzak var:

Satırı olmayan indeks: Hiç dokunulmamış indeksin sys.dm_db_index_usage_stats içinde satırı olmayabilir; LEFT JOIN şarttır.
Gözlem penceresi: Sayaçlar motor başlayınca sıfırlanır. Dün açılan sunucuda sıfır okuma, aylık rapor indeksinin gereksiz olduğunu göstermez. sys.dm_os_sys_info.sqlserver_start_time bak.

SELECT OBJECT_NAME(i.object_id) AS tablo, i.name AS indeks, COALESCE(s.user_updates, 0) AS yazma
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED' AND i.is_unique = 0
  AND COALESCE(s.user_seeks, 0) + COALESCE(s.user_scans, 0) + COALESCE(s.user_lookups, 0) = 0
ORDER BY tablo, indeks;

user_updates satır sayısı değil işlem sayısıdır: 1.000 satır silen tek ifade 1 artırır.