“Sistem yavaş” şikâyeti geldiğinde ilk iş, yavaşlığın hangi sorgulardan geldiğini bulmaktır. SQL Server bunun için gereken bilgiyi zaten tutar: şu anda çalışan istekler, plan cache’teki sorguların biriken istatistikleri ve Query Store’daki geçmiş. Doğru sorgularla birkaç dakika içinde sunucunun zamanını ve kaynaklarını en çok neyin tükettiğini görebilirsiniz.
Bu rehberde yavaş sorguları üç zaman ölçeğinde bulmayı anlatıyoruz: şu anda, cache’in tuttuğu süre boyunca ve geçmişte. Her sorgunun neyi gösterdiğini ve neyi gösteremeyeceğini de açıklıyoruz.
Hangi sorgu sisteminizi yavaşlatıyor?
Aryasoft, en pahalı sorguları bulur, execution plan'larını analiz eder ve index, sorgu ve yapılandırma düzeyinde kalıcı iyileştirmeler yapar.
1. Şu Anda Ne Çalışıyor?
Kullanıcılar şu an yavaşlık yaşıyorsa ilk bakılacak yer çalışan isteklerdir. Ne kadar süredir çalıştıklarını, neyi beklediklerini ve kim tarafından bloklandıklarını gösterir:
SELECT r.session_id, r.status, r.command,
r.total_elapsed_time / 1000 AS elapsed_sec,
r.cpu_time / 1000 AS cpu_sec,
r.logical_reads,
r.wait_type, r.wait_time, r.blocking_session_id,
DB_NAME(r.database_id) AS database_name,
s.login_name, s.host_name, s.program_name,
SUBSTRING(t.text, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;blocking_session_id dolu satırlar bloklanmış isteklerdir; zincirin başındaki oturumu (head blocker) bulun. wait_type, isteğin neyi beklediğini söyler: LCK_* kilit, PAGEIOLATCH_* disk, CXPACKET paralellik, boş ve status = running ise CPU.
2. Plan Cache: En Pahalı Sorgular
sys.dm_exec_query_stats, plan cache’teki her sorgu için çalışma sayısını ve toplam CPU, süre ve okuma değerlerini tutar. “En pahalı” sorgu, en yavaş tek çalıştırma değil, toplamda en çok kaynak tüketen sorgudur; saniyede yüz kez çalışan 50 milisaniyelik bir sorgu, günde bir kez çalışan beş dakikalık bir rapordan daha pahalı olabilir.
-- Toplam CPU'ya göre ilk 20 sorgu (sıralamayı total_logical_reads ya da total_elapsed_time ile değiştirebilirsiniz)
SELECT TOP (20)
qs.execution_count,
qs.total_worker_time / 1000 AS total_cpu_ms,
qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
qs.total_elapsed_time / qs.execution_count / 1000 AS avg_duration_ms,
qs.total_logical_reads / qs.execution_count AS avg_reads,
qs.last_execution_time,
DB_NAME(st.dbid) AS database_name,
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;Ad hoc sorguları gruplamak
Değerleri metne gömülü sorgular, cache’te her değer için ayrı satır oluşturur ve listede tek tek önemsiz görünür. query_hash ile gruplamak, aynı kalıptaki sorguların toplam etkisini ortaya çıkarır:
SELECT TOP (20)
qs.query_hash,
COUNT(*) AS distinct_plans,
SUM(qs.execution_count) AS executions,
SUM(qs.total_worker_time) / 1000 AS total_cpu_ms,
SUM(qs.total_logical_reads) AS total_reads,
MIN(SUBSTRING(st.text, 1, 200)) AS sample_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY qs.query_hash
ORDER BY total_cpu_ms DESC;Uyarı
Plan cache’e dayalı sorgular yalnızca şu anda cache’te bulunan planları görür. Servis yeniden başladığında, bellek baskısında, istatistik güncellemesinde ya da birisi DBCC FREEPROCCACHE çalıştırdığında geçmiş silinir. OPTION (RECOMPILE) kullanan sorgular cache’te hiç kalmaz. Sonuçları değerlendirirken last_execution_time ve sunucunun ne zamandır çalıştığına bakın.
3. Geçmiş: Query Store
“Dün saat 15.00’te ne oldu?” sorusunun cevabı plan cache’te değil, Query Store’dadır. Query Store sorguları, planlarını ve istatistiklerini zaman aralıklarıyla saklar; bir sorgunun ne zaman yavaşladığını ve planının değişip değişmediğini gösterir. Kurulumu ve sorguları için Query Store rehberimize bakın.
4. Bağlam: Sunucu Neyi Bekliyor?
Tek tek sorgulara bakmadan önce sunucunun genel olarak neyi beklediğini görmek, aramayı doğru yöne çevirir:
SELECT TOP (10) wait_type,
wait_time_ms / 1000 AS wait_sec,
waiting_tasks_count,
CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS decimal(5, 1)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (N'SLEEP_TASK', N'LAZYWRITER_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'CHECKPOINT_QUEUE', N'LOGMGR_QUEUE',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'WAITFOR', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
N'ONDEMAND_TASK_QUEUE', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'XE_LIVE_TARGET_TVF')
AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;Bu değerler servisin başlangıcından beri birikir. PAGEIOLATCH_* öndeyse okumaları azaltmaya (index’ler, sorgu tasarımı) ve diske, CXPACKET öndeyse paralellik ayarlarına, LCK_* öndeyse blocking’e ve transaction tasarımına odaklanın.
5. Sonra Ne Yapmalı?
- En pahalı sorgunun planını açın (
query_plankolonu). Büyük tablo taramaları, uyarılar (sarı ünlem), tahmini ve gerçek satır sayısı arasındaki büyük farklar ilk ipuçlarıdır. - SARGable olmayan koşulları arayın: kolonun bir fonksiyon içinde kullanılması, örtük tip dönüşümleri, baştan joker karakterli LIKE.
- Eksik index önerilerini körü körüne uygulamayın; mevcut index’lerle birlikte değerlendirin. Bkz. index rehberimiz.
- Değişiklikten sonra aynı ölçümü tekrarlayın; iyileştirmeyi sayılarla doğrulayın.
İpucu
Gerçek bir örnek için kamu kurumu başarı hikâyemize bakın: yeni bir sürümle CPU’yu %100’e kilitleyen sorgu, bu yöntemle birkaç dakikada bulundu ve bir view düzeltmesiyle tek bir filtered index, CPU’yu %5’e indirdi.
Kontrol Listesi
- Anlık yavaşlıkta sys.dm_exec_requests ile çalışan istekler ve blocking incelendi.
- Plan cache’te toplam CPU, okuma ve süreye göre en pahalı sorgular listelendi.
- Ad hoc sorgular query_hash ile gruplandı.
- Geçmiş için Query Store açık ve kullanılıyor.
- Bekleme istatistikleri ile sorgu bulguları birlikte değerlendirildi.
İlgili Yazılar
SQL Server Performans Danışmanlığı
Yavaşlığın kaynağını tahminle değil, ölçümle bulun
Aryasoft’un kıdemli DBA ekibi en pahalı sorgularınızı ve bekleme istatistiklerinizi analiz eder; index, sorgu ve yapılandırma düzeyinde kalıcı iyileştirmeleri ölçerek uygular.
