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

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

DISK I/O 병목 확인

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

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
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1562
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1927
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 841
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1637
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 5041
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1106
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1298
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1069
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1216
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1358
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1260
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1660
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1791
1810 DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4732
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1175
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1308
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2370
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2234
» DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 4070
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1500





XE Login