SQL Server AlwaysOn (Synchronous) Availability Groups ortamında replikasyon gecikmesini nasıl ölçersiniz?
SQL Server Availability Groups’ta Senkron Replikalar: Veri Kaybı Yok, Gecikme İhtimali Var
Senkron replikalar, veri kaybı olmadan yüksek erişilebilirlik sunar; ancak bu, sıfır gecikme garantisi verdikleri anlamına gelmez. Özellikle yüksek I/O yükü altındaki sistemlerde, senkron replikalar bile anlık olarak geri kalabilir. Bu durum genellikle gözden kaçan ancak performans üzerinde etkili olan bir gecikme tipidir.
Bu tür gizli replikasyon gecikmelerini, SQL Server performans counter ve DMV’ler aracılığıyla ölçmek ve izlemek mümkündür. Böylece sisteminizin, özellikle index yeniden oluşturma veya büyük veri yükleme gibi I/O yoğun işlemler sırasında ne kadar senkron kalabildiğini görebilir, bakım ve planlama süreçlerini daha güvenli hale getirebilirsiniz.
Neden Gecikmeyi Ölçmek Önemlidir?
Availability Groups, Yüksek Erişilebilirlik (HA) çözümlerinin temel taşlarından biridir. Ancak, yoğun işlem hacmine sahip ortamlarda altyapı kısıtları, veri aktarımında senkronizasyon gecikmeleri oluşturabilir. Bu gecikmeler, işlem gecikmesine ve beklenmedik performans düşüşlerine neden olabilir.
Senkron ve asenkron replikalar arasındaki temel fark, commit onayının ne zaman verildiğidir:
- Asenkron replika: Primary commit onayını aldıktan sonra veriyi gönderir, gecikme kolayca ölçülebilir.
- Senkron replika: Primary, commit onayını ancak secondary veriyi başarıyla aldığında verir. Teorik olarak sıfır gecikme beklenir, fakat pratikte bu her zaman geçerli olmaz.
Senkron replikalarda bu gecikmeyi tespit etmek için HADR_SYNC_COMMIT bekleme türü (wait type) ve sys.dm_os_performance_counters gibi sistem görünüşleri kullanılabilir.
Yük Altında Senkron Replika Davranışı
Yoğun çalışan AlwaysOn ortamlarında, özellikle şu işlemler sırasında HADR_SYNC_COMMIT beklemelerinin arttığı gözlenir:
- Index rebuild operasyonları
- Büyük hacimli veri yüklemeleri (bulk load)
- Toplu güncelleme (batch update) işlemleri
HADR_SYNC_COMMIT bekleme süresi artıyorsa bu şu anlama gelir:
“Senkron replikanız mevcut yükü gerçek zamanlı olarak işleyemiyor.”
Bu durumun nedenlerini anlamak, altyapı darboğazlarını (disk I/O, ağ gecikmesi, işlemci yükü vb.) doğru tespit etmenizi sağlar. Tespit sonrası şu önlemler alınabilir:
- Yoğun işlemleri bakım pencerelerine kaydırmak
- I/O yükünü düşük yoğunluklu zaman dilimlerine yaymak
- Index bakımını parçalara bölerek replikasyon yükünü azaltmak
- Ağ bant genişliği ve depolama performansını optimize etmek
Performans counters doğru yorumlayabilmek için ilk adım, dokümantasyonu incelemektir. Microsoft’un ilgili dokümanına buradan ulaşabilirsiniz:
https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-database-replica
Senkron replikalardaki gecikmeyi izlemek için kullanacağımız iki temel metrik şunlardır:
- Mirrored Write Transactions/sec
- Bu, saniye başına ikincil replika ile eşzamanlı (mirrored) olarak yazılan işlem sayısını gösterir.
- Yüksek değer, replikasyon trafiğinin yoğun olduğunu; düşük değer ise sistemin daha az işlem senkronize ettiğini gösterir.
- Transaction Delay
- Bu, bir işlemin commit edilmesinin ne kadar süre geciktiğini (milisaniye cinsinden) gösterir.
- Özellikle HADR_SYNC_COMMIT beklemelerinin arttığı dönemlerde, bu değerin yükselmesi beklenir.
- Artış trendi, genellikle disk I/O darboğazı, ağ gecikmesi veya secondary replikanın işleme kapasitesinin yetersiz olmasıyla ilişkilidir.
Bu counters birlikte yorumlayarak, hem yük hacmini hem de gecikmenin boyutunu tespit edebilir, bakım ve operasyon planlamasında bu verileri kullanabilirsiniz.
Mirrored Write Transactions/sec
Bu metrikle birlikte, trafiğin olmadığı bir sistemde Mirrored Write Transactions/sec değerinin nasıl göründüğünü inceleyerek başlayabiliriz.
Bu amaçla, SQL Server’ın işletim sistemine sunduğu performans sayaçlarını görüntülememizi sağlayan sys.dm_os_performance_counters isimli Dynamic Management View (DMV)’yi kullanacağız. Bu DMV, aynı zamanda Windows Performance Monitor aracında görülen sayaç verilerini doğrudan SQL Server üzerinden sorgulamamıza olanak tanır.
Bu yaklaşım sayesinde:
- Trafiksiz durumda temel (baseline) değerleri elde edebiliriz.
- Sonraki ölçümlerde oluşan artışları veya düşüşleri bu baseline ile kıyaslayabiliriz.
- İleri aşamada “Transaction Delay” gibi diğer metriklerle birlikte yorumlayarak, hem yük hem de gecikme ilişkisini analiz edebiliriz.
SELECT
instance_name
,CAST(cntr_value AS BIGINT) counter_value
,counter_name
FROM
sys.dm_os_performance_counters perf
WHERE object_name LIKE 'SQLServer:Database Replica%'
AND perf.counter_name LIKE 'Mirrored Write Transactions/sec%'
AND instance_name = '_Total';
Bu sorguyu çalıştırdığınızda şu sonucu döndürür:
Dokümantasyonu doğrudan esas alırsak, bu veri offline durumda olan sistemimizin son bir saniyede 877557638 işlem yaptığı anlamına geliyor — bu rakamı doğru kabul edersek, gerçekten devasa bir yük!
Ancak yaklaşık bir dakika bekleyip sorguyu yeniden çalıştırdığımızda, sonuç 877557700 oluyor. Aradaki fark bu kadar az olunca, bunun gerçek zamanlı bir işlem sayısı değil, daha çok birikimli bir sayaç olduğu izlenimini veriyor.
Büyük ihtimalle bu değer, sistemin senkron replikalarda beklemeye harcadığı toplam süreyi ya da benzer bir toplu istatistiği gösteriyor olabilir.
Bu yüzden, gerçek anlamını kesinleştirmek için bu sayaç üzerinde daha ayrıntılı bir analiz yapmamız gerekecek.
Bu sorguyu birkaç kez çalıştırdığımızda, sayacın aslında son bir saniyedeki aktiviteyi değil, son sunucu yeniden başlatmasından veya sayaç sıfırlamasından beri gerçekleşen toplam işlem sayısını izlediği açıkça görülüyor.
Bu bilgiyi dikkate alarak, Microsoft’un tanımını şu şekilde revize edebiliriz:
Mirrored Write Transactions/sec:
Primary veritabanına yazılan ve commit edilmeden önce işlem günlüğünün (log) secondary veritabanına gönderilmesini bekleyen toplam işlem sayısı (ölçüm, son sunucu yeniden başlatmasından itibaren geçerli).
Transaction Delay
Neyse ki Transaction Delay sayacı, isminden de anlaşılacağı gibi tam olarak görevini yerine getiriyor. Ayrıca tanımında, bu değerin Mirrored Write Transactions/sec sayacına bölünerek işlem başına ortalama gecikmenin hesaplanabileceği bilgisi de yer alıyor.
Bu iki sayacın bölünmesi, bize son yeniden başlatmadan bu yana gerçekleşen ortalama işlem gecikmesini verir. Ancak tek başına bu bilgi, özellikle bizim bağlamımızda, çok anlamlı olmayabilir. Daha yararlı olan, bu gecikmenin farklı sunucu yük türleriyle ne kadar ilişkili olduğunu analiz etmektir.
Ayrıca, /sec (saniye başına) eki çoğu sayaçta yanıltıcıdır; bu sayaçların büyük kısmı aslında cumulative tally tutar. Gerçek saniye başına oranı elde etmek istiyorsanız, bu matematiği sizin manuel olarak yapmanız gerekir.
Storing the Counters
Eğer bu değerleri zaman içinde saklarsak, sistemin hızı ve dayanıklılığı hakkında çok daha anlamlı bir analiz yapmamıza yardımcı olacak, dakika bazlı bir metrik elde edebiliriz.
Bunu yapmak için, bu sayaçları takip edecek bir tablo oluşturup, belirli aralıklarla bu tabloya veri yazacak bir stored procedure tanımlayacağız.
İlk adım olarak, gözlemlerimizi (counter ölçümlerimizi) tutacak tabloyu oluşturalım:
IF (SELECT object_id('dbo.AGLagObservations')) IS NULL
CREATE TABLE dbo.AGLagObservations
(
id INT IDENTITY(1,1)
,instance_name SYSNAME
,trancount BIGINT
,totaldelayMS BIGINT
,ts DATETIME2(7) DEFAULT (getdate())
)Bu sayaçlar sys.dm_os_performance_counters görünümünde aynı sütunda saklandığından, prosedürümüz bu değerleri ayrı sütunlara dönüştürmek (pivot etmek) için bir geçici tablo kullanacak ve böylece sonraki analizleri kolaylaştıracaktır.
CREATE OR ALTER PROC dbo.CreateAGLagObservation
AS
BEGIN
CREATE TABLE #t
(
instance_name SYSNAME
,counter_value BIGINT
,counter_name SYSNAME
)
--Fetch all the counters we want into one temp table
INSERT INTO #t
(instance_name,counter_value,counter_name)
SELECT
instance_name
,CAST(cntr_value AS BIGINT) counter_value
,counter_name
FROM sys.dm_os_performance_counters perf
WHERE object_name LIKE 'SQLServer:Database Replica%'
AND (perf.counter_name
LIKE 'Mirrored Write Transactions/sec%'
OR perf.counter_name LIKE 'Transaction Delay%')
--Join the temp table to itself to flatten the two
--counters into one row
INSERT INTO dbo.AGLagObservations
(instance_name, trancount,totaldelayMS)
SELECT
t1.instance_name
,t1.counter_value
,t2.counter_value
FROM #t t1 --'Mirrored Write Transactions/sec%'
JOIN #t t2 --'Transaction Delay%'
ON t1.instance_name = t2.instance_name
AND t1.counter_name
LIKE 'Mirrored Write Transactions/sec%'
AND t2.counter_name LIKE 'Transaction Delay%';
END
GOBu sayaçlar sys.dm_os_performance_counters görünümünde aynı sütun içinde tutulduğu için, saklama prosedürümüzde önce bu verileri geçici bir tabloya (temp table) alacağız.
Ardından, bu geçici tabloyu pivot ederek her bir sayacı ayrı sütunlar haline getireceğiz.
Bu yöntem, sayaç verilerini tek satırda ve kolay okunabilir bir formatta saklamamızı sağlayarak sonraki analiz adımlarını oldukça basitleştirecektir.
Artık verilerimizi toplamak için bir döngü başlatabiliriz.
Bu kodu bir SQL Agent Job içine ekleyebilir veya doğrudan SSMS üzerinden çalıştırabilirsiniz.
DECLARE @stop_time_local DATETIME2(7) = GETDATE() + 1
, @time_delay VARCHAR(10) = '00:01:00'
, @now DATETIME2(7) = GETDATE()
WHILE @now < @stop_time_local
BEGIN
EXEC dbo.CreateAGLagObservation
SELECT @now = GETDATE()
WAITFOR DELAY @TIME_DELAY
ENDHer dakika civarında çalışacak bir job tanımlayıp EXEC dbo.CreateAGLagObservation; komutunu çalıştırabilirsiniz
Gecikme Eğilimlerini Zaman İçinde Analiz Etme
Script birkaç dakika çalıştıktan sonra, topladığımız gözlemlerden lag değerini hesaplayabilir ve görüntüleyebiliriz.
Bu kod, bir CTE (Common Table Expression) kullanarak her satırı bir önceki satırla karşılaştırır ve son bir dakikada her sayacın ne kadar değiştiğini gösterir.
Ardından bu delta değerlerini birbirine bölerek, ilgili dakikadaki işlem başına ortalama gecikmeyi (delay_ms_per_tran) hesaplar.
Burada instance_name alanında tüm veritabanlarının toplamını gösterecek şekilde filtreleme yaptım. Ancak stored procedure içinde bu filtreyi eklemedim, böylece istenirse her veritabanı için gecikme ayrı ayrı izlenebilir.
;WITH a AS (
SELECT
begin_time = lag(CAST( ts AS DATETIME2(0)))
OVER (PARTITION BY instance_name
ORDER BY id
)
,end_time = CAST( ts AS DATETIME2(0))
,instance_name
,id
,trancount_delta = trancount
- lag (trancount)
OVER (PARTITION BY instance_name
ORDER BY id
)
,totaldelayMS_delta = totaldelayMS
- lag (totaldelayMS)
OVER (PARTITION BY instance_name
ORDER BY id
)
FROM dbo.AGLagObservations
)
SELECT TOP 100
CAST(
CASE WHEN trancount_delta > 0
THEN totaldelayMS_delta * 1.0 /trancount_delta
ELSE 0 END
AS NUMERIC(19,2)) AS delay_ms_per_tran
,begin_time
,end_time
,trancount_delta
,totaldelayMS_delta
FROM a
WHERE instance_name = '_Total'
ORDER BY id DESC
Bu tabloyu inceleyerek, senkron replikasyon gecikmesinin sistem yükü altındaki davranışını net şekilde görebiliyoruz.
Normal koşullarda, arka planda sürekli çalışan INSERT döngüsü sırasında gecikme değerleri 0.5 – 1.1 ms/işlem seviyelerinde sabit kaldı.
Ancak sistemi zorlamak amacıyla gerçekleştirilen index rebuild gibi yoğun I/O işlemleri sırasında gecikme, belirgin şekilde artış gösterdi. Özellikle belirli zaman dilimlerinde gecikmeler 10 ms, 18 ms ve hatta 21 ms’nin üzerine çıktı.
Bu veriler, index yeniden oluşturma işlemlerinin senkron replikasyon üzerinde doğrudan etkili olduğunu ortaya koyuyor. Milisaniyenin altındaki gecikmeler, bu tür bakım operasyonları sırasında kısa süreli ancak yüksek seviyelere tırmanabiliyor. Bu nedenle, yüksek I/O gerektiren işlemler mümkün olduğunca düşük yük dönemlerinde veya optimize edilmiş bakım planlarıyla uygulanmalı.
Özet
Bu çalışmada, SQL Server’ın kendi sayaçlarını kullanarak Availability Group içindeki senkron replikalar arasındaki gecikmenin nasıl ölçülebileceğini gösterdik.
Senkron commit, veri kaybı yaşanmayacağını garanti etse de gecikmesiz işlem garantisi vermez — özellikle sistem yük altındayken, replikaları senkron tutmanın maliyeti önemli boyutlara ulaşabilir.
Bu maliyeti ölçmek, altyapının yükle ne kadar başa çıkabildiğini görmek için bize net bir pencere sunar; özellikle indeks bakımı veya veri yükleme gibi IO yoğun operasyonlarda bu fark daha belirgindir.
Zaman içinde bu tekniği kullanmak:
- Beklenmedik yavaşlamaların nedenlerini açıklamaya,
- AlwaysOn performansı hakkındaki varsayımları doğrulamaya,
- Arka plan görevlerini daha güvenli şekilde zamanlamaya
yardımcı olur.
Ayrıca bu, bize “sağlıklı” bir secondary replikanın sadece bağlı olan değil, primary ile senkron kalabilen replika olduğunu hatırlatır.
Bu tip izleme yalnızca sorun sonrası hata ayıklamaya değil, aynı zamanda operasyonel planlama, değişiklik yönetimi ve bakım rutinlerinin güvenli şekilde devreye alınması süreçlerine de katkı sağlar.
Replikasyon performansı, sistem sağlığının önemli bir parçasıdır ve bu yöntem bize onu pratik bir şekilde takip etme imkânı verir.