통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) - 통계 옵션을 이용한 실행계획 변경 하기

통계 업데이트 옵션(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;

 

 

동일한 쿼리임에도 불구하고 통계 정보를 조작하여 실행계획이 변경 된 것을 확인 할 수 있다.

 

통계 옵션을 이용하여 통계를 조작, 실행 계획을 변경 하였다. 기본적으로는 옵티마이저의 판단에 따르는 것을 권장 한다. 수동으로 통계를 조작할 경우 다른 쿼리에서 문제가 발생 할 수 있기 때문이다. 특정 상황에서 더 좋은 최적화된 플랜을 알고 있을 때 통계를 조작하여 쿼리의 성능을 향상 시킬 수 있다.

 

[참고자료]

• http://www.mssqltips.com/sqlservertip/2870/using-with-rowcount-and-pagecount-option-of-sql-server-update-statistics/
• http://msdn.microsoft.com/ko-kr/library/ms187348.aspx

 

 

 

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