INF: SQL Server 7.0 AutoStats 작업 방법

기술 자료 번역 기술 자료 번역
기술 자료: 195565 - 이 문서가 적용되는 제품 보기.
이 문서는 이전에 다음 ID로 출판되었음: KR195565
모두 확대 | 모두 축소

이 페이지에서

요약

새로 도입된 통계 관리 기능인 AutoStats는 다음과 같은 작업을 수행할 경우 프로덕션 시스템 상에서 원하지 않는 오버헤드를 생성할 수도 있습니다.
  • 대량 프로덕션 작업 동안 UPDATE STATISTICS를 시작하는 경우

    -또는-
  • 일정 시점에서 너무 많은 수의 UPDATE STATISTICS 프로세스를 시작하는 경우
본 문서에서는 어떤 상황에서 AutoStats가 발생하며 데이터베이스의 테이블에서 UPDATE STATISTICS를 실행하게 되는 상황은 언제인지 자세히 설명합니다.

추가 정보

일반 정보

SQL Server는 테이블과 인덱스에 있는 통계 정보에 대하여 비용 기준 최적화 프로그램(Cost-Based Optimizer)을 사용합니다. 그러므로 정확한 최신 통계 정보가 없다면 SQL Server에서는 특정 쿼리에 대한 최적의 실행 계획(Plan)을 결정할 수 없게 됩니다.

비용 기준 최적화 프로그램(Cost-Based Optimizer)을 보조하기 위해 SQL Server 7.0의 각 테이블에서 관리하는 통계의 대상은 다음과 같습니다.
  • 테이블에서 사용된 행 수
  • 테이블에서 사용된 페이지 수
  • 마지막 통계 업데이트 이후로 테이블의 키를 수정한 수
각 인덱스에 대하여 다음과 같은 추가 정보가 저장됩니다.
  • 첫 번째 열(Column)에 있는 같은 높이의 히스토그램
  • 모든 열 접두사(Column Prefix)에 대한 밀도(Density)
  • 평균 키 길이(Average Key Length)
인덱스에 대한 통계는 인덱스를 새로 만들 때마다 자동으로 작성됩니다. 또한 이제 다른 열(Column)에 대한 통계도 작성하여 유지 관리할 수 있게 되었습니다.

가능한 한 최신의 통계 정보를 관리하기 위해 SQL Server 7.0에서는 AutoStats를 시작하여 특정 변경 임계값에 도달했을 때 SQL Server의 테이블 수정 모니터링을 통해 테이블에 대한 통계를 자동으로 업데이트할 수 있습니다. 그리고, SQL Server 7.0은 자동 통계 작성(Auto-Create-Statistics)을 시작하여 특정 쿼리를 완벽하게 최적화하는데 필요한 모든 통계를 서버에서 자동으로 생성할 수 있도록 합니다.

AutoStats가 생성되는 시기 결정

앞에서 말한 바와 같이, AutoStats는 어떤 "변경 임계값"에 도달하게 되면 특정 테이블에 대하여 통계를 자동으로 업데이트합니다. sysindexes.rowmodctr 열(Column)은 시간이 지남에 따라 쿼리 프로세서의 결정 과정에 좋지 않은 영향을 줄 수 있는 테이블의 모든 수정 사항을 유지 관리합니다. 이 카운터는 다음과 같은 이벤트가 발생할 때마다 업데이트됩니다.
  • 한 행이 삽입되었습니다.
  • 한 행이 삭제되었습니다.
  • 인덱스된 열(Column)이 업데이트되었습니다.
테이블 통계가 업데이트되고 나면 rowmodctr 값은 0으로 다시 설정되며 테이블의 통계 스키마 버전이 업데이트됩니다.

그리고 저장 프로시저(Stored Procedure)의 실행 계획(Plan)을 캐시에서 가져와서 그 계획이 통계에 민감하게 반응하는 상황에서는 통계 스키마 버전을 현재의 버전과 비교합니다. 이 때 새로운 통계가 허용된다면 저장 프로시저(Stored Procedure)에 대한 계획(Plan)을 다시 컴파일하게 됩니다.

AutoStats 업데이트에 대한 기본 알고리즘은 다음과 같습니다.
  • tempdb 데이터베이스에 있는 어떤 테이블의 카디널리티가 6보다 작은 경우, 6번의 수정을 거칠 때마다 자동으로 테이블을 업데이트합니다.
  • 어떤 테이블의 카디널리티가 6보다는 크지만 500 이하일 경우에는 500번의 수정을 거칠 때마다 상태를 업데이트합니다.
  • 어떤 테이블의 카디널리티가 500보다 클 경우에는 500에다 테이블 카디널리티의 20%를 더한 만큼의 변경이 일어날 때 통계를 업데이트합니다.
