해당 쿼리가 디스크i/o 논리i/o elapsetime 등을 확인하는 쿼리

✅ 1. 디스크 I/O (Physical I/O)

sys.dm_io_virtual_file_stats를 사용 — 파일 수준의 I/O (읽기/쓰기 횟수 및 대기 시간).

---

✅ 2. 논리 I/O (Logical I/O)

쿼리 실행 시 발생한 페이지 읽기 횟수는 SET STATISTICS IO ON으로 확인 가능합니다. 예:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 여기에 실행할 쿼리
SELECT * FROM YourTable WHERE YourCondition;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

결과 예:

Table 'YourTable'. Scan count 1, logical reads 500, physical reads 5, read-ahead reads 10
SQL Server Execution Times:
   CPU time = 50 ms,  elapsed time = 200 ms.

---

✅ 3. 실행 계획 기반 상세 분석 (Elapsed Time, Logical Reads, CPU Time)

이 쿼리는 최근 실행된 쿼리들의 성능 메트릭을 보여줍니다:

SELECT
    DB_NAME(st.dbid) AS database_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_physical_reads / qs.execution_count AS avg_physical_reads,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    st.text AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_elapsed_time DESC;

---

✅ 4. 요약

메트릭	확인 방법
디스크 I/O	sys.dm_io_virtual_file_stats
논리 I/O	SET STATISTICS IO ON + 실행
Elapsed Time / CPU Time	sys.dm_exec_query_stats 사용
실시간 모니터링	sys.dm_exec_requests, Extended Events 등