SQL Server Statistics Update row sampling rate and histogram step

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