참고: 아주 엄밀하게 말하자면 SQL Server는 카디널리티를 테이블의 행 수로 간주합니다.

다음의 두 가지 예는 이러한 개념을 잘 설명해 주고 있습니다.

예 1

pubs 데이터베이스에 있는 authors 테이블에 23개의 행과 두 개의 인덱스가 있다고 생각해 봅시다. 고유의 클러스터된 인덱스 UPKCL_auidind는 au_id라는 하나의 열(Column)에 인덱스되어 있으며, 복합된 클러스터되지 않은 인덱스 aunmind는 au_Iname과 au_fname이라는 열(Column)에 작성되었습니다. 이 테이블에 있는 행 수는 500 이하이므로 테이블 데이터가 500번 변경된 후에야 AutoStats가 시작됩니다. 이 때 변경은 au_Iname이나 그 이후에 발생한 모든 조합과 같은 인덱스된 열(Column)에서 500번 이상 일어난 삽입, 삭제, 변경 중의 하나를 말하는 것입니다.

그러므로, 사용자는 각 업데이트에 증분되는 sysindexes.rowmodctr 값을 모니터하여 UPDATE STATISTICS가 시작되는 시기를 예측할 수 있습니다. 즉, 그 값이 500 이상이 되면 UPDATE STATISTICS가 시작되는 것으로 예측할 수 있습니다.

예 2

카디널리티가 1,000인 또 다른 테이블 t2가 있다고 생각해 봅시다. SQL Server 7.0에서는 이 테이블의 행 수가 500보다 클 경우 500에다 테이블 카디널리티의 20%를 더한 횟수의 변경이 일어날 때 통계를 업데이트합니다. 계산하면 1,000의 20%는 200이므로, 테이블에서 대략 700번의 수정을 거친 후에야 AutoStats가 시작될 것이라는 결과를 예측할 수 있습니다.

AutoStats 결정 자동화

AutoStats의 실행 시기를 자동으로 결정하기 위해 사용자는 sysindexes 테이블을 폴링하고 테이블 수정이 시작 위치에 이르는 기점을 지정할 수 있습니다. 다음은 이 작업에 필요한 기본 알고리즘입니다.
if (sysindexes.rows > 500)
      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
      hours) //500 change leeway
         begin
            disable autostats
            log autostats disable
         end
      else
         begin
            stats ok
         end
   else
      if (sysindexes.rowmodctr >= 425) //75 change leeway
         begin
            disable autostats
            log autostats disable
         end

다음과 같은 작업을 예약할 수 있습니다.
  • 하루 동안 비활성화된 모든 테이블에 대하여 UPDATE STATISTICS를 실행하십시오.

    -그리고-
  • UPDATE STATISTICS가 실행되면 모든 테이블의 수정 카운터가 0으로 다시 설정되므로 AutoStats를 다시 활성화하십시오.

테이블에 대해 통계 UPDATE STATISTICS 실행 여부 제어

AutoStats에 문제가 있다고 판명되었을 때 이에 대한 가장 분명한 해결책은 AutoStats 생성을 비활성화하여 데이터베이스 관리자가 부담이 되지 않는 시간 내에서 UPDATE STATISTICS 일정을 자유롭게 정하도록 하는 것입니다. 이 작업은 UPDATE STATISTICS 문이나 sp_autostats 저장 프로시저(Stored Procedure)를 사용하여 수행할 수 있습니다. 먼저 UPDATE STATISTICS 문에서 사용하는 구문은 다음과 같습니다.
   UPDATE STATISTICS <table>...with NORECOMPUTE

sp_autostats 저장 프로시저(Stored Procedure)에서 사용하는 구문은 다음과 같습니다.
sp_autostats <table_name>, <stats_flag>, <index_name>

여기서 <stats_flag>는 "on" 또는 "off"입니다.

사용자는 또한 sp_dboption을 사용하여 데이터베이스 단위 수준에서 UPDATE STATISTICS 또는 CREATE STATISTICS가 자동으로 발생하지 않도록 할 수 있습니다.
sp_dboption <dbname>,'auto update statistics', <on | off>

-또는-

sp_dboption <dbname>,'auto update statistics', <on | off>

동시 UPDATE STATISTICS 프로세스 수 제어

현재 특정 테이블에 대한 AutoStats를 비활성화하지 않고는 동시에 실행할 자동 UPDATE STATISTICS 문의 수를 구성하는 것은 불가능하며, DCR 51539가 이를 위해 제시되었습니다. 하지만 서버는 동시 UPDATE STATISTICS의 수를 각 프로세서마다 4개로 제한합니다.

