Önemli olan SQL Server beklemeleri ve görmezden gelebilecekleriniz

Beklemelerle başlayın

SQL Server'daki bir görev devam edemediğinde (diskten bir sayfaya, başka bir oturumun tuttuğu bir kilide, bir bellek ayırmasına veya bir CPU'ya ihtiyaç duyduğu için) bekler. SQL Server bekleme türünü ve süresini sys.dm_os_wait_stats'a kaydeder. Yoğun bir dönem boyunca toplandığında bu görünüm, tek bir sorguya bakmadan önce zamanın nereye gittiğini söyler.

Her bekleme türünün bilinmeye değer iki sayısı vardır. wait_time_ms beklemede geçen toplam süredir. signal_wait_time_ms ise kaynak kullanılabilir hale geldikten sonra, CPU için kuyrukta beklerken geçen kısımdır. Aradaki fark, kaynağın kendisini beklerken geçen süredir.

Toplamları değil, farkları okuyun

sys.dm_os_wait_stats, instance başladığından ya da biri onu temizlediğinden beri kümülatiftir. 90 gündür çalışan bir instance, her gece alınan yedeği ve geçen ayki olayı aynı toplamlarda taşır. İki anlık görüntü alın ve çıkarın:

-- Snapshot, wait a minute, snapshot again: the difference is that minute
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #w1
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:01:00';

SELECT w2.wait_type,
       w2.wait_time_ms - w1.wait_time_ms               AS wait_ms,
       w2.signal_wait_time_ms - w1.signal_wait_time_ms AS signal_ms,
       w2.waiting_tasks_count - w1.waiting_tasks_count AS waits
FROM sys.dm_os_wait_stats AS w2
JOIN #w1 AS w1 ON w1.wait_type = w2.wait_type
WHERE w2.wait_time_ms - w1.wait_time_ms > 0
ORDER BY wait_ms DESC;

Toplamların gizlediği bir şey daha var: bir bekleme sys.dm_os_wait_stats'a yalnızca bittiğinde eklenir. On dakikadır bir kilidin arkasında takılı kalmış bir oturum, beklerken burada değil, sys.dm_os_waiting_tasks ve sys.dm_exec_requests'te görünür. Ortalamayı bulmak için bekleme süresini bekleme sayısına bölün. Toplamı büyük, ortalaması çok küçük bir bekleme türü çok sayıda kısa beklemedir; sayısı az, ortalaması büyük olan ise birkaç uzun beklemedir. Nedenleri farklıdır.

Önce zararsız beklemeleri filtreleyin

Pek çok bekleme türü, tasarımı gereği bekleyen arka plan görevlerine aittir: iş olana kadar uyurlar. Devasa bekleme süreleri biriktirirler ve hiçbir anlam taşımazlar. Herhangi bir şeyi sıralamadan önce bunları çıkarın. Eksiksiz değil, kısa bir liste:

• SLEEP_TASK, LAZYWRITER_SLEEP, SQLTRACE_BUFFER_FLUSH, DIRTY_PAGE_POLL
• XE_TIMER_EVENT, XE_DISPATCHER_WAIT, SP_SERVER_DIAGNOSTICS_SLEEP
• REQUEST_FOR_DEADLOCK_SEARCH, LOGMGR_QUEUE, CHECKPOINT_QUEUE
• BROKER_TO_FLUSH, BROKER_TASK_STOP, FT_IFTS_SCHEDULER_IDLE_WAIT
• QDS_PERSIST_TASK_MAIN_LOOP_SLEEP, HADR_FILESTREAM_IOMGR_IOCOMPLETION
• Bilerek bekleyen bir ifade olan WAITFOR

Bir şeye işaret eden aileler

Kilitler: LCK_M_*

Bir oturum, başka bir oturumun tuttuğu bir kilidi istiyor. Sonek kilit modunu belirtir: paylaşımlı için LCK_M_S, güncelleme için LCK_M_U, özel (exclusive) için LCK_M_X, niyet kilitleri için LCK_M_IX ve benzerleri. Çözüm neredeyse hiçbir zaman bekleyen oturumda değildir. Kilidi tutan oturumdadır; bu yüzden kilitlenme zincirine gidin.

Sayfa okuma: PAGEIOLATCH_SH ve PAGEIOLATCH_EX

