의심쿼리 찾는 법

MSSQL에서 Deadlock이나 성능 문제를 유발하는 “의심 쿼리” 를 찾으려면, 다음과 같은 진단 포인트를 중심으로 쿼리를 수집하고 분석할 수 있습니다.

---

🔍 1. 현재 실행 중이거나 블로킹 중인 쿼리 찾기

-- 현재 실행 중이며 다른 세션을 블로킹 중인 쿼리 찾기
SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.cpu_time,
    r.total_elapsed_time,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    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 running_statement,
    t.text AS full_query,
    s.host_name,
    s.program_name,
    s.login_name,
    s.reads,
    s.writes
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0 OR r.status = 'running'
ORDER BY r.total_elapsed_time DESC;

✅ 이 쿼리는 현재 실행 중인 쿼리들 중에서 블로킹하거나 오래 실행 중인 것을 보여줍니다. total_elapsed_time, cpu_time 기준으로 정렬해서 “느린 쿼리” 파악에 적합합니다.

---

🔥 2. 최근 실행된 비효율 쿼리 조회 (쿼리 캐시 기반)

-- 실행 횟수 대비 평균 CPU 사용량이 높은 쿼리 TOP 20
SELECT TOP 20
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    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 query_text,
    st.text AS full_text,
    db_name(st.dbid) AS db_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

✅ 이 쿼리는 최근 실행된 쿼리 중 CPU를 많이 쓴 의심 쿼리를 찾아냅니다. 실제 deadlock은 아니더라도 과부하 유발자를 추적할 수 있습니다.

---

🧠 3. Deadlock 유발 가능성 높은 쿼리 특징

특징	설명
🔁 트랜잭션 안에서 여러 테이블을 동시에 조작	순서가 꼬이면 교착 상태 발생
🔒 WITH (TABLOCK), SERIALIZABLE 사용	잠금 확장 또는 범위 잠금
🔍 인덱스 미사용 or 잘못된 조건	스캔으로 인해 잠금 범위 넓어짐
❗ 쿼리 실행 시간 or I/O 비정상적으로 높음	다른 쿼리를 막고 있는 가능성

---

🛠️ 4. 잠금 보유 현황으로 의심 세션 찾기

-- 현재 어떤 테이블에 락을 걸고 있는지 보기
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_resource,
    t.text AS sql_text,
    o.name AS locked_object,
    p.object_id,
    l.resource_type,
    l.request_mode
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.objects o ON l.resource_associated_entity_id = o.object_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
WHERE s.is_user_process = 1;

✅ 락을 잡고 있는 세션이 어떤 테이블을 대상으로 어떤 모드의 락을 걸고 있는지 분석해 볼 수 있습니다.

---

📌 요약: 의심 쿼리를 찾는 방법

목적	쿼리	설명
🔎 현재 블로킹 상태 보기	sys.dm_exec_requests	현재 문제 유발자 확인
📊 과거 비효율 쿼리 보기	sys.dm_exec_query_stats	캐시된 쿼리 중 고비용 쿼리 탐지
🔐 락 정보 보기	sys.dm_tran_locks	어떤 객체가 잠겨 있는지 분석
📈 히스토리 추적	Extended Events	Deadlock 발생 쿼리 추적 가능