SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성

SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 AUTO_UPDATE-STATISTICS_ASYNC(비동기 통계 업데이트) 옵션은 자동 통계 업데이트에 영향을 준다. 쿼리 최적화 프로그램이 동기 또는 비동기 통계 업데이트를 사용하는지를 결정 한다. AUTO_UPDATE-STATISTICS_ASYNC의 기본 값은 OFF 이다.

 

동기 통계 업데이트의 경우 쿼리는 항상 최신 통계로 컴파일 하고 실행 한다. 통계가 최신이 아닌 경우 쿼리를 컴파일 하고 실행하기 전에 쿼리 최적화 프로그램에서 업데이트 된 통계를 기다린다.

[사용 경우]

• 대량 업데이트를 수행 하는 경우와 같이 데이터 분포를 변경하는 작업을 수행 할 때는 동기 통계를 사용하는 것이 좋다.
• 작업을 완료 후 통계를 업데이트 하지 않은 경우 동기 통계를 사용하면 변경된 데이터에 대해 쿼리를 실행 하기전 통계를 최신 상태로 유지할 수 잇다.

 

비동기 통계 업데이트의 경우 쿼리는 기존 통계가 최신이 아닌 경우에도 기존 통계로 컴파일 한다. 쿼리가 컴파일 될 때 사용된 통계가 오래된 통계인 경우 쿼리 최적화 프로그램은 최적의 쿼리를 선택하지 못할 수도 있다. 비동기 업데이트가 완료 된 이후 컴파일된 쿼리는 업데이트 된 통계의 이점을 활용 할 수 있다.

[사용 경우]

• 빠른 계획 생성이 가능하지만 오래된 통계의 사용으로 최적의 쿼리를 사용하지 못 할 수 있다.
• 응용 프로그램에서 동일한 쿼리, 또는 유사한 쿼리를 사용하여 캐시된 실행 계획을 자주 사용하는 경우 최적화 프로그램은 통계를 기다리지 않고 들어오는 쿼리를 수행. 예상 가능한 쿼리 응답시간을 얻을 수 있다. 이는 일부 쿼리의 지연을 방지 할 수 있다.
• 응용 프로그램에서 통계 업데이트를 기다리는 하나 이상의 쿼리로 인해 클라이언트 요청 제한 시간을 초과하는 비동기 통계를 사용하여 프로그램 실패를 방지 할 수 있다.

 

 

실습을 통해 비동기 통계 업데이트 활성 / 비활성을 변경해 보자. 다음 스크립트를 통해 데이터베이스의 비동기 통계 활성화 여부를 확인 할 수 있다.

SELECT     name, is_auto_update_stats_on, is_auto_update_stats_async_on FROM sys.databases

 

 

0 - 비활성화

1 - 활성화

 

[SSMS에서 옵션 변경]

데이터베이스 속성 – [옵션] 탭에서 변경 할 수 있다.

 

 

 

[쿼리를 이용한 변경]

-- Enabled ALTER DATABASE [AdventureWorks2008R2] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT   --Disabled ALTER DATABASE [AdventureWorks2008R2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT

 

 

 

그렇다면 위의 옵션을 언제 사용 하면 좋을까? 개인 적인 생각으로는 OLTP와 같은 대량의 트랜잭션이 발생하는 시스템에 대해서는 활성화 해야 된다고 생각한다. 자신이 관리하는 시스템의 특성을 잘 파악하여 충분한 테스트 후 사용하길 바란다.

 

참고로 SQL Server 2008 이상의 버전에서 비동기 통계 업데이트 사용시 메모리 누수가 있다고 한다. 다음 링크를 참고 하여 핫픽스를 적용하도록 하자.

비동기 통계업데이트 메모리 누수 관련 : http://support.microsoft.com/kb/2778088

 

 

[참고자료]

• http://www.mssqltips.com/sqlservertip/2904/sql-servers-auto-update-statistics-async-option/
• http://msdn.microsoft.com/ko-kr/library/ms190397.aspx
• http://msdn.microsoft.com/ko-kr/library/bb522682(v=SQL.105).aspx
• http://support.microsoft.com/kb/2778088

 

2013-03-28 / 강성욱 / http://sqlmvp.kr