MSSQL 데이터 i/o 병목현상 확인하기(SQL서버 느려짐)

MSSQL 데이터베이스를 운영하면서 생긴 경험을 공유하고자 작성합니다. 결론부터 말하면 윈도우서버의 I/O 처리에 문제가 발생하였기 때문이었습니다. I/O 문제를 직면하고 확인하는 과정을 설명하겠습니다.

 

 

 

 

 

1. 오류증상 확인을 위한 SQL 로그 확인 방법입니다.

MSSQL 데이터베이스를 운영하는 중에 갑자기 데이터베이스가 느려 졌습니다. 문제가 생기면 서버나 네트워크 장비나 데이터베이스나 로그를 확인을 하는게 먼저입니다. SQL 로그를 확인하는 방법입니다.

1) SSMS(SQL Server Management Studio) 에 접속합니다.

2) 왼쪽에 데이터베이스 트리를 열어 '관리'에 'SQL Server 로그' 하위 트리를 열어줍니다.

3) 현재라고 적혀져 있는 목록에 마우스 오른쪽 클릭 후 'SQL Server 로그보기(V)' 를 선택합니다.

4) 로그 파일 뷰어가 실행되면 로그를 확인합니다. 상단에 필터 버턴을 눌러서 조건별로 필터링도 가능합니다.

 

 

 

 

 

2. 해당 내용과 관련된 SQL 오류메시지를 확인합니다.

SQL서버의 로그를 확인한 결과 아래와 같은 오류 메시지를 확인 할 수 있었습니다.

이 오류 메시지는 SQL Server I/O 에 대한 병목 현상을 나타냅니다.

sql server has encountered 1 occurrence(s) of i/o requests taking longer than 15 seconds to complete

autogrow of file 'templog' in database 'tempdb' was cancelled by user or timed out after 117437ms

 

3 오류내용 처리를 위해 현재 대기 보류 중인 I/O를 확인 합니다.

아래의 쿼리문을 실행하여 보유중인 I/O를 조회합니다.

SQL> SELECT SUM(pending_disk_io_count) AS [Number of pending I/Os] FROM sys.dm_os_schedulers

쿼리 조회결과 화면입니다. 대기 보류중인 I/O가 없으면 아래의 그림과 같이 'Number of pending I/Os'가 0으로 보이지만 장애 발생 시점에는 숫자가 0이 아닌 다른 숫자로 증가된 값이 보일 것입니다.

 

4 3번의 쿼리 결과를 확인하고 I/O 카운터에 대하여 세부 사항을 확인 합니다.

아래의 쿼리를 실행하여 세부사항 결과를 조회합니다.

SQL> SELECT *  FROM sys.dm_io_pending_io_requests

IO_Pending 열 값이 0 이면 SQL Server 는 아직 I/O 를 처리 하지 않은 것 입니다.

IO_Pending 열 값이 1 이면 Windows 에서 아직 I/O 를 처리 하지 않은 것 입니다.

 

 

 

 

5. I/O Pending 결과 값에 따른 조치 사항입니다.

쿼리 결과 조회 시 IO_Pending 열 값이 1 로 나타났고 Windows 에서 아직 I/O 를 처리 하지 않은 것으로 결론을 내렸습니다.

담당 엔지니어에게 윈도우서버의 하드디스크와(스토리지를 사용 중 이면 스토리지 디스크를 확인합니다.)

그리고 연결된 네트워크단 스위치(스토리지 사용 중이었기 때문에 san 스위치 점검이 필요합니다.) 점검을 요청했습니다

 

6. I/O Pending 결과 값에 따른 처리 결과입니다.

점검결과 SQL 데이터베이스 서버와 연결된 스위치 쪽 GBIC Fault 문제로 발생한 SQL Server I/O 병목 현상으로 확인이 되었고 GBIC을 교체한 후로는 문제없이 정상적으로 돌아왔습니다.

 

마지막으로 현재 대기 보류 중인 I/O를 다시 확인해 봅니다.

SQL> SELECT SUM(pending_disk_io_count) AS [Number of pending I/Os] FROM sys.dm_os_schedulers

쿼리 조회 시 'Number of pending I/Os'가 0 으로 표시되는 것을 확인할 수 있습니다.

 

이상으로 데이터베이스가 갑자기 느리게 되는 현상에 대한 원인과 해결방안에 대해서 알아보았습니다.