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

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 14481
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 8885
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1517
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1119
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 994
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3250
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1654
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1406
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1129
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 987
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 887
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 951
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 1095
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1987
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1302
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1337
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2808
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1759
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 2045
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5900
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1408
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 4385





XE Login