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

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

 

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

 

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

 

• 통계 : http://sqlmvp.kr/140165557766

 

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

 

이번 실습을 통해서 현재 통계 정보를 저장하고 실재 데이터 분포를 비교하여 통계 정보가 현재의 분포를 잘 반영하고 있는지 확인해본다. 이번 실습에서는 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

 

2015-11-17 / 강성욱 / http://sqlmvp.kr

 

MS SQL, SQL Server, DB 튜닝, DB 통계, Statistics, DBCC SHOW_STATISTICS, 데이터 샘플링, 옵티마이저, SQL 최적화, SQL 튜닝