SQL Server Statistics Update row sampling rate and histogram step

-          통계 업데이트  샘플링 비율과 히스토그램 단계

 

·         Version : SQL Server

 

SQL Server에서 통계(statistics)정보는 데이터를 조회할때 검색 조건에 따라 옵티마이저가 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 결정하는 중요한 참고 정보이다통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질   컬럼값의 히스토그램  관련정보를 이용해서 만들어 진다.

·         SQL Server Statistics : http://sqlmvp.kr/140165557766

 

통계에 대한 샘플링 비율이 높을 수록 통계 정보의 신뢰도는 높아진다그렇다면 통계에 대한  샘플링 비율을 Full Scan(전체  샘플링)하였을때오히려 히스토그램의 단계가 줄어든 이유는 무엇일까?

히스토그램의 단계 수는 고유  수보다 적을  있다경계 지점이 200 미만인 열에도 고유 값의 개수보다 적을  있다고유 값만으로 구성된 열인 경우 통합된 히스토그램은 최소 3단계를 포함하게 된다따라서 완벽한 분포(빈도 = 1) 경우 데이터 분포를 정확하게 묘사하는데 필요한 단계가 많기 때문에 최소 3단계를 포함하게 된다.

 

아래 실습을 통해서 히스토그램에 대한 분포 변화를 살펴보자. IDENTITY  GUID 사용하여1백만건을 데이터를 생성한다.

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] =OBJECT_ID(N'[CustomersTableGuid]') AND [type] IN (N'U'))

CREATE TABLE CustomersTableGuid

(

    ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

)

GO

 

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] =OBJECT_ID(N'[CustomersTableIdent]') AND [type] IN (N'U'))

CREATE TABLE CustomersTableIdent

(

    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

)

GO

 

SET NOCOUNT ON;

DECLARE @i INT = 0

WHILE (@i <= 1000000)

BEGIN

    INSERT INTO CustomersTableGuid (FirstName, LastName)

    VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))

 

    INSERT INTO CustomersTableIdent (FirstName, LastName)

    VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))

   

    SET @i +=1

END

 

 

FULLSCAN으로 전체 행을 샘플링하여 통계를 업데이트 한다.

UPDATE STATISTICS CustomersTableGuid WITH FULLSCAN

GO

UPDATE STATISTICS CustomersTableIdent WITH FULLSCAN

GO

 

샘플된 행과 단계를 살펴보면  통계에 대해 3 또는 4단계 히스토그램이 있는 것을 확인할  있다.

SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], sp.stats_id, name,

    last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter  

FROM sys.stats AS stat  

CROSS APPLY sys.dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp 

WHERE stat.[object_id] = OBJECT_ID('CustomersTableGuid')

    OR stat.[object_id] = OBJECT_ID('CustomersTableIdent');

GO

 


 

 

ID열에 대한 히스토그램을 살펴 보면  통계에 대해 3단계 또는 4단계로 표시된다. SQL Server 히스토그램 품질을 유지하면서 가능한 적은 단계로 여러 히스토그램을 압축 한다예상대로 FULLSCAN 완벽한 분포이므로 3단계로 압축   있다.

SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], stat.name, sh.stats_id,

    sh.range_high_key, sh.range_rows, sh.equal_rows

FROM sys.stats AS stat

INNER JOIN sys.stats_columns AS sc

    ON stat.stats_id = sc.stats_id AND stat.[object_id] = sc.[object_id]

INNER JOIN sys.all_columns AS ac

    ON ac.column_id = sc.column_id AND ac.[object_id] = sc.[object_id]

CROSS APPLY sys.dm_db_stats_histogram(stat.[object_id], stat.stats_id) AS sh

WHERE (stat.[object_id] = OBJECT_ID('CustomersTableGuid')

    OR stat.[object_id] = OBJECT_ID('CustomersTableIdent'))

    AND ac.name = 'ID';

GO

 


 

통계 샘플을 90% 설정하여 업데이트 한다. (사용자 원한는  입력하여 테스트 가능)

UPDATE STATISTICS CustomersTableGuid WITH SAMPLE 90 PERCENT

GO

UPDATE STATISTICS CustomersTableIdent WITH SAMPLE 90 PERCENT

GO

 

통계 정보를 살펴본다. SQL Server 페이지 데이터를 가져온 다음 이를 전체 분포로 추정한다예상대로 표본 분포는 근사치로 빈도가 1 근접하지만 정확히 1 아니다.


 


 

 실습을 통해서 히스토그램 단계가 많은 것이  나은 예측을   있다는 판단을 모든 환경에 적용된다고    없다하지만  테스트 결과는 유니크한 데이터를 기준으로만 판한단 것이므로 무조건 단계가 낮은것이 좋은 것으로 오해하지 않기를 바란다일반적으로 샘플링 비율이 높을 수록 상대적으로 정확한 통계 정보로 인해 옵티마이저가  나은 판단을   있다또한 대용량의 테이블 경우샘플링 비율이 높을  성능적인 문제가 발생하기 때문에통계 업데이트에 대한 주기  비율은 각자의 비즈니스 환경에 따라서 DBA 최적의 컨디션을 유지할  있도록 조절해야 한다.

 

[참고자료]

·         https://blogs.msdn.microsoft.com/sql_server_team/perfect-statistics-histogram-in-just-few-steps/

·         https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics

 

 

2018-01-22 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

SQL Server, MS SQL, SQL Statistics, Histogram, SQL Optimizer, SQL Index, query, DB Tuning, SQL Density, update statistics, sys.stats





profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.