SQL Server 833오류 (15 Sec Slow IO Detected)

 

·         Version : SQL Server

 

SQL Server I/O 요청 작업에서 15 이상I/O delay 발생하는 경우 833오류가 반환되고 에러로그에 기록 된다.

SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d).  The OS file handle is 0x%p.  The offset of the latest long I/O is: %#016I64x.

 

이전 버전의 SQL Server Lazy Writer  I/O Completion콜백 루틴을 활용하여 오류 상태를 반환하고 에러로그에 기록하였다최선 버전의 SQL Server 검색 기능을 개선하여 Scheduler Monitor 일부로 포함하였다모든 I/O 요청에는 시작시간(invariant RDTSC ticks), OVERLAPPED 구조  추가 추적 정보가 포함된다. I/O 추적 구조는 모두 동일한 메모리에서할당되므로 메모리가 부족할  I/O 수행해야하는 Memory manager/object에서 할당하려고 시도하지 않는다.

스케줄러 모니터는 움직임을 체크(5 간격)  I/O 엔트리와 OVERLAPPED 상태를 점검하여 I/O 요청이 여전히 커널에 보류중인지 확인한다. 10진수 (Overlaaped->internal) 259이면 커널 수준에서 I/O 보류 중이고 경과 시간이 15 이상이되면  느린 I/O 상태로 기록된다.



 프로세스는 I/O 요청 디스펜서와 1024개의 검사 제한을 사용하여Scheduler Monitor 단일 검사를 통과하는 I/O 요청을사용하여 조금더 복잡해졌다하지만 기본적으로 SQL Server I/O 추적 목록을 실행하고 HasOverlappedioCompleted결과 OVERLAPPED->internal 값이  이상 259 아니라면 I/O 처리에서 SOS 스케줄링 문제가 발생하며 느린 I/O 기록하지 않는다.

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

 

Sys.dm_os_pending_io_requests DMV I/O 상태를   있으며 *_pending_* 컬럼 값을 참고 한다.  스케줄러 모니터가 모니터 역할을 하므로 Windows에서 I/O 발생하는 타이머가 없다.

SELECT *  FROM sys.dm_io_pending_io_requests

 

SQL Server 오류 로그에  오류를 기록  때오류 로그 범람을 방지하기 위해 파일  5분의 간격을 유지 관리한다문제를해결할  XEvent  나은 대상이   있다.(대부분의 경우 기록시 XeSqlPkg::long_io_detected 실행됨)

 

요약하면 Scheduler Monitor I/O 상태를 모니터링하는 작업을 수행하며 보류 중일때 15 이상이 지나면833 오류가 반환되고 에러로그에 기록 된다.

 

 

[참고자료]

·         https://blogs.msdn.microsoft.com/bobsql/2016/09/01/how-it-works-how-is-sql-server-error-833-15-sec-io-detected/

·         http://www.proforanalytics.com/io-performance/

·         https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/09/troubleshooting-sql-server-io-requests-taking-longer-than-15-seconds-io-stalls-disk-latency/

·         http://technet.microsoft.com/en-in/library/cc966414(en-us).aspx – Physical Database Storage Design

·         http://technet.microsoft.com/en-us/library/aa997558(EXCHG.65).aspx – Ruling Out Disk-Bound Problems

·         http://www.microsoft.com/downloads/en/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en – SQLIO Disk Subsystem Benchmark Tool

·         http://msdn.microsoft.com/en-us/library/aa365199(v=VS.85).aspx – I/O Concepts (Windows)

 



MS SQL, SQL Server, Error 833, Slow I/O Detected, Lazy Writer, IO Tracking, I/O Request, sys.dm_os_pending_io_requests, I/O pending, SQL Disk, SQL IO



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp





profile

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

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.