Bir sorgu, buffer pool'da olmayan bir veri sayfasına ihtiyaç duyuyor. Büyük değerler üç şeyden birini gösterir: depolama yavaştır (sys.dm_io_virtual_file_stats'ta dosya başına gecikmeyi kontrol edin), sorgular gerekenden çok daha fazla okur (bir index'in önleyeceği taramalar) ya da buffer pool çalışma kümesi için çok küçüktür. Önce dosya gecikmesine bakın; üçünden hangisinde olduğunuzu o belirler.

Log yazma: WRITELOG

Commit'ler işlem günlüğünün diske yazılmasını bekler. Log dosyasındaki yazma gecikmesini kontrol edin ve bir döngü içinde her seferinde tek satır commit eden uygulamalara bakın.

Bellekteki sayfalar: PAGELATCH_*

Adına rağmen G/Ç değildir. Pek çok oturum aynı anda bellekteki aynı sayfayı ister: yoğun geçici tablo kullanımında tempdb'deki ayırma sayfaları ya da yoğun insert'ler altında anahtarı sürekli artan bir index'in son sayfası.

CPU: SOS_SCHEDULER_YIELD ve sinyal beklemeleri

Worker'lar 4 ms'lik zaman dilimlerinden sonra CPU'yu bırakır ve geri almak için bekler. Sinyal beklemeleri tüm bekleme süresinin büyük bir payını oluşturuyorsa oturumlar CPU için kuyrukta bekliyordur. Çekirdek eklemeden önce CPU'yu tüketen sorguları bulun.

Bellek ayırmaları: RESOURCE_SEMAPHORE

Sorgular sıralama veya hash için bellek bekliyor. Genellikle birkaç sorgu, optimizer döndürdüklerinden çok daha fazla satır tahmin ettiği için kullandıklarından çok daha fazla bellek ister.

Paralellik: CXPACKET ve CXCONSUMER

Paralel bir sorgunun thread'lerinin birbirini beklemesi. Tek başlarına bir sorun olduğunu değil, sorguların paralel çalıştığını söylerler. CXCONSUMER genellikle zararsızdır. Thread'ler arasında iş dengesiz dağılmışken yüksek bir CXPACKET, MAXDOP'a ve paralellik maliyet eşiğine bakmaya değer.

İstemci: ASYNC_NETWORK_IO

SQL Server sonuçları hazırlamış ve istemci onları okumuyor. Çoğu zaman satırları tek tek çeken ve aralarında iş yapan bir uygulamadır. Burada darboğaz veritabanı değildir.

Kaynak açlığı: THREADPOOL

Yeni bir isteği çalıştıracak boş worker thread yok. Nadir ve ciddi: genellikle worker'ları tutan yoğun kilitlenmeler ya da bir yeni bağlantı fırtınası.

Ardından kilitlenme zincirinin başını bulun

Kilit beklemeleri bir belirtidir. sys.dm_exec_requests'teki her kilitlenmiş isteğin bir blocking_session_id'si vardır. Başkalarını kilitleyen ve kendisi kilitlenmemiş bir oturuma ulaşana kadar onu takip edin. Çoğu zaman bu oturum hiçbir şey çalıştırmıyordur: açık bir işlemi olan uyuyan bir oturum ve commit etmeyi unutmuş bir uygulamadır.

-- Who is blocked, and by whom
SELECT r.session_id, r.blocking_session_id, r.wait_type,
       r.wait_time AS wait_ms, r.wait_resource, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

-- Sessions holding a transaction open while doing nothing
SELECT session_id, login_name, host_name, program_name,
       last_request_end_time, open_transaction_count
FROM sys.dm_exec_sessions
WHERE status = 'sleeping' AND open_transaction_count > 0;

Logstag bunu nasıl gösterir

Agent bu görünümleri VIEW SERVER STATE yetkisine sahip bir login ile okur ve oturumlardan ve kilitlenmelerden kısa aralıklarla örnek alır. En yüksek beklemeleri sıralamadan önce zararsız beklemeleri çıkarır; Activity Explorer'ın Blocking Chains sekmesi de her zinciri kök kilitleyiciden başlatır. SQL Server kontrolleri, kilitlenmeler ve uzun süren oturumlar için olası nedenleri ve yapılacakları içeren alarmlar üretir.