SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인

 

·       Version : SQL Server 2019

 

SQL Server에서 통계정보는 옵티마이저가 실행 계획을 생성할 참고하는 중요한 지표이다. 통계 자동 업데이트가  true 설정된 경우, 데이터의 변경이 특정 임계치 이상되면 자동으로 통계 정보를 업데이트 한다.

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

 

이때 통계 정보를 업데이트하면서 블럭킹이 발생하는데 이전까지는 블럭킹이 발생한것에 대해서 확인할 방법이 없었다. SQL Server 2019 부터는 이러한 문제를 해결하기 위해 새로운 진단 데이터가 도입되었다. 통계 업데이트시 블럭킹을 발생하는 것을 재현하기 위해 아래와 같은 시나리오를 만들었다.

·       자동 통계 업데이트를 트리거하는  SELECT 쿼리를 실행한다.

·       동기 통계 업데이트가 실행을 시작하고 통계가 생성될때 까지 쿼리가 대기한다. (기본적으로 차단됨)

·       동기 통계 업데이트 조작이 완료 까지 쿼리 컴파일 실행이 재개되지 않는다.

시간 동안 쿼리는 동기화 통계 업데이트 작업이 완료될 까지 대기하고 있으며, 문제를 확인하기 어려웠다. 대용량 테이블또는 사용량이 많은 시스템등 통계 업데이트에 시간이 오래 걸리는 경우 원인을 쉽게 확인할 있는 방법이 없다.

 

 

SQL Server 2019에서는 동기화 통계 업데이트로 인해 쿼리가 차단되면  sys.dm_exec_requests에서‘command’컬럼에 (STATMAN) 표시된다. 그리고 통계 업데이트 작업이 완료되면 초기 명령이름으로 돌아간다.

 

또한 새로운 WAIT_ON_SYNC_STATISTICS_REFRESH 대기 유형은 동기 통계 업데이트에서 집계된 대기 시간(블럭) 측정한다. 대기시간 누적은 sys.dm_os_wait_stats 동적 관리뷰에서 확인할 있다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/13/diagnostic-data-for-synchronous-statistics-update-blocking/

 

 

2019-09-20/ Sungwook Kang / http://sungwookkang.com

 

 

SQL Server2019, SQL Statistics, WAIT_ON_SYNC_STATISTICS_REFRESH, sys.dm_os_wait_stats, sys.dm_exec_requests

SQL Server Parallelism and Wait change (CXAPCKET, CXCONSUMER)

-          병렬처리에 발생하는 대기 종류 변경

 

·         Version : SQL Server 2016 SP2 later, SQL Server 2017 CU3, Azure SQL

 

SQL Server에서 CPU 두개 이상 사용하는 컴퓨터에서는 운영체제의 다중 스레드로 쿼리나 인덱스 작업을 병렬로 수행하여 빠르고 효율적으로 요청 작업을 완료할 있다.  쿼리가 병렬로 실행될때 가장 이상적인 동작은 모든 작업이 생산자 스레드에 균등하게 분산되어 모두 같은 시간에 작업이 끝나서 결과를 소비자 스레드로 넘기는 것이다. 이때 모든 생성자가 완료되기를 기다리는동안 CXPACKET 대기(이때 발생하는 대기는 정상이다.) 발생한다. 하지만 잘못된 병렬처리가 발생(작업의 불균등 분배)하여 스레드가 완료 때까지 일부 기다리는 스레드가 발생하는데 이때에도 CXAPCKET 대기가 발생한다. 그래서 정상적인(성능에 문제가 없는) CXPACKET인지, 비정상(성능에 문제가 있는) CXPACKET 인지 DBA 구분할수 있어야 하는데 안타깝게도 SQL Server에서는 모두 CXPACKET 표현되어 쉽게 있는 방법이 없다. (물론 실행계획을 캡쳐하여 스레드별로 처리되는 리소스를 확인하면 가능하다.)

 

SQL Server 2016 SP2 이상, SQL Server 2017 CU3 이상, Azure SQL 데이터베이스에서는 CXPACKET 대기가 보고되는 방식을 변경하였는데, 스레드간의 작업이 고르지 않게 분산되어 대기가 발생하는 (불량 유형의 대기) CXPACKET 대기로 계속 보고하고 소비자 스레드가 모든 제작자 스레드를 기다리는 정상적인(좋은 유형의 대기) 대기 스레드는 CXCONSUMER 보고되어 사용자가 구분할 있도록 되었다.

 

기존의 SQL Server 2016 SP2 이하에서는 아래와 같이 CXPACKET 대기로만 표현되었다.


 

SQL Server2016 SP2 이후에서는 CXPACKET 함께 CXCONSUMER 구분할 있게 되었다.


 

 

이처럼 병렬처리시 대기에 대한 종류가 구분되어 사용자가 문제를 쉽게 파악할 있게 되었다. 하지만 주의해야할 점이 CXCONSUMMER 보고되었기 때문에 무조건 성능에 문제가 없다고 판단할 수도 없다. 따라서 기존의CXPACKET 모니터링은 지속되어야 하며  CXCONSUMER 함께 모니터링할 필요가 있다. 그리고 불필요한 병렬처리가 발생할 경우 쿼리를 수정하거나, 적절한 실행계획이 생성되기 위새 통계를 업데이트 하거나,  MAXDOP 옵션을 사용하여 SQL Server 최적화 있도록 전략을 수립해야한다.

 

[참고자료]

·         쿼리 처리 아키텍처 - 병렬 쿼리 처리 : http://sqlmvp.kr/140189219438

·         CPU 리소스 상태에 따른 병렬 처리 제한 : http://sqlmvp.kr/140193366014

·         병렬 처리 대기 확인 : http://sqlmvp.kr/140207680181

·         SQL Server MAXDOP 설정 : http://sqlmvp.kr/140176043445

·         https://www.mssqltips.com/sqlservertip/5207/changes-to-cxpacket-waits-in-sql-server/

·         https://blogs.msdn.microsoft.com/sql_server_team/making-parallelism-waits-actionable/

 

 

 

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

 

SQL Server, MS SQL, SQL Statistics, SQL Parallelism, CXPACKET, CXCONSUMER, worker thread, sql wait


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


+ Recent posts