소개
통계(Statistics) 개체는 특정 열의 어떤 값이 어떻게 분포되어 있는지 히스토그램을 포함한 여러가지 통계 정보를 저장하고 있는 개체입니다. 이는 Query Optimizer가 해당 열의 행 수 추정을 할 때 참조하는 중요 정보이며 다양한 방식으로 생성됩니다.
[그림-1] 참고 – CustKey 라는 열의 “가상”의 히스토그램
예를 들어, 인덱스를 생성하면 단일 열의 경우 해당 열에, 복합 열의 경우 첫 번째 열에 자동으로 생성됩니다, 반대로 통계가 없는 열을 Query Optimizer가 참조하는 경우 자동으로 생성됩니다(AUTO_DROP = ON 으로).
이 두 가지는 모두 자동으로 생성되는 것이니 삭제도 자동으로 될 수 있겠죠. 인덱스를 삭제하거나 해당 열을 삭제하게 되면 종속된 통계 개체도 자동으로 삭제됩니다.
참고 자료
수동 통계 개체 생성
또 한 가지 옵션은 수동 통계 개체 생성입니다. CREATE STATISTICS (또는 UPDATE STATISTICS) 문을 이용해서 원하는 특정 테이블에 하나 이상의 열을 지정하고 샘플링 비율 등의 원하는 추가 옵션을 이용해서 직접 통계 정보를 생성하고 관리할 수 있습니다. (자세한 구문은 위 MS 링크를 참조하세요)
이는 사용자가 실제로 직접 생성하는 경우(확률은 매우 낮은)도 있고 특정 솔루션에 의해서 생성될 수도 있습니다.
AUTO_DROP 옵션
- AUTO_DROP = { ON | OFF }
OFF(디폴트): 로 지정 시 해당 열의 종속 개체가 되며 열 삭제 시 충돌이 납니다, 통계 개체를 먼저 삭제하고 열을 삭제할 수 있습니다.
ON: 으로 지정 시 자동 통계 개체처럼 열 삭제 시 통계 개체도 자동 삭제됩니다.
SQL Server 2022 (또는 Azure SQL)의 새로운 AUTO_DROP 옵션을 이용하면 열 삭제 시 해당 통계 개체도 자동 삭제됩니다.
그러면 간단한 예제 코드로 테스트 하겠습니다.
[코드-1] 현재 DB의 dbo.MyProducts 테이블의 통계 개체 목록 확인, AUTO_DROP 속성 ON/OFF 포함
SELECT stats_id, [name], st.auto_created, st.user_created, auto_drop
FROM sys.stats AS st
WHERE st.object_id = OBJECT_ID('dbo.myProducts');
[코드-2] UnitsInStock 열에 수동으로 통계 개체 생성하며 AUTO_DROP = ON 설정
CREATE STATISTICS ST_myProducts_UnitsInStock ON myProducts(UnitsInStock) WITH AUTO_DROP = ON;
[그림-2] 코드-1을 이용해 통계 개체 목록 확인, AUTO_DROP 속성(1) 확인
[코드-3] 열 삭제
ALTER TABLE dbo.myProducts DROP COLUMN UnitsInStock
이전 동작 기준으로는 통계 개체로 인해 열 삭제가 오류가 발생하지만, AUTO_DROP = ON으로 설정된 경우 정상적으로 실행되며 코드-1로 확인하면 통계 개체도 삭제된 것을 볼 수 있다.
참고 및 권장 사항
- AUTO_DROP 옵션은 CREATE STATISTICS, UPDATE STATISTICS 문에서 지원
- 단 UPDATE STATISTICS의 경우 수동 생성 통계에만 적용
- ALTER COLUMN 시에도 해당 통계 개체는 삭제되는 것으로 보임
- RESTORE 등의 방법으로 Upgrade 시에는 sp_updatestats 등을 이용해서 전체 통계 업데이트를 권함 (MS 링크 문서 참조)
정리
Query Optimizer의 행 수 추정에 통계 개체가 중요하게 사용되므로 불필요하게 많은(혹은 존재하는) 통계 개체는 컴파일이나 테이블 스키마 변경 등에 방해 요소가 될 수도 있습니다. 그러한 이유로 이번 옵션이 새로 추가된 것이 아닌가 짐작됩니다(MS 문서에도 언급된 부분). 우리의 경우 수동으로 통계 개체를 만들어 사용하는 경우가 드물지만 혹시 유사한 작업을 한다면 이 옵션도 의미있게 활용 할 수 있겠습니다.
모두 건강하세요.
김정선 드림
