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

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

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

 

  • 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
2050 In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고 jevida(강성욱) 2017.01.11 1273
2049 Spool 연산자와 추적 플래그 8690 jevida(강성욱) 2017.01.11 1816
2048 .NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작 jevida(강성욱) 2017.01.11 1594
2047 함수 통계 정보 확인 (sys.dm_exec_function_stats) jevida(강성욱) 2017.01.11 1561
2046 NULL 데이터가 포함된 데이터 사용 시 주의점 jevida(강성욱) 2017.01.11 2808
» 통계정보와 실제 데이터 분포 확인하기 jevida(강성욱) 2017.01.11 1664
2044 SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류 jevida(강성욱) 2017.01.11 1456
2043 SSIS 실행 로그 남기기 jevida(강성욱) 2017.01.11 2643
2042 확장이벤트를 사용한 실행 계획 캡처 jevida(강성욱) 2017.01.11 1208
2041 테이블 외래키 트리 확인 및 데이터 삭제하기 jevida(강성욱) 2017.01.11 1738
2040 기본 추적을 사용한 SQL Server 스키마 변경사항 캡처 jevida(강성욱) 2017.01.11 1551
2039 SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기 jevida(강성욱) 2017.01.11 1648
2038 SQL Server 임시 테이블 특성 jevida(강성욱) 2017.01.11 7090
2037 트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거) jevida(강성욱) 2017.01.11 2128
2036 컬럼스토어 인덱스 성능 (Columnsotre Index Performance) jevida(강성욱) 2017.01.11 3150
2035 In-Memory 최적화 파일 경로 변경 jevida(강성욱) 2017.01.11 1492
2034 SA 계정 이름 변경 및 비활성화 jevida(강성욱) 2017.01.11 2264
2033 SQL Server 데이터베이스 속성을 확인할 때 사용되는 master.dbo.spt_values 복구 jevida(강성욱) 2017.01.11 1521
2032 XML로 생성된 정보를 테이블로 저장하기(sp_xml_preparedocument) jevida(강성욱) 2017.01.11 2165
2031 SQL Server 2016 Temporal Table – 데이터 변경 내용 추적 jevida(강성욱) 2017.01.11 1908





XE Login