Teşhis Metodolojisi: Wait Statistics, DMV'ler ve Extended Events ile "Veritabanı Yavaş" Şikâyetini Çözmek

Sürüm notu: Bu yazıdaki SQL (Structured Query Language) örnekleri SQL Server 2025 (17.0.5035.5, Cumulative Update 10) üzerinde çalıştırılmıştır; .NET örneği .NET 10, Microsoft.Data.SqlClient 7.1.1, Dapper 2.1.89, OpenTelemetry 1.20.1 ve OpenTelemetry.Instrumentation.SqlClient 1.20.2 ile derlenip denenmiştir. Laboratuvar emülasyonla çalışan ortak bir container olduğu için süreleri hız iddiası olarak değil, yalnız hangi beklemenin baskın olduğunu göstermek için kullanıyoruz.

Önce Tahmin Et: Bu Sorgu Ne Bekledi?

Destek ekibi yazıyor: "Sipariş ekranı bazen çok yavaş açılıyor." Ekranın arkasında iki sorgu var: bir siparişin detayı ve bekleyen siparişlerin listesi. Aynı session'da ikisini çalıştırıp session'ın kendi wait istatistiklerine bakıyoruz:

SELECT OrderId, Status FROM dbo.Orders WHERE OrderId = 999001;
SELECT TOP (20) OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE Status = 'Pending' ORDER BY CreatedAt DESC;

SELECT TOP (6) wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@SPID
ORDER BY wait_time_ms DESC;

İkinci sorgu bir milyon siparişlik tabloyu index'siz tarıyor. Yavaşlığın sebebi o mu? Yoksa disk mi, CPU (Central Processing Unit) mu?

wait_type|waiting_tasks_count|wait_time_ms
---------|-------------------|------------
LCK_M_S|1|3970
CXSYNC_PORT|11|114
LATCH_EX|24|71
LATCH_SH|7|6
SESSION_WAIT_STATS_CHILDREN|9|1
RESERVED_MEMORY_ALLOCATION_EXT|122|0

