데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

 

[참고자료]

 


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

No. Subject Author Date Views
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3626
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1582
» SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1694
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1432
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1308
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1560
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1237
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3594
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1238
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1179
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1065
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1129
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1301
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1652
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1228
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1418
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1066
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 958
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3104





XE Login