SQL Server IO 병목 확인과 오해
- Version : SQL Server 2005, 2008, 2008R2, 2012
SQL Server의 작업 부하는 일반적으로 I/O 서브시스템이 디스크로부터 데이터를 읽거나 쓰는 작업에서 많이 발생 한다.
SQL Servers는 내부 메커니즘에 의해(I/O 요청이 OS의 스케줄러에 의해 처리 되는) I/O 요청이 처리 된다. 이 때 작업의 규모나 시스템의 자원에 따라 요청에 대한 처리 속도에 영향을 받는다. 서비스하고 있는 서버의 I/O 부하는 어느 정도일까? 즉 요청 된 작업이 대기하는 시간은 어떻게 될까? 다음 스크립트를 통하여 요청에 응답하는 시간(대기 시간)이 기록되어 있는 통계를 확인 할 수 있다.
SELECT DB_NAME(vfs.database_id) [db_name], io_stall_read_ms / NULLIF(num_of_reads, 0) avg_read_latency, io_stall_write_ms / NULLIF(num_of_writes, 0) avg_write_latency, physical_name [file_name], io_stall / NULLIF(num_of_reads + num_of_writes, 0) avg_total_latency FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.FILE_ID = mf.FILE_ID ORDER BY avg_total_latency DESC; |
이 스크립트는 서버에서 즉시 실행 할 수 있으며 SQL Server가 읽기/쓰기를 하는 동안 기다리는 시간(밀리초)을 평가 할 수 있다.
I/O stall을 평가할 때 이상적인 수치로는 데이터 파일은 읽고 쓰는데 0-8ms, 로그 파일은 0-4ms의 대기시간을 표시 한다. 스핀들이 많은 I/O 서브시스템의 경우 0-2ms를 나타내기도 한다. 일부 서버에서는 100-200ms부터 때로는 2000ms까지 느린 대기 시간을 나타내는 경우도 있다.
여기서 오해의 소지가 있는 부분이 대기 시간이 크다고 해서 무조건 문제가 있는 것은 아니다. 스크립트를 통하여 확인 되는 정보가 오랜 시간 동안 수집되어 나타낸 통계임을 고려 할 때 일부 쿼리(특정 시간의 배치 작업)에서 I/O에 대한 집약적인 사용으로 수치가 부풀려 질 수 있다.
다음 스크립트는 어떤 I/O 문제에 대해서 현재 실행 상태의 디스크의 물리적 읽기/쓰기 병목이 발생하는 파일 및 파일ID를 확인 확인 할 수 있다.
DECLARE @Reset bit = 0;
IF NOT EXISTS (SELECT NULL FROM tempdb.sys.objects WHERE name LIKE '%#fileStats%') SET @Reset = 1; -- force a reset
IF @Reset = 1 BEGIN IF EXISTS (SELECT NULL FROM tempdb.sys.objects WHERE name LIKE '%#fileStats%') DROP TABLE #fileStats;
SELECT database_id, file_id, num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall INTO #fileStats FROM sys.dm_io_virtual_file_stats(NULL, NULL); END
SELECT DB_NAME(vfs.database_id) AS database_name, --vfs.database_id , vfs.FILE_ID , (vfs.io_stall_read_ms - history.io_stall_read_ms) / NULLIF((vfs.num_of_reads - history.num_of_reads), 0) avg_read_latency, (vfs.io_stall_write_ms - history.io_stall_write_ms) / NULLIF((vfs.num_of_writes - history.num_of_writes), 0) AS avg_write_latency , mf.physical_name FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.FILE_ID = mf.FILE_ID RIGHT OUTER JOIN #fileStats history ON history.database_id = vfs.database_id AND history.file_id = vfs.file_id ORDER BY avg_write_latency DESC; |
DBA는 I/O 서브시스템의 파워와 사용량을 확인하여 안정적인 서비스가 되도록 설계하고 제안 하여야 한다.
[참고자료]
- sys.dm_io_virtual_file_stats : http://msdn.microsoft.com/ko-kr/library/ms190326.aspx
- DISK IO 병목확인 : http://sqlmvp.kr/140192055664
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
