통계정보와 실제 데이터 분포 확인하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

통계(Statistics)는 검색 조건이 주어질 때 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 옵티마이저가 판단의 근거로 활용되는 자료이다. 통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질 때 이 컬럼 값의 히스토그램 및 관련정보를 이용하여 만들어진다.

 

 

통계의 경우 데이터가 변경됨에 따라 그 정보도 업데이트되는데 일정 비율이상 데이터가 변경될 업데이트 된다. 간혹 특정 시점에서 쿼리가 느린 경우가 발생하는데 통계정보와 실제 데이터의 분포가 다를 경우 옵티마이저가 잘못된 판단을 하여 최적화된 플랜을 사용하지 못한 경우이다.

 

이번 실습을 통해서 현재 통계 정보를 저장하고 실재 데이터 분포를 비교하여 통계 정보가 현재의 분포를 잘 반영하고 있는지 확인해본다. 이번 실습에서는 AdventureWorks2012 데이터베이스를 사용하였다.

 

통계를 확인하는 방법은 DBCC SHOW_STATISTICS 구문을 사용한다. 이 데이터를 테이블에 저장하여 실제 데이터와 비교한다. 우선 통계 정보를 저장할 수 있는 임시테이블을 생성한다.

create table #tblHistogram

(

vData sql_variant,

range_rows bigint,

eq_rows bigint,

distinct_range_rows bigint,

avg_range_rows bigint,

actual_eq_rows bigint DEFAULT(NULL),

actual_range_rows bigint DEFAULT(NULL)

)

go

 

통계 정보를 조회하는 프로시저를 생성한다.

create procedure #spHistogram

@strTable sysname,

@strIndex sysname

as

 

dbcc show_statistics(@strTable, @strIndex) with HISTOGRAM

go

 

통계 정보를 조회하여 위에서 생성한 임시테이블에 데이터를 저장한다. 실습테이블로 Person.person 테이블을 사용하였다.

truncate table #tblHistogram

go

 

insert

into #tblHistogram (vData, range_rows, eq_rows, distinct_range_rows, avg_range_rows)

exec #spHistogram '[Person].[Person]', 'PK_Person_BusinessEntityID'

go

 

select * from #tblHistogram

 

 

각 단계의 샘플링된 최대 값이 실제 데이터의 값과 비교할 수 있도록 정보를 업데이트 한다.

-- EQ_ROWS

update #tblHistogram set actual_eq_rows = (select count(*) from [Person].[Person] with(NOLOCK) where BusinessEntityID = h.vData)

from #tblHistogram h;

 

 

 

각 샘플링 구간에 대한 실제 행수가 현재 데이터의 실제 행수와 비교한다.

-- RANGE_ROWS

with BOUNDS (LowerBound, UpperBound)

as

(

select LAG(vData) over(order by vData) as [LowerBound], vData [UpperBound] from #tblHistogram

)

update

#tblHistogram

set actual_range_rows = ActualRangeRows

from (select LowerBound, UpperBound,

(select count(*) from [Person].[Person] with(NOLOCK) where BusinessEntityID > LowerBound and BusinessEntityID < UpperBound) as ActualRangeRows from BOUNDS

) as t

where vData = t.UpperBound

go

 

 

아래 스크립트는 통계 정보와 실제 데이터 분포가 다른 구간에 대해서 조회하여 최신 통계가 반영되지 않은 정보를 확인할 수 있다.

select

vData, eq_rows, actual_eq_rows, range_rows, actual_range_rows from #tblHistogram

where eq_rows <> actual_eq_rows or range_rows <> actual_range_rows

order by vData

go

 

최신 통계가 반영되어 있지 않은 경우 통계 업데이트를 진행하여 옵티마이저가 최적의 쿼리를 수행 할 수 있도록 해야 한다. 단 통계를 업데이트하기전에 발생할 수 있는 상황을 고려하여 기존 비즈니스에 영향을 주지 않도록 해야한다.

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/11/09/are-my-statistics-correct.aspx

 



강성욱 / 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 20078
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 12120
2054 확장이벤트 사용시 주의사항 jevida(강성욱) 2017.01.11 1686
2053 Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기 jevida(강성욱) 2017.01.11 3654
2052 Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화 jevida(강성욱) 2017.01.11 1653
2051 시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기 jevida(강성욱) 2017.01.11 2568
2050 In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고 jevida(강성욱) 2017.01.11 1323
2049 Spool 연산자와 추적 플래그 8690 jevida(강성욱) 2017.01.11 2005
2048 .NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작 jevida(강성욱) 2017.01.11 1653
2047 함수 통계 정보 확인 (sys.dm_exec_function_stats) jevida(강성욱) 2017.01.11 1621
2046 NULL 데이터가 포함된 데이터 사용 시 주의점 jevida(강성욱) 2017.01.11 2939
» 통계정보와 실제 데이터 분포 확인하기 jevida(강성욱) 2017.01.11 1721
2044 SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류 jevida(강성욱) 2017.01.11 1545
2043 SSIS 실행 로그 남기기 jevida(강성욱) 2017.01.11 2789
2042 확장이벤트를 사용한 실행 계획 캡처 jevida(강성욱) 2017.01.11 1282
2041 테이블 외래키 트리 확인 및 데이터 삭제하기 jevida(강성욱) 2017.01.11 2043
2040 기본 추적을 사용한 SQL Server 스키마 변경사항 캡처 jevida(강성욱) 2017.01.11 1657
2039 SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기 jevida(강성욱) 2017.01.11 1762
2038 SQL Server 임시 테이블 특성 jevida(강성욱) 2017.01.11 7269
2037 트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거) jevida(강성욱) 2017.01.11 2265
2036 컬럼스토어 인덱스 성능 (Columnsotre Index Performance) jevida(강성욱) 2017.01.11 3263
2035 In-Memory 최적화 파일 경로 변경 jevida(강성욱) 2017.01.11 1545





XE Login