통계 업데이트 옵션(ROWCOUNT and PAGECOUNT)
(통계 옵션을 이용한 실행계획 변경 하기)
- Version : SQL Server 2005, 2008, 2008R2, 2012
옵티마이저가 실행 계획을 생성할 때 통계를 기반으로 한다. 수동으로 통계를 조작하여 실행 계획을 변경 할 수 없을까? 통계 업데이트시 ROWCOUNT, PAGECOUNT 옵션을 사용하여 통계 정보를 변경하여 보자.
실습을 위하여 테스트 테이블을 생성 한다.
CREATE TABLE testtable ([col1] [int] NOT NULL primary key, [col2] [int] NULL, [col3] [int] NULL, [col4] [varchar](50) NULL);
CREATE NONCLUSTERED INDEX [IX_testtable] ON testtable ([col2] ASC);
DECLARE @val INT SELECT @val=1 WHILE @val <= 2000 BEGIN INSERT INTO testtable (col1, col2, col3, col4) VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR)) SELECT @val=@val+1 END |
샘플 코드를 조회하여 본다. 이때 실행 계획을 같이 볼 수 있도록 한다.
SELECT * FROM testtable WHERE col2 = 6; |
싱글로 인덱스 스캔을 한 것을 볼 수 있다. 현재 통계의 정보를 살펴 보자.
select a.name, a.id, d.name as IndexName, b.rows, c.in_row_data_page_count, c.in_row_used_page_count, c.in_row_reserved_page_count, c.used_page_count, c.reserved_page_count, c.row_count from sys.sysobjects as a inner join sys.indexes as d on a.id = d.object_id inner join sys.partitions as b on a.id = b.object_id inner join sys.dm_db_partition_stats as c on b.partition_id = c.partition_id and c.index_id = d.index_id where a.name = 'testtable' |
수동으로 통계 업데이트를 진행 한다. 이때 데이터베이스 속성에 자동 통계 업데이트를 사용하고있으면 사용하지 않음으로 설정 한다.
강제로 ROWCOUNT 및 PAGECOUNT 의 숫자를 조정하여 데이터가 많은 것처럼 통계 정보를 변경 하였다.
UPDATE STATISTICS testtable WITH ROWCOUNT = 50000000, PAGECOUNT = 500000 UPDATE STATISTICS testtable IX_testtable WITH ROWCOUNT = 50000000, PAGECOUNT = 500000
Go
select a.name, a.id, d.name as IndexName, b.rows, c.in_row_data_page_count, c.in_row_used_page_count, c.in_row_reserved_page_count, c.used_page_count, c.reserved_page_count, c.row_count from sys.sysobjects as a inner join sys.indexes as d on a.id = d.object_id inner join sys.partitions as b on a.id = b.object_id inner join sys.dm_db_partition_stats as c on b.partition_id = c.partition_id and c.index_id = d.index_id where a.name = 'testtable' |
통계 정보가 변경 된 것을 확인 할 수 있다.
다시 한번 테스트 쿼리를 실행 하여 데이터를 조회하여 보자.
SELECT * FROM testtable WHERE col2 = 6; |
동일한 쿼리임에도 불구하고 통계 정보를 조작하여 실행계획이 변경 된 것을 확인 할 수 있다.
통계 옵션을 이용하여 통계를 조작, 실행 계획을 변경 하였다. 기본적으로는 옵티마이저의 판단에 따르는 것을 권장 한다. 수동으로 통계를 조작할 경우 다른 쿼리에서 문제가 발생 할 수 있기 때문이다. 특정 상황에서 더 좋은 최적화된 플랜을 알고 있을 때 통계를 조작하여 쿼리의 성능을 향상 시킬 수 있다.
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
