DISK I/O 병목 확인

DISK I/O 병목 확인

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

디스크의 성능은 SQL Server를 운영하는데 매우 밀접한 관련이 있다. 따라서 우리는 SQL Server의 논리적 및 물리적 I/O를 최소화 해야 한다. 물리적 I/O는 디스크에서 읽고 쓸 때 발생한다. I/O 관련 데이터베이스 관리 개체(I/O-related database management objects (DMOs))는 시스템에서 일어나고 있는 I/O를 조사하는데 도움을 준다.

 

DMO에는 디스크 서브 시스템의 관점에서 디스크 I/O의 명시적인 그림을 제공한다. 예를 들어 어떻게 I/O가 디스크의 여러 파일에 분산되는지, 어떤 곳에서 I/O 병목이 발생하는지 보여준다.

우리는 디스크 서브시스템의 아키텍처를 최적화 하기 위해 이 정보를 사용 할 수 있다. 또한 데이터를 수집하고 더 많은 저장 용량을 요청할 때 자료로 사용 할 수도 있다.

 

SQL Servers는 모든 삽입에 대한 트랜잭션 로그 기록, 업데이트 및 삭제 기록, 대량 작업을 기록 해야 하므로 물리적인 I/O는 피할 수 없다. 하지만 디스크 파워를 높이기 전에 쿼리 튜닝과 인덱스 등으로 논리적 및 물리적 I/O를 최소화 할 수 있다.

 

우리는 DMO의 I/O 정보 (sys.dm_io_ 로 시작하는)와 파생된 DMV를 참고 하여 I/O 성능을 고려해야 한다.

 

[캐시된 쿼리 계획에 대한 집계 성능 통계]

select * from sys.dm_exec_query_stats

 

 

[SQL Server 연결에 대한 세부 정보]

select * from sys.dm_exec_connections

 

 

 

[활성 사용자 연결 및 내부 태스크에 대한 정보]

select * from sys.dm_exec_sessions

 

 

 

[시스템의 작업자 정보]

select * from sys.dm_os_workers

 

 

 

[Disk Bottlenecks via I/O Stalls]

데이터 및 로그 파일에 대한 I/O 통계는 sys.dm_io_virtual_file_stats 로 확인 할 수 있다. 반환되는 값은 SQL Server가 마지막으로 시작된 시점부터 지속적으로 증가한다. 우리는 기본 측정을 한 다음 실제 측정을 수행 해야 한다.

 

아래 스크립트는 실행 중인 모든 데이터베이스의 데이터 및 로그 파일의 읽기 및 쓰기의 정보를 볼 수 있다. 이 정보에서 디스크 리소스에 따라 대기하는 개별 파일을 찾을 위치를 찾을 수 있으며 특정 파일의 디스크 병목 현상을 확인 할 수 있다.

-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT     DB_NAME(database_id) AS [Database Name],     file_id,     io_stall_read_ms,     num_of_reads ,     CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms],     io_stall_write_ms,     num_of_writes,     CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms],     io_stall_read_ms + io_stall_write_ms AS [io_stalls],     num_of_reads + num_of_writes AS [total_io],     CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] FROM sys.dm_io_virtual_file_stats(NULL, NULL) ORDER BY avg_io_stall_ms DESC ;

 

 

 

[Disk Bottlenecks via Pending I/O]

보류 중인 I/O 요청에 대한 정보는 sys.dm_io_pending_io_requests 를 사용한다. 스크립트를 실행 하는 순간 시스템에 보류중인 I/O 요청의 스냅샷을 제공 한다.

 

-- Look at pending I/O requests by file SELECT      DB_NAME(mf.database_id) AS [Database],      mf.physical_name,      r.io_pending,      r.io_pending_ms_ticks,      r.io_type,      fs.num_of_reads,      fs.num_of_writes FROM     sys.dm_io_pending_io_requests AS r         INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs             ON r.io_handle = fs.file_handle         INNER JOIN sys.master_files AS mf             ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id ORDER BY r.io_pending, r.io_pending_ms_ticks DESC ;

 

 

 

우리는 이와 같은 다양한 정보를 통해 특정 드라이브에 사용할 RAID 레벨을 결정하거나 시스템의 성능을 측정할 때 많은 참고자료로 활용 할 수 있다.

 

[참고자료]

SQL Server: Minimize Disk I/O :

http://technet.microsoft.com/ko-kr/magazine/jj643251(en-us).aspx

 

 

2013-06-28 / 강성욱 / http://sqlmvp.kr