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

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

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

No. Subject Author Date Views
2133 SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링 jevida(강성욱) 2019.03.25 455
2132 SQL Server 2017 소규모 시스템에서 향상된 리소스 사용 jevida(강성욱) 2019.03.25 262
2131 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 408
2130 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 395
2129 SQL Server 2016 향상된 복제 기능 – 배포 데이터베이스 클린업 향상 jevida(강성욱) 2019.03.25 294
2128 SQL Server 2017향상된 복제 기능 – 배포 데이터베이스의 AG 지원 jevida(강성욱) 2019.03.25 444
2127 SQL Server 2017 향상된 복제 기능 - 복제에이전트 프로필 매개변수의 동적 새로 고침 jevida(강성욱) 2019.03.25 493
2126 SQL Server update on Docker jevida(강성욱) 2019.03.25 383
2125 SQL Server 2017에서 향상된 UDF 실행 계획 jevida(강성욱) 2019.03.25 351
2124 ETL data error with MariaDB ODBC 3.0 (from Aurora to MS SQL) jevida(강성욱) 2019.03.25 507
2123 Run the SQL Server 2017 with Docker jevida(강성욱) 2019.03.25 432
2122 SQL Server Configuration Manager 실행 오류 (WMI 공급자 연결 오류) jevida(강성욱) 2019.03.25 471
2121 BULK INSERT 동시에 여러개 실행 향지 2019.01.30 856
2120 대용량 데이터 조인에 대한 고민 ( Hash Join VS Nested Loop Join ) 향지 2018.04.04 4287
2119 How to check if Azure SQL is using In-Memory jevida(강성욱) 2018.03.31 3234
2118 MSSQL-CLI를 활용한 크로스플랫폼에서 SQL Server 관리하기 jevida(강성욱) 2018.03.31 3548
2117 SSMS – Search for execution plan jevida(강성욱) 2018.03.31 3730
2116 SQL Server Parallelism and Wait change (CXAPCKET, CXCONSUMER) jevida(강성욱) 2018.03.31 3943
» SQL Server Statistics Update row sampling rate and histogram step jevida(강성욱) 2018.03.31 3641
2114 SSMS – AlwaysOn Group Latency report jevida(강성욱) 2018.03.31 3381





XE Login