SQL Server Disk Depolama Best Practice’leri: Disk Düzeni, Formatlama, Dosya Ayarları ve Gecikme

“Sunucu yavaş” diye başlayan SQL Server performans sorunlarının çoğu sonunda depolamaya çıkar. Bellek, yavaş diskleri bir süre gizler; çünkü buffer pool’da zaten bulunan sayfaların yeniden okunması gerekmez. Ama her commit bir log yazmasını, her cache kaçırma bir veri okumasını bekler; her tempdb spill’i, yedek ve CHECKDB de aynı I/O için yarışır. Depolama yanlış kurulmuşsa hiçbir sorgu iyileştirmesi bunu tam olarak telafi edemez.

Bu rehberde önemli kararları ele alıyoruz: veri, log, tempdb ve yedek dosyalarının nasıl yerleştirileceği, volume’ların nasıl formatlanacağı, SAN, sanal makine ve bulutta nelerin değiştiği, depolama kaynaklı olayların çoğuna yol açan veritabanı dosya ayarları ve depolamanın gerçekten yeterince hızlı olup olmadığının nasıl ölçüleceği.

SQL Server Depolamayı Nasıl Kullanır?

Dosya türlerinin I/O kalıpları birbirinden çok farklıdır; ayrı volume’lara konmalarının nedeni budur:

Transaction log ayrıca dikkat ister. Bir veri sayfası daha sonra checkpoint ile yazılabilir; ama bir transaction, log kayıtları diske yazılmadan commit edilemez. Bu yüzden yavaş log yazmaları, veri diskleri ne kadar hızlı olursa olsun her insert, update ve delete’i yavaşlatır. Bunu WRITELOG bekleme türü olarak görürsünüz.

Disk Düzeni: Neler Ayrılmalı?

• Veritabanlarını C: sürücüsünden uzak tutun. Dolan bir sistem sürücüsü Windows’u ve SQL Server’ı birlikte durdurabilir; sistem diskleri de nadiren sunucudaki en hızlı depolamadır.
• Veri, log ve tempdb volume’larını ayırın. I/O kalıplarını birbirinden yalıtır, gecikmeyi dosya türüne göre teşhis etmeyi kolaylaştırır ve kontrolden çıkan bir dosyanın diğerlerinin alanını doldurmasını engeller.
• Yedekleri asla veritabanlarıyla aynı depolamada tutmayın. O depolama arızalanırsa veritabanlarını ve yedeklerini birlikte kaybedersiniz. Yedekleri ayrı bir depolamaya yazın ve sunucu dışına kopyalayın.
• tempdb’yi elinizdeki en hızlı lokal depolamaya koyun. İçeriği her yeniden başlatmada sıfırdan oluşturulduğu için lokal NVMe, failover cluster instance’ta bile uygun bir seçimdir.

Volume Formatlama: 64 KB Kuralı

SQL Server 8 KB’lık sayfalar ve 64 KB’lık extent’lerle okuyup yazar. Veri, log ve tempdb volume’larını Windows’un varsayılanı olan 4 KB yerine 64 KB allocation unit ile formatlayın. Mevcut volume’lara güvenmeden önce kontrol edin:

# Tüm volume'ların allocation unit boyutu
Get-Volume | Where-Object DriveLetter |
  Select-Object DriveLetter, FileSystemLabel, FileSystem, AllocationUnitSize, SizeRemaining, Size

# Ya da tek bir volume için: "Bytes Per Cluster : 65536" satırına bakın
fsutil fsinfo ntfsinfo E:

Allocation unit yerinde değiştirilemez. 4 KB cluster ile formatlanmış bir volume’u düzeltmek için doğru formatlanmış yeni bir volume ekleyin, veritabanı dosyalarını bir bakım penceresinde oraya taşıyın ve eskisini devreden çıkarın.

Eski sistemlerde yaygın bir sorun olan partition alignment, yeni partition’ları 1 MB’ta hizalayan Windows Server 2008 ve sonrasında otomatik olarak çözülür. Yalnızca uzun zaman önce ya da üçüncü parti araçlarla oluşturulmuş volume’larda kontrol edilmesi gerekir.

RAID, SSD ve Flash

