disk i/o와 logical i/o를 sqlid로 확인하는 mssql 쿼리

SQL Server에서는 Oracle의 SQL_ID처럼 하나의 SQL 문장을 식별해서 해당 쿼리의 Disk I/O (물리적 I/O) 및 Logical I/O (논리적 I/O) 정보를 확인할 수 있습니다.

SQL Server에는 SQL_ID가 없지만, 유사하게 sql_handle 또는 query_hash를 사용하면 동일한 기능을 구현할 수 있습니다.

---

✅ 목표: “하나의 SQL 문장”별 Disk I/O + Logical I/O 확인

SQL Server에서는 다음 DMV들을 조합해 사용합니다:

• sys.dm_exec_query_stats : 쿼리 실행 통계
• sys.dm_exec_sql_text : SQL 텍스트
• sys.dm_exec_query_plan : 실행 계획

---

✅ 쿼리 예제: SQL 문장별 Disk I/O & Logical I/O 보기

sql복사편집SELECT
    qs.execution_count,
    qs.total_logical_reads AS total_logical_io,
    qs.total_physical_reads AS total_physical_io,  -- Disk I/O
    qs.total_logical_writes,
    qs.total_elapsed_time / 1000 AS total_elapsed_ms,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    st.text AS sql_text,
    qs.query_hash,
    qs.query_plan_hash
FROM
    sys.dm_exec_query_stats qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
    st.text LIKE '%<여기에 찾고자 하는 SQL 키워드 또는 테이블명 등 입력>%'
ORDER BY
    qs.total_physical_reads DESC;

🔍 이 쿼리는 실행된 SQL 문들 중에서 가장 많은 디스크 I/O를 발생시킨 쿼리를 보여줍니다. 원하는 SQL을 찾을 때는 LIKE 조건을 수정하거나 query_hash로 필터링하면 됩니다.

---

✅ 특정 query_hash로 추적 (Oracle SQL_ID처럼)

sql복사편집-- 예시: 특정 query_hash로 조회
DECLARE @query_hash BINARY(8) = 0x1234567890ABCDEF;  -- 실제 해시 값 입력

SELECT
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_physical_reads,
    qs.total_logical_writes,
    st.text AS sql_text,
    qs.query_hash
FROM
    sys.dm_exec_query_stats qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
    qs.query_hash = @query_hash;

---

✅ 쿼리 실행 시 실시간 I/O 확인 (STATISTICS IO)

실제 쿼리 성능을 보려면 SSMS에서 직접 실행하면서 아래 옵션을 설정:

sql복사편집SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 분석할 SQL 문
SELECT * FROM YourTable WHERE SomeColumn = 'X';

결과는 SSMS “Messages” 탭에 표시되며, Logical/Physical Reads 등을 볼 수 있습니다.

---

✅ 주요 지표 설명

항목	설명
total_logical_reads	Buffer pool에서 읽은 페이지 수 (논리 I/O)
total_physical_reads	디스크에서 읽은 페이지 수 (물리 I/O)
total_logical_writes	버퍼에서 디스크로 쓴 페이지 수
execution_count	실행 횟수

---

✅ 요약

Oracle의 SQL_ID로 확인하던 것처럼 SQL Server에서는 다음 방식으로 확인합니다:

• 식별자: sql_handle or query_hash
• I/O 지표: total_logical_reads, total_physical_reads, total_logical_writes
• 대상 뷰: sys.dm_exec_query_stats, sys.dm_exec_sql_text