SQL Server nonpreemptive 모드에서Long Sync IO 오류

·         Version : SQL Server

SQL Server nonpreemptive모드에서  1000ms 보다 오래 걸리는 I/O 동기화 작업이 발생하면  SQL Server에러로그에 아래와 같은 로그가 기록된다.

SQL Server가 수행하는 I/O 유형은 일반적으로 2가지가 있다.

·         Async (대부분의SQL Server I/O 방식https://technet.microsoft.com/en-us/library/aa175396(v=sql.80).aspx)

·         Sync

이번 포스트에서 다룰 내용은 Sync variety이다. 간단히 말하면 입출력이 완료 될 때까지 스레드는 대기한다. 기본적으로SQL Server가 동기화를 위해 수행하는 작업은 I/O호출(WriteFile, ReadFile, FlushFileBuffers 등)을 타이머로 래핑하는 것이다. 순서는 아래와 같다.

1.       SQL Server 에러로그에 메시지 작성

2.       타이머 시작

3.       WriteFile 호출 : I/O 작업이 완료될 때까지 API가 호출 스레드로 제어를 반환하지 않음

4.       타이머 정지

5.       경과시간이 > 1000ms 이고 작업자가 nonpremeemptive 모드 인경우 I/O 문제를 반환

즉 Sync IO 에러로그는SQL Server 스레드(작업자)가 1000ms 보다 오래 걸린 API 호출을 수행했음을 의미한다. 어느 한 시점에서 하나의 작업자만 스케줄러를 소유 할 수 있기 때문에 스케줄러가 어떠한 느린 조건을 만났음을 의미한다. SQL Server 스케줄러 컨텍스트 스위치는 약 4ms이므로 1000ms 속도는 SQL Server 처리에 영향을 미칠 수 있다. SQL Server는 오류로그에 기록 함으로써 사용자가 해결해야할 I/O 병목 현상을 알려준다. 해당 문제가 발생하면 아래 링크를 참고하여 문제를 해결 할 수 있도록 한다.

·         https://blogs.msdn.microsoft.com/psssql/2008/03/03/how-it-works-debugging-sql-server-stalled-or-stuck-io-problems-root-cause/

·         https://blogs.msdn.microsoft.com/psssql/2010/03/24/how-it-works-bob-dorrs-sql-server-io-presentation/

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2016/08/17/how-it-works-sync-ios-in-nonpreemptive-mode-longer-than-1000-ms/

SQL Server, MSSQL, Sync IO, I/O operation, IO동기화, DBA, DB Tuning, SQL Scheduler, I/O stall problem

강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp