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