Session'ın beklediği zamanın neredeyse tamamı tek bir LCK_M_S beklemesi: ilk sorgu, başka bir session'ın değiştirip commit etmediği siparişi okumak için bekledi. Tarama yapan ikinci sorgu paralel çalıştı (CXSYNC_PORT, latch'ler) ama beklemenin küçük bir kısmı. "Yavaş" şikâyetinin cevabı bu örnekte index değil, blocking. Tahminle index ekleseydik, sorun yerinde dururdu. Bu yazı, bu tür şikâyete her adımda bir ölçümle yaklaşan bir yöntem kuruyor: wait'ler, sorgular, planlar, düzeltme ve doğrulama.

Wait Statistics: Sunucu Neyi Bekliyor?

SQL Server bir task'ın çalışamadığı her anı bir wait type ile kaydeder. Sunucu seviyesinde birikmiş toplamlar sys.dm_os_wait_stats'ta durur; ama bu sayılar sunucu açıldığından beri birikir. Anlamlı olan, iki an arasındaki fark (delta):

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #before
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:05:00';

SELECT w.wait_type,
       w.waiting_tasks_count - b.waiting_tasks_count AS waits,
       w.wait_time_ms - b.wait_time_ms AS wait_ms,
       w.signal_wait_time_ms - b.signal_wait_time_ms AS signal_ms
FROM sys.dm_os_wait_stats AS w
JOIN #before AS b ON b.wait_type = w.wait_type
WHERE w.wait_time_ms - b.wait_time_ms > 0
  AND w.wait_type NOT IN (N'SLEEP_TASK', N'LAZYWRITER_SLEEP', N'XE_TIMER_EVENT', N'BROKER_TO_FLUSH',
                          N'REQUEST_FOR_DEADLOCK_SEARCH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
                          N'CHECKPOINT_QUEUE', N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION')
ORDER BY wait_ms DESC;

Beş dakikalık bir pencerede en çok beklenen tipler, sunucunun darboğazının ilk ipucudur. Bu laboratuvar başka işlerin de çalıştığı ortak bir sunucu olduğu için sunucu seviyesindeki çıktıyı burada vermiyoruz; tek bir isteği incelerken session seviyesindeki sys.dm_exec_session_wait_stats daha temiz sonuç verir. Listedeki "zararsız" bekleme tipleri (arka plan task'larının uykuları) kısaltıldı; topluluğun yayımladığı daha uzun filtre listeleri var. signal_wait_time_ms kaynak serbest kaldıktan sonra CPU sırası beklenen süredir; toplam bekleme içindeki payı yüksekse CPU baskısı vardır. En sık göreceğin wait'ler:

Wait'ler "neyin" beklendiğini söyler, "kimin" yüzünden olduğunu değil. PAGEIOLATCH_SH yüksekse disk yavaş olabilir, ama daha sık sebep, gereğinden fazla page okuyan bir sorgudur. Bu yüzden bir sonraki adım sorguları bulmak.

DMV'lerle En Pahalı Sorguları Bulmak

Bookstore API'sini (Application Programming Interface) taklit eden küçük bir .NET uygulaması üç sorguyu birkaç kez çalıştırıyor: duruma göre sipariş listesi, sipariş numarasıyla detay ve bir müşterinin siparişleri. Plan cache'teki istatistiklerden, bu veritabanında en çok logical read yapan sorgular:

SELECT TOP (4)
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       qs.total_rows / qs.execution_count AS avg_rows,
       LEFT(SUBSTRING(t.text, qs.statement_start_offset / 2 + 1, 200), 80) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE pa.attribute = 'dbid' AND CAST(pa.value AS int) = DB_ID()
  AND t.text LIKE '%FROM dbo.Orders%' AND t.text NOT LIKE '%dm_exec%'
ORDER BY qs.total_logical_reads DESC;

execution_count|avg_logical_reads|avg_rows|statement_text
---------------|-----------------|--------|--------------
5|7707|20|SELECT TOP (20) OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE Status = @
4|7707|4458|SELECT OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE CustomerId = @custo

İki sorgu da çalıştığı her seferde yaklaşık 7.700 page okuyor; Orders'ın clustered index'i 7.601 page olduğuna göre ikisi de tabloyu baştan sona tarıyor. Ama aralarında önemli bir fark var: ilki 20 satır döndürmek için tüm tabloyu okuyor, ikincisi 4.458 satır döndürüyor. 20 satır için 7.707 read, okunan page başına döndürülen satır oranının ne kadar kötü olduğunu gösteriyor; ilk aday bu. Parametreli sorgularda sys.dm_exec_sql_text'in dbid'si boş gelir, bu yüzden veritabanını plan attribute'undan okuyoruz.

Bu DMV'nin (Dynamic Management View) sınırlarını bil: yalnız plan cache'te duran sorguları gösterir; sunucu yeniden başlarsa, plan bellek baskısıyla atılırsa ya da OPTION (RECOMPILE) kullanılırsa geçmiş kaybolur. Kalıcı ve zamana göre karşılaştırılabilir bir geçmiş için Query Store kullanılır (8. yazı). Anlık durum için ise topluluğun ücretsiz aracı sp_WhoIsActive (Adam Machanic'in yazdığı bir stored procedure), sys.dm_exec_requests, sessions, locks ve plan bilgisini tek bir okunaklı çıktıda toplar; production'da sık kullanılır ve bu yazıdaki DMV sorgularının olgunlaşmış bir hâlidir.

Index Kullanımı: Kullanılmayan ve Duplicate Index'ler

Okuma tarafının tersi yazma tarafı: her index, her INSERT, UPDATE ve DELETE'te güncellenir. Laboratuvara bilerek iki gereksiz index ekledik: IX_Orders_CustomerId'nin birebir kopyası ve hiçbir sorgunun kullanmadığı IX_Payments_Method:

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
       ISNULL(us.user_seeks, 0) AS seeks, ISNULL(us.user_scans, 0) AS scans,
       ISNULL(us.user_lookups, 0) AS lookups, ISNULL(us.user_updates, 0) AS updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
WHERE i.object_id IN (OBJECT_ID('dbo.Orders'), OBJECT_ID('dbo.Payments'))
ORDER BY table_name, index_name;

table_name|index_name|seeks|scans|lookups|updates
----------|----------|-----|-----|-------|-------
Orders|IX_Orders_CustomerId|0|4|0|0
Orders|IX_Orders_CustomerId_Copy|0|0|0|0
Orders|PK_Orders|0|15|4|2
Orders|UX_Orders_OrderNumber|4|0|0|0
Payments|IX_Payments_Method|0|0|0|0
Payments|PK_Payments|0|0|0|1
Payments|UX_Payments_OrderId|0|1|0|0

Kopya index ve IX_Payments_Method hiç okunmamış. Duplicate'leri key kolonlarını karşılaştırarak bulmak da mümkün; aynı kolonları aynı sırayla içeren index çiftlerini listeleyen bir sorgu bu laboratuvarda IX_Orders_CustomerId_Copy'yi IX_Orders_CustomerId'nin kopyası olarak buldu. Dikkat: sys.dm_db_index_usage_stats sunucu yeniden başladığında sıfırlanır. Bir index'i "kullanılmıyor" diye silmeden önce, ay sonu raporu gibi seyrek çalışan işleri kapsayacak kadar uzun bir dönemi izle.

Extended Events ile Yakalamak

DMV'ler toplamları gösterir; tek tek çalışmaları, hangi uygulamadan geldiklerini ve hangi parametrelerle çalıştıklarını görmek için Extended Events gerekir. Yalnız bu veritabanında ve 1.000 logical read'in üzerinde biten istekleri yakalayan bir session:

CREATE EVENT SESSION sqls_18_slow ON SERVER
ADD EVENT sqlserver.rpc_completed
    (ACTION (sqlserver.client_app_name, sqlserver.query_hash)
     WHERE sqlserver.database_name = N'sqls_18' AND logical_reads > 1000),
ADD EVENT sqlserver.sql_batch_completed
    (ACTION (sqlserver.client_app_name, sqlserver.query_hash)
     WHERE sqlserver.database_name = N'sqls_18' AND logical_reads > 1000)
ADD TARGET package0.ring_buffer
WITH (MAX_DURATION = 30 MINUTES);
GO
ALTER EVENT SESSION sqls_18_slow ON SERVER STATE = START;

event_name|app|logical_reads|row_count|statement
----------|---|-------------|---------|---------
rpc_completed|Bookstore.Api|7737|20|exec sp_executesql N'SELECT TOP (20) OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE
rpc_completed|Bookstore.Api|7707|20|exec sp_executesql N'SELECT TOP (20) OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE
rpc_completed|Bookstore.Api|7777|4458|exec sp_executesql N'SELECT OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE Customer

Uygulamanın gönderdiği her parametreli sorgu bir rpc_completed, sqlcmd ya da SSMS'ten (SQL Server Management Studio) gelen ad hoc batch'ler sql_batch_completed olarak gelir; ikisini birlikte almazsan resmin yarısını kaçırırsın. Filtre event'in kendi alanı (logical_reads) üzerinde olduğu için eşiğin altındaki binlerce hızlı istek hiç toplanmaz; bu, production'da Extended Events'i ucuz tutan şeydir. client_app_name action'ı connection string'deki Application Name'i getiriyor; aynı sunucuyu paylaşan servisleri ayırmanın en basit yolu. query_hash ise aynı sorgunun farklı parametrelerle çalışmalarını gruplamaya yarar.

MAX_DURATION = 30 MINUTES, SQL Server 2025'te gelen time-bound session özelliği: teşhis için açılan session yarım saat sonra kendini durdurur. Production'da en sık yapılan hata, sorun çözüldükten sonra açık unutulan ve haftalarca event toplayan bir session'dır. Daha önceki sürümlerde bunu bir SQL Agent job'u ya da elle durdurarak yönetirdin.

Uygulamayla Bağlamak: OpenTelemetry

Şikâyet veritabanından değil, bir endpoint'ten gelir. Uygulama tarafında her HTTP (Hypertext Transfer Protocol) isteğinin hangi SQL komutlarını çalıştırdığını görmek, "yavaş endpoint"ten "yavaş sorgu"ya inmenin en kısa yolu. OpenTelemetry'nin SqlClient instrumentation'ı her komut için bir span üretir:

ActivitySource api = new("Bookstore.Api");

using TracerProvider tracing = Sdk.CreateTracerProviderBuilder()
    .AddSource("Bookstore.Api")
    .AddSqlClientInstrumentation()
    .AddConsoleExporter()
    .Build();

await using SqlConnection connection = new(connectionString);

using (Activity? request = api.StartActivity("GET /orders?status=Pending"))
{
    IEnumerable<OrderRow> rows = await connection.QueryAsync<OrderRow>(
        "SELECT TOP (20) OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE Status = @status ORDER BY CreatedAt DESC",
        new { status = new DbString { Value = "Pending", IsAnsi = true, Length = 20 } });
    request?.SetTag("orders.count", rows.Count());
}

Activity.TraceId:            ea1724588f09528815421995b061ee3b
Activity.ParentSpanId:       5466b422839844df
Activity.DisplayName:        SELECT dbo.Orders
Activity.Kind:               Client
Activity.Tags:
    db.system.name: microsoft.sql_server
    db.namespace: sqls_18
    server.address: localhost
    server.port: 14333
    db.query.text: SELECT TOP (?) OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE Status = @status ORDER BY CreatedAt DESC
    db.query.summary: SELECT dbo.Orders

Activity.TraceId:            ea1724588f09528815421995b061ee3b
Activity.SpanId:             5466b422839844df
Activity.DisplayName:        GET /orders?status=Pending
Activity.Kind:               Internal
Activity.Tags:
    orders.count: 20

İki span aynı TraceId'yi paylaşıyor; SQL span'inin ParentSpanId'si endpoint span'i. Çıktıdaki süre satırlarını, emülasyondaki ölçümler hız iddiası olmasın diye çıkardık; gerçek bir sistemde bu iki span'in süresini Jaeger ya da Grafana gibi bir arayüzde yan yana görürsün. db.query.text'te literal'lar ? ile maskelenmiş, parameter'lar adıyla duruyor: hassas değerler trace'e sızmıyor ama sorgu metni Extended Events'teki statement ve sys.dm_exec_query_stats'taki metinle eşleştirilebilecek kadar net. ASP.NET Core Web API serisinde kurduğumuz logging ve CancellationToken altyapısı ile birlikte bu, şikâyetten sorguya giden yolun uygulama tarafı.

Sorguyu yazarken DbString ile IsAnsi = true verdiğimize dikkat et: Status kolonu varchar. Bunu vermeseydik parameter nvarchar gider ve implicit conversion yüzünden index olsa bile seek yapılamayabilirdi (6. yazı ve Dapper serisinin SQL Server yazısı).

Akışın Tamamı: Düzelt ve Doğrula

Plan, durum listesinin clustered index'i baştan sona taradığını gösteriyor; Status üzerinde index yok. Düzeltmeyi yapıp, kopya index'i de kaldırıp aynı iş yükünü tekrar çalıştırıyoruz:

CREATE INDEX IX_Orders_Status_CreatedAt ON dbo.Orders (Status, CreatedAt) INCLUDE (TotalAmount);
DROP INDEX IX_Orders_CustomerId_Copy ON dbo.Orders;

execution_count|avg_logical_reads|avg_rows|statement_text
---------------|-----------------|--------|--------------
4|7708|4458|SELECT OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE CustomerId = @custo
4|6|1|SELECT OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE OrderNumber = @numb
5|3|20|SELECT TOP (20) OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE Status = @

Durum listesi 7.707'den 3 logical read'e indi ve Extended Events session'ında artık görünmüyor; yeniden başlatılan session'da yalnız müşteri sorgusunun dört çalışması kaldı. Doğrulama, düzeltmeyle aynı metrikle yapılır: "daha hızlı hissettiriyor" değil, "aynı sorgu, aynı ölçü, önce 7.707, sonra 3". Listede kalan müşteri sorgusu bir sonraki tur için aday: 4.458 satır için tablo taraması, serinin 3. yazısındaki tipping point konusunun ta kendisi.

SQL Server 2019/2022'de Farkı

Wait istatistikleri, DMV'ler ve Extended Events 2019 ve 2022'de aynı şekilde çalışır; bu yazıdaki sorguların hepsi o sürümlerde de geçerli. Farklar: MAX_DURATION ile time-bound session 2025'e özgü. SQL Server 2019, son çalışan planı her zaman tutmak için LAST_QUERY_PLAN_STATS veritabanı ayarını ve sys.dm_exec_query_plan_stats DMV'sini getirdi; 2022, Query Store'u yeni veritabanlarında varsayılan açık yaptı. 2025'te optimized locking açıksa blocking'i LCK_M_S_XACT_* wait'leri olarak görürsün (13. yazı); yalnız LCK_M_U/LCK_M_X'e bakan bir izleme bunları kaçırır.

Üretimde Dikkat

Baseline tut. "Normal" wait profilini ve en pahalı sorguların listesini bilmeden "anormal"i tanıyamazsın. Saatlik wait delta'larını ve Query Store'un top sorgularını bir tabloya yazan küçük bir job, ilk şikâyette en değerli veriyi sağlar.

Extended Events'i dar tut. Filtreyi event'in kendi alanlarına (logical_reads, duration, database_name) koy, yalnız gereken action'ları ekle ve ring buffer yerine kalıcı inceleme için event_file kullan. Her şeyi toplayan bir session, teşhis ettiği sorundan daha büyük bir yük olabilir.

Uygulamayı tanımlanabilir yap. Her servis için ayrı Application Name, mümkünse ayrı SQL kullanıcısı. Blocking zincirinde ya da Extended Events'te SQLCMD ya da ".Net SqlClient Data Provider" görmek, işin yarısını tahmine bırakmak demek.

Düzeltmeyi tek değişiklikle yap. Aynı anda index ekleyip, sorguyu değiştirip, statistics güncellersen hangisinin işe yaradığını bilemezsin. Bir değişiklik, bir ölçüm.

Sık Düşülen Tuzaklar

Kümülatif wait istatistiğini okumak. sys.dm_os_wait_stats'ın ham değerleri aylar öncesini de içerir; tek başına hiçbir şey söylemez. Her zaman iki an arasındaki farka bak.

CXPACKET'i "kötü wait" sanmak. Paralel planların doğal sonucudur. Sorun paralellik değil, paralel çalışmak zorunda kalan gereksiz büyük scan'dir; MAXDOP'u düşürmek belirtiyi gizler.

Missing index önerilerini toplu uygulamak. sys.dm_db_missing_index_details her sorgu için ayrı öneri üretir; çoğu birbirine benzer ve hepsini eklemek yazma maliyetini katlar. Önerileri sorgu bazında değerlendir ve mevcut index'leri genişletmeyi önce düşün.

Süreyi tek metrik saymak. Bir sorgunun süresi, o anki yüke, cache'in sıcaklığına ve blocking'e göre değişir. Logical read, satır sayısı ve plan operator'leri tekrarlanabilir; düzeltmenin etkisini onlarla göster.

Küçük Bir Deneme

★☆☆ sqls_18_slow session'ına sql_statement_completed event'ini ekle ve bir stored procedure içindeki statement'ların ayrı ayrı nasıl göründüğünü incele. rpc_completed ile farkı ne?

★★☆ Wait delta sorgusunu bir procedure'e çevir ve 11. yazıdaki blocking zinciri demosu çalışırken bir dakikalık pencerede çağır. En üstte hangi wait type çıkıyor?

★★★ Bir ASP.NET Core uygulamasında, connection açıldığında isteğin trace id'sini SET CONTEXT_INFO ile SQL Server'a yazan bir Entity Framework Core (EF Core) interceptor'ı ekle ve Extended Events session'ına sqlserver.context_info action'ını ekleyerek bir trace'i doğrudan Extended Events event'ine bağla.

Teşhis yöntemi sürümden bağımsız; ama sürüm, hangi aracın elinde olduğunu belirliyor. Serinin son yazısında eski SQL Server'ları okumayı öğreniyoruz: 2016'dan 2025'e motorun nasıl değiştiği, compatibility level'ın ne anlama geldiği ve bir yükseltmeyi Query Store ile güvenle yapmanın planı.