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

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

DISK I/O 병목 확인

jevida(강성욱) 2016.09.29 03:57 Views : 3718

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

 

 


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

No. Subject Author Date Views
1810 DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4395
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1156
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1285
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2235
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2138
» DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 3718
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1251
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1129
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1070
1801 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2398
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1049
1799 Hot Add CPU jevida(강성욱) 2016.09.29 849
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1000
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1692
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1392
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1231
1794 메모리 관리 아키텍처 – NUMA 지원 방법 jevida(강성욱) 2016.09.29 1545
1793 메모리 관리 아키텍처 – NUMA(Non-Uniform Memory Access)이해 jevida(강성욱) 2016.09.29 1415
1792 메모리 관리 아키텍처 – Hot Add 메모리 jevida(강성욱) 2016.09.28 929
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1053





XE Login