AutoStats 실행 시기 결정

추적(Trace) 플래그 205를 사용하여 각 통계별 저장 프로시저(Stored Procedure)가 AutoStats의 결과로 다시 컴파일되는 시기를 보고할 수 있습니다. 이 추적(Trace) 플래그는 오류 로그에 대하여 다음과 같은 메시지를 기록합니다.
1998-10-15 11:10:51.98 spid9 Recompile issued : ProcName: sp_helpindex
LineNo: 75 StmtNo: 29

추적(Trace) 플래그 205가 활성화되면 통계가 업데이트될 때 다음과 같은 메시지가 AutoStats의 8721 메시지를 대괄호로 묶습니다. 메시지를 여는 대괄호는 RowModCnt 값에 의하여 구별되는데 이 값은 0보다 큽니다. 그리고 UPDATE STATISTICS 다음의 닫는 대괄호는 0의 RowModCnt 값을 갖습니다.
1998-10-15 11:38:43,68 spid8 Schema Change: Tbl Dbid: 7 Objid:
133575514 RowModCnt: 60500 RowModLimit: 60499

이 메시지에서 "RowModCnt"는 테이블에 대한 총 수정 횟수입니다. "RowModLimit"는 임계값으로 이 값을 초과하면 해당 테이블에 대하여 UPDATE STATISTICS 문이 실행됩니다.

추적(Trace) 플래그 8721을 활성화시킬 수도 있는데, 이것은 AutoStats가 실행되었을 때 정보를 오류 로그로 덤프해 줍니다. 다음은 사용자가 볼 수 있는 메시지의 예입니다.
1998-10-14 16:22:13.21 spid13 AUTOSTATS: UPDATED Tbl: [authors]
Rows: 23 Mods: 501 Bound: 500 Duration: 47ms UpdCount: 2

이 메시지에서 "Mods"는 테이블에 대한 총 수정 횟수입니다. 또한 "Bound"는 수정 임계값이고 "Duration"은 UPDATE STATISTICS 문을 완료하는 데 걸리는 시간이며, "UpdCount"는 업데이트된 통계의 수입니다.

사용자는 또한 SQL Server Profiler(프로필러)를 사용하여 UPDATE STATISTICS 문을 실행하는 시기를 지정할 수 있습니다. 이렇게 하려면 다음 단계를 수행하십시오.
  1. Profiler 메뉴에서 Tools를 누른 다음 Options를 누릅니다.
  2. General 탭에서 Events로 가서 All Event Classes를 선택합니다.
  3. 새로운 추적(Trace)을 정의한 다음 Events에서 Misc를 선택합니다. Auto-Update Stats 하위 이벤트를 선택합니다.
참고: Autostats에서 다수의 통계를 업데이트하는 경우, 많은 수의 메시지가 오류 로그로 기록될 수 있습니다. 모든 프로덕션 또는 중요한 서버 상에서 사용하기 전에 이 추적(Trace) 플래그로 철저하게 검사하도록 합니다.

스키마 잠금

SQL Server 7.0에서는 두 가지 유형의 스키마 잠금을 사용하는데, 두 가지 모두 테이블에서 통계를 업데이트할 때 가져오는 것입니다.
   Sch-S: 스키마 안정성 잠금 
   ----------------------------- 
   이 유형의 잠금은 어떤 세션이 스키마 요소에서 스키마 안정성 잠금을 실행하는 
   동안 테이블이나 인덱스와 같은 스키마 요소가 손실되지 않도록 해줍니다. 

   Sch-M-UPD-STATS: 스키마 수정 잠금 
   ------------------------------------------- 
   이 잠금은 일정 시점에서 하나의 테이블에 대해 하나의 자동 UPDATE STATISTICS 
   프로세스를 실행하도록 시스템에서 사용하는 비블로킹 잠금입니다.  sp_lock 저장 
   프로시저는 이 잠금이 a type = TAB, resouce = UPD-STATS, mode = SCH-M을 
   가진다고 보고합니다.

이 두 가지 유형의 잠금을 보려면 sp_lock를 실행하거나 syslockinfo 테이블에서 선택하면 됩니다.

속성

기술 자료: 195565 - 마지막 검토: 1999년 8월 12일 목요일 - 수정: 1.0
본 문서의 정보는 다음의 제품에 적용됩니다.
  • Microsoft SQL Server 7.0 Standard Edition
키워드:?
KB195565

피드백 보내기

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com