데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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 서브시스템의 파워와 사용량을 확인하여 안정적인 서비스가 되도록 설계하고 제안 하여야 한다.

 

[참고자료]

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1573
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1931
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1928
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2051
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2210
» SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2497
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1711
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1542
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1862
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 819
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1602
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4316
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1075
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1269
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1188
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1311
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1223
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1635
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1757





XE Login