인덱스 통계
업데이트: 2006년 12월 12일
SQL Server 2005에서는 열의 값 배포에 대한 통계 정보를 생성하도록 허용합니다. 쿼리 최적화 프로그램에서는 이 통계 정보를 바탕으로 인덱스 사용 비용을 예측하고 쿼리를 평가하여 최적의 쿼리 계획을 결정합니다.
통계가 생성되면 데이터베이스 엔진에서는 통계가 작성되는 열의 값을 정렬한 다음 이들 값을 최대 200개까지 간격으로 구분하여 표시한 히스토그램을 만듭니다. 히스토그램은 각 간격 값과 일치하는 행 수, 간격 내에 포함된 행 수 및 간격 내 값의 밀도 계산이나 중복 값의 빈도를 지정합니다.
SQL Server 2005에서는 char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text 및 ntext 열에 대해 생성되는 통계에서 추가 정보를 수집하도록 합니다. 문자열 요약이라고 하는 이러한 추가 정보를 바탕으로 쿼리 최적화 프로그램은 문자열 패턴에 대한 쿼리 조건자의 선택도를 예측할 수 있습니다. LIKE 조건이 쿼리에 있으면 문자열 요약을 이용하여 결과 집합 크기를 보다 정확하게 예측하고 많은 경우 쿼리 계획을 개선할 수 있습니다. 여기에는 WHERE ProductName LIKE '%Bike' 및 WHERE Name LIKE '[CS]heryl' 조건이 포함됩니다.
[!참고] 열 샘플 요약이 데이터베이스 엔진에서 유지 관리할 수 있는 것보다 클 경우 문자열 요약 정보가 유지 관리되지 않습니다. 예를 들어 85,000행이 있는 테이블의 경우 문자열 간에 유사성이 거의 없으며 각 문자열에 80자가 있는 고유한 varchar(80) 열에 대해 WITH FULLSCAN을 사용하여 통계를 생성했다면 문자열 요약이 유지 관리되지 않습니다. 특정 통계 개체에 대한 문자열 요약의 저장 여부를 결정하려면 DBCC SHOW_STATISTICS(Transact-SQL)를 사용합니다.
자동 통계 작업 방법
사용자가 인덱스를 만들 때 쿼리 최적화 프로그램에서는 인덱싱된 열에 대한 통계 정보를 자동으로 저장합니다. 또한 AUTO_CREATE_STATISTICS 데이터베이스 옵션을 ON(기본값)으로 설정하면 데이터베이스 엔진에서는 조건자에 사용되는 인덱스 없이 열의 통계를 자동으로 만듭니다.
열의 데이터가 변경되면 인덱스 및 열 통계가 최신이 아니기 때문에 쿼리 최적화 프로그램이 쿼리를 처리할 최적의 방법을 결정할 수 없습니다. 예를 들어 인덱싱된 열과 1,000개의 데이터 행이 있는 테이블이 생성되면 쿼리 최적화 프로그램에서는 인덱싱된 열을 사용하여 쿼리에 필요한 데이터를 수집하는 것이 좋다고 판단합니다. 열의 데이터가 업데이트되어 많은 값이 중복되면 그 열은 더 이상 쿼리에서 사용하기에 이상적인 후보가 아닙니다. 그러나 쿼리 최적화 프로그램에서는 업데이트 이전의 데이터에 기초하는 인덱스의 오래된 배포 통계를 기준으로 여전히 그 열을 이상적인 후보로 간주합니다.
[!참고] SQL Server Management Studio를 사용하여 쿼리 실행 계획을 그래픽으로 표시할 때 오래되거나 누락된 통계는 테이블 이름을 빨간 문자열로 나타내어 경고로 표시합니다. 자세한 내용은 그래픽 실행 계획 표시(SQL Server Management Studio)를 참조하십시오. 또한 SQL Server 프로파일러를 사용하여 누락된 열 통계 이벤트 클래스를 모니터링하면 통계가 언제 누락되었는지 나타납니다. 자세한 내용은 Errors and Warnings 이벤트 범주(데이터베이스 엔진)를 참조하십시오.
AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 ON(기본값)으로 설정하면 쿼리 최적화 프로그램에서는 테이블의 데이터가 변경됨에 따라 주기적으로 이 통계 정보를 업데이트합니다. 쿼리 실행 계획에 사용되는 통계가 현재 통계 테스트에 실패할 때마다 통계 업데이트가 시작됩니다. 샘플링은 데이터 페이지에서 임의로 이루어지고 통계에서 필요로 하는 테이블이나 열의 가장 작은 비클러스터형 인덱스에서 수행됩니다. 디스크에서 데이터 페이지를 읽으면 데이터 페이지의 모든 행을 사용하여 통계 정보를 업데이트합니다. 대부분의 경우 데이터 행의 약 20%가 변경되면 통계 정보가 업데이트됩니다. 그러나 쿼리 최적화 프로그램에서는 항상 최소한의 행 수를 샘플링합니다. 8MB보다 작은 테이블은 항상 완전히 검색하여 통계를 수집합니다.
모든 데이터를 분석하는 대신 데이터를 샘플링하여 자동 통계 업데이트의 비용을 최소화합니다. 어떤 경우에는 통계 샘플링이 테이블 데이터의 특성을 정확히 집어내지 못할 때도 있습니다. UPDATE STATISTICS 문의 SAMPLE 및 FULLSCAN 절을 사용하면 테이블 단위로 수동 통계 업데이트를 할 때 샘플링되는 데이터의 양을 조절할 수 있습니다. FULLSCAN 절을 사용하면 테이블의 모든 데이터를 검색하여 통계를 수집할 수 있습니다. SAMPLE 절을 사용하면 샘플링할 행의 비율이나 수를 지정할 수 있습니다.
비동기 통계 업데이트
오래된 통계의 업데이트를 시작하는 쿼리는 통계 업데이트가 끝나야만 결과 집합을 컴파일하여 반환할 수 있습니다. 이로 인해 쿼리 응답 시간을 예상하지 못할 수 있으며 엄격한 시간 제한이 있는 응용 프로그램은 실패할 수 있습니다.
SQL Server 2005의 AUTO_UPDATE_STATISTICS_ASYNC 데이터베이스 옵션은 비동기 통계 업데이트 기능을 제공합니다. 이 옵션을 ON으로 설정하면 통계가 업데이트될 때까지 쿼리 컴파일을 미루지 않습니다. 대신 오래된 통계가 큐에 위치되어 작업자 스레드에 의해 백그라운드 프로세스로 업데이트됩니다. 해당 쿼리 및 기타 다른 동시 쿼리는 기존의 오래된 통계를 사용하여 즉시 컴파일을 시작합니다. 통계 업데이트로 인한 지연이 없으므로 쿼리 응답 시간은 예상 가능하지만 통계가 오래된 것이기 때문에 쿼리 최적화 프로그램에서 덜 효율적인 쿼리 계획을 선택할 수 있습니다. 업데이트된 통계가 준비된 후 시작되는 쿼리는 업데이트된 통계를 사용합니다. 그러면 오래된 통계를 바탕으로 하는 캐시된 계획은 다시 컴파일해야 할 수도 있습니다. CREATE, ALTER 및 DROP 문과 같은 DDL(데이터 정의 언어) 문이 동일한 명시적 사용자 트랜잭션에서 실행되는 경우 비동기 통계를 업데이트할 수 없습니다.
AUTO_UPDATE_STATISTICS_ASYNC 옵션은 데이터베이스 수준으로 설정되며 데이터베이스의 모든 통계에 대한 업데이트 방식을 결정합니다. 이 옵션은 통계 업데이트에만 적용되며 비동기적으로 통계를 생성하는 데 사용할 수 없습니다. 이 옵션은 ON으로 설정하더라도 AUTO_UPDATE_STATISTICS가 ON으로 설정되어 있지 않는 한 적용되지 않습니다. 기본적으로 AUTO_UPDATE_STATISTICS_ASYNC 옵션은 OFF로 설정되어 있습니다. 이 옵션 설정에 대한 자세한 내용은 ALTER DATABASE(Transact-SQL)를 참조하십시오.
데이터베이스를 SINGLE_USER로 설정하기 전에 AUTO_UPDATE_STATISTICS_ASYNC 옵션이 OFF로 설정되어 있는지 확인합니다. 이 옵션이 ON으로 설정되어 있으면 통계 업데이트에 사용되는 백그라운드 스레드가 데이터베이스에 대해 연결하고 사용자가 단일 사용자 모드로 데이터베이스에 액세스할 수 없습니다. 옵션이 ON으로 설정되어 있으면 다음 작업을 수행합니다.
- AUTO_UPDATE_STATISTICS_ASYNC를 OFF로 설정합니다.
- sys.dm_exec_background_job_queue 동적 관리 뷰를 쿼리하여 활성 비동기 통계 작업을 검사합니다.
- 활성 작업이 있을 경우 KILL STATS JOB을 사용하여 해당 작업을 완료하도록 허용하거나 수동으로 종료합니다.
최선의 구현 방법 고려 사항
사용자 응용 프로그램에 다음과 같은 특징이 있는 경우 AUTO_UPDATE_STATISTICS_ASYNC 옵션을 ON으로 설정하는 것이 좋습니다.
- 통계 업데이트를 기다리는 하나 이상의 쿼리로 인해 클라이언트 요청 제한 시간을 초과한 경험이 있는 경우
- 오래된 통계로 인해 덜 효율적인 쿼리 계획으로 쿼리를 실행해야 하는 경우가 있더라도 쿼리 응답 시간을 예측할 필요가 있는 경우
비동기 업데이트 통계 속성 보기
AUTO_UPDATE_STATISTICS_ASYNC 옵션의 ON/OFF 상태를 보려면 sys.databases 카탈로그 뷰에서 is_auto_update_stats_async_on 열을 선택합니다. 자세한 내용은 sys.databases(Transact-SQL)를 참조하십시오.
통계가 업데이트를 위해 큐에서 대기 중인 상태인지 또는 업데이트가 진행 중인지를 보려면 sys.dm_exec_background_job_queue 동적 관리 뷰를 사용합니다. 통계의 경우 object_id1 열에 테이블 또는 뷰 ID가 표시되고 object_id2 열에 통계 ID가 표시됩니다. sys.dm_exec_background_job_queue_stats 동적 관리 뷰를 사용하여 실행 대기 중인 작업 요청 수, 실패한 요청 수 및 이전에 제출된 요청의 평균 실행 시간 등의 모든 백그라운드 작업 큐의 집계 통계를 볼 수 있습니다.
자동 통계 기능 해제
특정 열이나 인덱스에 대한 자동 통계 생성 기능은 다음과 같은 방법으로 해제할 수 있습니다.
- sp_autostats 시스템 저장 프로시저 사용
- CREATE INDEX 문의 STATISTICS_NORECOMPUTE 절 사용
- UPDATE STATISTICS 문의 NORECOMPUTE 절 사용
- CREATE STATISTICS 문의 NORECOMPUTE 절 사용
- ALTER DATABASE 문을 사용하여 AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 OFF로 설정합니다. 자세한 내용은 데이터베이스 옵션 설정을 참조하십시오.
데이터베이스 엔진에서 통계를 자동으로 유지 관리하지 않으면 수동으로 통계 정보를 업데이트해야 합니다.
[!참고] NORECOMPUTE 절을 지정하지 않으면 UPDATE STATISTICS 문은 자동 통계 업데이트를 다시 설정합니다.
수동으로 통계 생성 및 업데이트
sp_createstats 시스템 저장 프로시저를 사용하면 한 문으로 현재 데이터베이스의 모든 사용자 테이블에 있는 적절한 모든 열에 대해 통계를 생성할 수 있습니다. 특정 테이블이나 뷰 열에 대한 통계는 CREATE STATISTICS 문을 사용하여 생성하고 UPDATE STATISTICS 문을 사용하여 업데이트할 수 있습니다. 인덱스와 별개로 테이블이나 뷰에 대해 생성할 수 있는 최대 통계 수는 2,000개입니다. 인덱스 키로 적합한 열이나 열 조합은 일부 예외적인 경우를 제외하고는 모두 통계에 적합합니다.
- xml을 제외한 큰 개체 유형 열을 지정할 수 있습니다. varchar(max), nvarchar(max), varbinary(max), image, text 및 ntext 유형을 지정할 수 있습니다.
- 결합된 열 값의 최대 허용 크기는 인덱스 키 값에 부과된 900바이트 제한을 초과할 수 있습니다.
열에 대해 생성된 통계를 더 이상 유지 관리하지 않으려면 이 통계를 삭제할 수 있습니다.
수동으로 통계를 생성하면 여러 열 조밀도를 포함하는 통계를 생성할 수 있습니다. 열 조밀도는 열 조합에 대한 평균 중복 값 수입니다. 예를 들어 WHERE a = 7 and b = 9 절을 가진 쿼리가 있습니다.
두 열(a, b)에 대해 수동으로 통계를 만들면 열 a와 b 열을 조합한 고유 값의 평균 개수가 통계에 포함되기 때문에 데이터베이스 엔진이 쿼리를 보다 잘 평가할 수 있습니다.
열에서 통계를 만들려면
CREATE STATISTICS(Transact-SQL)
모든 사용자 테이블에 있는 모든 적절한 열에서 통계를 만들려면
수동으로 통계를 업데이트하려면
UPDATE STATISTICS(Transact-SQL)
테이블의 통계를 보려면
DBCC SHOW_STATISTICS(Transact-SQL)
열의 통계를 삭제하려면
데이터베이스를 SQL Server 2005로 업데이트 후 통계 작업
데이터베이스를 SQL Server의 이전 버전에서 SQL Server 2005로 업그레이드하면 이전 버전의 모든 통계는 오래된 통계로 간주됩니다. 따라서 이러한 통계를 처음 사용할 때 AUTO_UPDATE_STATISTICS 데이터베이스 옵션으로 업데이트할 수 있는 통계는 기본 샘플링 비율을 사용하여 업데이트됩니다. 이 기능에는 중요한 이점이 있으며 일반적으로 사용자는 별도의 작업을 수행할 필요가 없습니다. 하지만 이 경우 SQL Server 이전 버전에서 FULLSCAN 또는 다른 높은 샘플링 비율을 사용하여 수동으로 통계를 계산하거나 샘플링되는 테이블이 8MB보다 크고 데이터 분포가 균일하지 않을 때는 드물게 통계 결과가 정확하지 않을 수 있습니다. 사실 AUTO_UPDATE_STATISTICS가 실행될 때는 8MB보다 큰 테이블의 FULLSCAN 통계에 대한 샘플링 비율이 언제든지 감소할 수 있습니다. SQL Server의 새 버전으로 업그레이드하면 곧바로 초기 통계가 업데이트될 수 있습니다.
통계를 SQL Server 2005 형식으로 업그레이드할 때의 이점은 대개 특정 샘플링 비율에 대한 SQL Server 2005 통계의 품질이 SQL Server 2000 및 이전 버전보다 더 뛰어나다는 점입니다. 또한 앞에서 설명한 것과 같이 SQL Server 2005에서는 문자 열에 대한 특수한 문자열 요약 통계를 생성합니다. SQL Server 2005의 통계에 대한 자세한 내용은 Microsoft 웹 사이트를 참조하십시오.
최선의 구현 방법 고려 사항
대부분의 경우 데이터베이스를 업그레이드한 후 사용자는 통계와 관련하여 특별한 작업을 수행할 필요가 없습니다. 그러나 성능 요구 사항이 높은 대용량 데이터베이스의 경우에는 업그레이드한 후 RESAMPLE 옵션을 사용하여 sp_updatestats(Transact-SQL)를 실행하는 것이 바람직합니다. 그러면 이전 샘플링 비율이 유지되고 모든 통계가 최신 형식으로 업데이트됩니다. 인덱스를 만드는 도중 생성되는 통계는 FULLSCAN 샘플링 비율을 사용하여 생성됩니다. 이러한 FULLSCAN 통계는 AUTO_UPDATE_STATISTICS로 인해 업데이트될 때 기본 샘플링 비율을 사용합니다. sp_updatestats를 실행하여 모든 통계를 업데이트하지 않을 때는 데이터베이스 업그레이드 후 UPDATE STATISTICS를 사용하여 FULLSCAN 샘플링 비율로 인덱스 통계 및 기타 FULLSCAN 통계를 선택적으로 업데이트합니다.
참고 항목
개념
관련 자료
CREATE INDEX(Transact-SQL)
쿼리 튜닝
sp_autostats(Transact-SQL)
도움말 및 정보
변경 내역
| 릴리스 | 내역 |
|---|---|
2006년 12월 12일 |
|
2005년 12월 5일 |
|