데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

통계 업데이트 옵션(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
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3703
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1583
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1704
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1435
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1310
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1580
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1239
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3664
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1239
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1180
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1071
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1132
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1302
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1662
» 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1233
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1422
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1066
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 959
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3123





XE Login