통계와 동기, 비동기 처리 특징

1.   SQL Server의 통계

1)   Optimizer와 통계의 사용

(1)    SQL Server Optimizer는 비용 기반 Optimizer : 가장 낮은 예상 처리 비용으로 실행할 수 있는 쿼리 계획을 선택

(2)    쿼리 실행 계획 비용은 다음과 같은 2가지 요소로 결정

I.       쿼리 계획의 각 수준에서 처리되는 총 행 수(계획의 카디널리티라고 함)

II.      쿼리에 사용된 연산자가 지정하는 알고리즘의 비용

(3)    카디널리티가 연산자 알고리즘 비용 모델의 매개변수로 사용된다.

(4)    즉, 카디널리티가를 향상 시키면 예상 비용이 줄어들게 된다.

(5)    카디널리티는 인덱스나 통계를 만들 때 생성되는 히스토그램에서 주로 파생된다.

(6)    즉, 통계를 생성 또는 갱신할 시 카디널리티가 변경되어 실행 계획이 변경된다.

2)   통계

(1)    SQL Server SQL은 [T-SQL -> 구문 분석 -> 개채 이름 확인 -> 사용 권한 확인 -> 최적화 -> 컴파일 및 실행 계획 등록 -> 실행] 순으로 작동함

(2)    그 중 최적화 부분에서 통계를 사용해서 쿼리 성능을 향상 시키는 쿼리 계획을 생성

(3)    SQL Server에서 통계란 테이블이나 indexing된 view에서 하나 이상의 열에 있는 값의 분포에 대한 통계 정보를 포함하는 BLOB 개체

(4)    예를 들면 Optimizer가 카디널리티 추정을 사용하여 집약적 Index Scan 연산자 대신 index Seek 연산자를 성택하게 되는 것

(5)    통계가 변경되는 것은 다음과 같이 확인해 볼 수 있다.

I.       통계 정보를 확인하는 쿼리는 다음과 같다.

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target ) 

 

II.      업데이트 확인 테스트

DBCC SHOW_STATISTICS([PERSON.ADDRESS], PK_ADDRESS_ADDRESSID)  

                                  i.         Updated : 최근 통계가 업데이트된 날짜.

                                 ii.         Rows : 전체 행 수

                                iii.         Rows Sampled : 샘플링된 행 수 (행 수가 많을 경우 일부를 샘플링하여 사용)

                                iv.         Steps : 히스토그램의 단계 수.고유 인덱스가 아닐 경우 단계수가 많아지며 최대 200단계 이다.

                                 v.         Average Key length : 컬럼 값의 평균 길이. Int형이므로 4가 표시 된다.

                                vi.         All density : 밀도. 밀도는 Steps의 값과 무관하며 일반적으로 (1/ 인덱스 키들의 수) 이다.

                               vii.         RANGE_HI_KEY : 각 단계(step)에서샘플링된 최대 값.

                              viii.         RANGE_ROWS : 각 단계에서RANGE_HI_KEY갑을 제외한 값들의 개수.

                                ix.         EQ_ROWS : 최대값의 개수.

                                 x.         DISTINCT_RANGE_ROWS : RANGE_ROWS 컬럼 값들중 고유한 값의 개수.

III.    수동으로 통계 업데이트

UPDATE STATISTICS PERSON.ADDRESS

 

IV.    정보의 갱신 확인

 

3)   동기 통계 갱신과 비동기 통계 갱신

(1)    데이터베이스 생성 시 자동 통계 생성과 자동 통계 갱신이 활성화되어 있는 것을 확인할 수 있다.

I.       SSMS에서 확인

 

II.      DMV로 확인

SELECT  [name] As DBNAme

    ,   database_id

    ,   is_auto_create_stats_on

    ,   is_auto_create_stats_incremental_on

    ,   is_auto_update_stats_on

    ,   is_auto_update_stats_async_on

FROM    sys.databases

WHERE   [name]='AdventureWorks2019'

 

                                  i.         0 : 비활성화

                                 ii.         1 : 활성화

(2)    동기 통계 업데이트

I.       동기 통계 업데이트는 항상 최신의 통계로 컴파일 하고 실행한다.

II.      대량의 업데이트를 수행하는 경우와 같이 데이터 분포를 크게 변경하는 작업을 수행할 때 이롭다.

(3)    비동기 통계 업데이트

I.       통계 갱신을 확인하지 않으므로 빠른 실행 계획 생성이 가능

II.      유사한 쿼리를 사용해서 캐시된 실행 계획을 자주 사용하는 경우 유리

III.    통계 업데이트 wait 중 발생되는 time out을 방지할 수 있다.

IV.    OLTP 중 유사한 쿼리가 반복적으로 일어나는 상황에서 설정에서 유리할 수 있다.

2.   동기, 비동기 통계 설정 변경

(1)    SSMS를 이용한 변경

 

(2)    쿼리를 통한 변경

-- Enabled

ALTER DATABASE [AdventureWorks2019] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT

 

--Disabled

ALTER DATABASE [AdventureWorks2019] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT

 

 

3.   출처

1)      https://sqlmvp.tistory.com/436

2)      https://docs.microsoft.com/ko-kr/sql/relational-databases/statistics/statistics?view=sql-server-ver15

3)      https://docs.microsoft.com/ko-kr/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver15

4)      https://m.blog.naver.com/PostView.nhn?blogId=ljc8808&logNo=220410557767&proxyReferer=https:%2F%2Fwww.google.com%2F

5)      https://police84.tistory.com/79

6)      https://docs.microsoft.com/ko-kr/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-ver15