• Production için kurumsal sınıf SSD veya NVMe kullanın. Tüketici sınıfı disklerde güç kesintisi koruması yoktur ve yazma dayanıklılıkları çok daha düşüktür.
• Doğrudan bağlı disklerde log ve yazma ağırlıklı veri için en güvenli seçim RAID 10’dur. RAID 5 veya 6, log ve tempdb için önemli olan bir yazma cezası öder.
• All-flash dizilerde RAID seviyesini genellikle dizi belirler. SQL Server için önemli olan, yoğun yük altında ölçülen gecikmedir.
• Denetleyicinin yazma önbelleğinin pil ya da flash ile korunduğundan emin olun. Korumasız bir yazma önbelleği, güç kesintisinde onaylanmış yazmaları kaybedebilir; SQL Server bunu bozulma olarak görür.

Sanal Makineler ve Bulut

• VMware: veri, log ve tempdb disklerini tek bir denetleyici yerine ayrı paravirtual (PVSCSI) denetleyicilere koyun; böylece tek bir kuyruğu paylaşmazlar.
• Hyper-V: yoğun I/O için sabit boyutlu ya da pass-through diskler kullanın ve bunları birden fazla sanal SCSI denetleyicisine dağıtın.
• Azure VM’leri: premium SSD ya da Premium SSD v2 / Ultra diskler kullanın; veri diskleri için host caching’i read-only, log diskleri için none yapın ve VM’i yalnızca çekirdeğe göre değil depolama throughput sınırına göre boyutlandırın. Bir VM boyutu, I/O’yu disklerin verebileceğinin çok altında sınırlayabilir.
• Snapshot ve replikasyon: depolama seviyesindeki snapshot’lar, uygulama tutarlı olmadıkça ve restore için test edilmedikçe yedek değildir.

Depolama Olaylarına Yol Açan Veritabanı Dosya Ayarları

Autogrowth

Büyümeyi yüzde olarak değil, sabit megabayt olarak ayarlayın. Büyük bir dosyada yüzdesel büyüme dev ve yavaş büyüme olayları demektir; çok küçük sabit büyüme ise binlerce parçaya ve log’larda çok fazla sanal log dosyasına (VLF) yol açar. Dosyaları beklenen büyümeye göre önceden boyutlandırın, autogrowth’u bir güvenlik ağı olarak görün. Boyutlandırmayı autogrowth ayarları ve VLF sayısı yazılarımızda ayrıntılı anlatıyoruz.

Instant file initialization

Bu özellik kapalıysa SQL Server her yeni veri dosyası bölgesini kullanmadan önce sıfırlarla doldurur; bu yüzden büyük bir veri dosyasını oluşturmak, büyütmek ya da restore etmek çok daha uzun sürer. Açmak için SQL Server servis hesabına Perform volume maintenance tasks yetkisini verin. Transaction log dosyaları her zaman sıfırlanır; tek istisna, SQL Server 2022 ve sonrasında 64 MB’a kadar olan log büyümelerinin instant file initialization’dan yararlanabilmesidir. Ayrıntılar için instant file initialization yazımıza bakabilirsiniz.

-- Motor servisi için instant file initialization açık mı?
SELECT servicename, service_account, instant_file_initialization_enabled
FROM sys.dm_server_services;

Sayfa doğrulama

Her veritabanında PAGE_VERIFY CHECKSUM‘ı açık tutun. SQL Server’ın depolama katmanında bozulan sayfaları bu sayede tespit eder. SQL Server 2005 ve sonrasında oluşturulan veritabanlarında varsayılandır; ama çok eski sürümlerden yükseltilmiş veritabanları hâlâ TORN_PAGE_DETECTION ya da NONE kullanıyor olabilir.

Antivirüs istisnaları

Veri, log, tempdb ve yedek dosyalarının bulunduğu klasörleri ve SQL Server sürecini gerçek zamanlı antivirüs taramasından hariç tutun. Veritabanı dosyalarının taranması I/O gecikmelerine, başarısız büyümelere ve hatta bozuk yedeklere yol açabilir.

Büyük Veritabanları İçin Filegroup’lar

Büyük veritabanlarında filegroup’lar depolamayı bir yönetim aracına dönüştürür:

• Geçmiş veriyi kendi filegroup’una koyun ve read-only yapın. Bir kez yedekleyin, sonra yalnızca aktif filegroup’ları yedekleyin.
• Soğuk filegroup’ları daha ucuz depolamaya taşıyın, sıcak veriyi flash’ta tutun.
• PRIMARY filegroup’u küçük tutun. Enterprise sürümde piecemeal restore, önce PRIMARY’yi ve güncel veriyi açıp eski filegroup’ları kullanıcılar çalışırken restore edebilir.

