웹 프로그램 또는 응용 프로그램(애플리케이션 프로그램: Application program)에서 SQL server(MsSql) 저장 프로시저를 사용하여 잘 동작하던 프로그램이 어느 순간부터 화면 응답속도가 현저히 저하되는 현상이 발생하고 원인을 찾기위해 SSMS(Microsoft SQL Server Management Studio)로 해당 프로시저를 실행해보면 빠른 처리 속도를 보이고 실행계획을 확인해봐도 속도가 느릴만한 특이점이 없는 문제가 발생했다면 다음 사항을 확인해 보기 바랍니다.
증상
– SQL Server 를 사용중 웹프로그램 또는 응용 프로그램에서 프로시저 실행속도가 현저히 느림
– SSMS로 프로시저를 실행해보면 처리 속도 빠름
– 실행계획에도 특이점 없음
확인해볼 사항
– 웹 프로그램 또는 응용프로그램에서 DB 접속 및 실행시 SET ARITHABORT 설정을 확인
– “SET ARITHABORT OFF” 로 되어 있으면 “ON”으로 변경 검토
SET ARITHABORT 는 무엇인가?
MS(MicroSoft) SQL Server 공식 문서에서 “SET ARITHABORT(Transact-SQL)”를 찾아보면
“쿼리 실행 중 오버플로 또는 0으로 나누기 오류가 발생하면 쿼리를 종료합니다.” 라고 적혀 있습니다.
위 링크에서 MS SQL Server 문서를 참고 바랍니다.
SET ARITHABORT 무엇이 문제인가?
그 밑에 설명에는 다음과 같이 적혀 있습니다.
“로그온 세션에서 ARITHABORT를 항상 ON으로 설정합니다. ARITHABORT를 OFF로 설정하면 쿼리 최적화에 부정적인 영향을 주어 성능 문제가 발생할 수 있습니다.”
그리고 바로 경고 문구가 보이는데
“SQL Server Management Studio에 대한 기본 ARITHABORT 설정은 ON입니다. ARITHABORT를 OFF로 설정하는 클라이언트 애플리케이션에서 다른 쿼리 계획을 받아 성능이 저조한 쿼리 문제를 해결하기 어려울 수 있습니다. 즉, 같은 쿼리가 Management Studio에서는 빨리 실행되지만, 애플리케이션에서는 느리게 실행될 수 있습니다. Management Studio로 쿼리 문제를 해결할 때 항상 클라이언트 ARITHABORT 설정을 일치시키세요.”네. “경고문구에 분명히 명시하고 있듯이 SSMS에서는 빠르게 실행되지만 애플리케이션에서는 느리게 실행 될 수 있습니다” 라고 명시되어 있습니다.
SET ARITHABORT 기본 설정은 어떻게 되어 있는데?
| 구분 | ARITHABORT | ANSI_WARNINGS |
| Application using ADO .Net, ODBC or OLE DB | OFF | ON |
| SSMS | ON | ON |
| SQLCMD, OSQL, BCP, SQL Server Agent | OFF | ON |
| ISQL, DB-LIBRARY | OFF | OFF |
SSMS 기본설정값은 “도구 > 옵션 > 쿼리 실행 > 고급”에서 “SET ARITHABORT” 가 체크되어 있는것을 확인 할 수 있다
“SET ARITHABORT ON” 으로 바꾸면 되는가?
결론부터 말하면 그렇다.
“SET ARITHABORT ON”으로 변경하면 응용 프로그램 속도가 빨라지지만 ANSI_WARNINGS 설정에 따라 처리중인 프로시저의 바로 종료 유무와 트랜잭션 롤백이 동작할 수 있으니 밑의 표를 보고 ANSI_WARNINGS 설정도 기본값이 “ON” 이지만 필요하다면 따라 “OFF”로 변경한다.
| ARITHABORT | ANSI_WARNINGS | 결과값 |
| ON | ON | 쿼리 종료(결과 없음) |
| ON | OFF | 일괄 처리 종료, 트랜잭션 롤백 |
| OFF | ON | 쿼리 종료(결과 없음) |
| OFF | OFF | 결과 NULL |
SET ARITHABORT, ANSI_WARNINGS 현재 설정값 보기
SET ARITHABORT의 현재 설정을 보려면 다음 쿼리를 실행합니다.
DECLARE @ARITHABORT VARCHAR(3) = 'OFF';
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';
SELECT @ARITHABORT AS ARITHABORT;
ANSI_WARNINGS의 현재 설정을 보려면 다음 쿼리를 실행합니다.
DECLARE @ANSI_WARN VARCHAR(3) = 'OFF';
IF ( (8 & @@OPTIONS) = 8 ) SET @ANSI_WARN = 'ON';
SELECT @ANSI_WARN AS ANSI_WARNINGS;
그외 MS 문서에는 SET ARITHABORT에 설명이 한참 적혀 있는데
“6. SET ARITHABORT = “ON” 으로 바꾸면 되는가?” 에 요약해서 적어뒀으나 궁금하신 분은 자세히 읽어 보기 바란다.
SET ARITHABORT 및 SET ANSI WARNINGS이 ON이면 해당 오류 조건으로 인해 쿼리가 종료됩니다.
SET ARITHABORT가 ON이고 SET ANSI WARNINGS가 OFF이면 해당 오류 조건으로 인해 일괄 처리가 종료됩니다. 트랜잭션에서 해당 오류가 발생하면 트랜잭션이 롤백됩니다. SET ARITHABORT가 OFF이고 해당 오류 중 하나가 발생하면 경고 메시지가 나타나고 산술 연산의 결과가 NULL입니다.
SET ARITHABORT 및 SET ANSI WARNINGS이 OFF이고 해당 오류 중 하나가 발생하면 경고 메시지가 나타나고 산술 연산의 결과가 NULL입니다.
참고
SET ARITHABORT 및 SET ARITHIGNORE이 둘 다 ON이 아니면 SQL Server에서 NULL을 반환하고 쿼리가 실행된 후 경고 메시지가 나타납니다.
ANSI_WARNINGS의 값이 ON이고 데이터베이스 호환성 수준이 90 이상으로 설정된 경우, ARITHABORT는 값 설정과 관계없이 암시적으로 ON이 됩니다. 데이터베이스 호환성 수준이 80 이하로 설정된 경우에는 명시적으로 ARITHABORT 옵션을 ON으로 설정해야 합니다.
식 평가의 경우 SET ARITHABORT가 OFF이고 INSERT, UPDATE 또는 DELETE 문에서 산술, 오버플로, 0으로 나누기 또는 도메인 오류가 나타나면 SQL Server에서 NULL 값을 삽입하거나 업데이트합니다. 대상 열이 Null 허용이 아니면 삽입이나 업데이트 동작이 실패하고 사용자에게 오류 메시지가 표시됩니다.
SET ARITHABORT 또는 SET ARITHIGNORE 옵션 중 하나가 OFF이고 SET ANSI_WARNINGS가 ON이면 SQL Server에서 0으로 나누기 또는 오버플로 오류가 발생할 경우 여전히 오류 메시지를 반환합니다.
SET ARITHABORT가 OFF이고 IF 문의 부울 조건을 평가하는 동안 중단 오류가 발생하면 FALSE 분기가 실행됩니다.
계산 열이나 인덱싱된 뷰에서 인덱스를 만들거나 변경할 경우 SET ARITHABORT는 ON이어야 합니다. SET ARITHABORT가 OFF이면 계산 열 또는 인덱싱된 열에 인덱스가 있는 테이블에서 CREATE, UPDATE, INSERT, DELETE 문이 실패합니다.
SET ARITHABORT는 실행 시간 또는 런타임에 설정되며, 구문 분석 시에는 설정되지 않습니다.