Tek bir filegroup’u aynı volume üzerindeki birden fazla dosyaya yaymak, kullanıcı veritabanlarında tek başına I/O kapasitesi eklemez. Dosyalar farklı volume’lardaysa ve birden fazla dosyanın allocation contention’ı azalttığı tempdb’de fayda sağlar.

Depolama Gecikmesini Ölçmek

sys.dm_io_virtual_file_stats, son yeniden başlatmadan bu yana her veritabanı dosyasının toplam okuma, yazma ve bekleme süresini gösterir. Dosya başına ortalama gecikme, SQL Server’daki en faydalı tek depolama metriğidir:

SELECT
    DB_NAME(vfs.database_id)                                        AS database_name,
    mf.type_desc                                                    AS file_type,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    CAST(vfs.io_stall_read_ms  * 1.0 / NULLIF(vfs.num_of_reads, 0)  AS decimal(10,2)) AS avg_read_ms,
    CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0) AS decimal(10,2)) AS avg_write_ms,
    vfs.num_of_bytes_read    / 1048576 AS mb_read,
    vfs.num_of_bytes_written / 1048576 AS mb_written
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_read_ms DESC;

Sonuçlar nasıl okunur:

• Log yazmaları modern flash’ta tek haneli düşük milisaniyelerde olmalıdır. Onlarca milisaniyelik sürekli log yazma gecikmesi WRITELOG beklemesi ve yavaş commit olarak kendini gösterir.
• Veri okumaları: Microsoft’un eski rehberleri 20 ms’nin üzerindeki sürekli ortalamaları yavaş kabul ediyordu. Flash depolamada bunun çok altını beklemelisiniz; sürekli çift haneli değerler incelenmelidir.
• Bekleme istatistikleriyle karşılaştırın: PAGEIOLATCH_* beklemeleri oturumların veri sayfalarının okunmasını beklediğini, WRITELOG ise log flush’larını beklediğini gösterir.

SQL Server, tek bir I/O 15 saniyeden uzun sürdüğünde error log’a bir uyarı yazar. Bu mesajın her görülüşü depolama ekibinin aranmasını gerektirir:

SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Data\SalesDB.mdf] in database id 7. The OS file handle is 0x0000000000000D3C. The offset of the latest long I/O is: 0x00000a3f4e2000

823, 824 ve 825 numaralı hatalar daha ciddidir: işletim sisteminin ya da SQL Server’ın başarısız veya bozuk bir okuma tespit ettiği anlamına gelirler. Başka bir şey yapmadan önce DBCC CHECKDB çalıştırın ve bozulma kurtarma rehberimizi izleyin.

Sık Yapılan Depolama Hataları

• Veritabanları, log’lar ve yedeklerin tek bir volume’da ya da C: sürücüsünde olması.
• Varsayılan 4 KB allocation unit ile formatlanmış volume’lar.
• Yüzdesel autogrowth ve binlerce VLF’ye sahip küçük log dosyaları.
• Instant file initialization yetkisinin verilmemesi; büyüme ve restore’ların dakikalar sürmesi.
• Gerçek zamanlı antivirüsün veritabanı ve yedek klasörlerini taraması.
• Throughput sınırı iş yükünün ihtiyacının altında kalan bir VM.
• Depolamayı yoğun dönemde ölçülen gecikmeye göre değil, katalogdaki IOPS’a göre değerlendirmek.
• Error log’daki 15 saniyelik I/O uyarılarını görmezden gelmek.

Depolama Kontrol Listesi

• Veri, log, tempdb ve yedekler ayrı volume’larda; C:’de hiçbir şey yok.
• Volume’lar 64 KB allocation unit kullanıyor.
• Yedekler ayrı depolamada ve sunucu dışına kopyalanıyor.
• Autogrowth sabit MB, dosyalar önceden boyutlandırılmış.
• Instant file initialization açık.
• Tüm veritabanlarında PAGE_VERIFY CHECKSUM açık.
• Antivirüs istisnaları tanımlı.
• Dosya başına gecikme ve I/O beklemeleri toplanıyor ve alarm üretiyor.
• Error log 833, 823, 824 ve 825 için izleniyor.