MSSQL 테이블 컬럼 통계 생성 및 확인

 

개요 : mssql의 인덱스 설계 요청으로 테이블의 컬럼 통계를 확인 하고자 한다.

 

---

1. 테이블의 컬럼 확인

use AdventureWorks2022
SELECT a.name table_name
      ,b.name column_name
	  ,b.column_id, b.max_length, iif(b.is_nullable = 0, 'NO', 'YES') NULLABLE	  
FROM sys.tables a
INNER JOIN sys.columns b 
  ON a.object_id = b.object_id
WHERE a.name = 'Person'

 

 

 

---

2. SHOW_STATISTICS로 컬럼 통계 확인 : 자동으로 생성된 통계가 없으면 오류 메시지 : 2767 반환

DBCC SHOW_STATISTICS('[Person].[Person]', 'EmailPromotion')

---

통계 정보를 표시할 인덱스, 통계 또는 열의 이름입니다. 대상은 대괄호, 작은 따옴표, 큰 따옴표 또는 따옴표 없음으로 묶입니다.

• target이 테이블 또는 인덱싱된 뷰의 기존 인덱스 또는 통계 이름인 경우 이 대상에 대한 통계 정보가 반환됩니다.
• target이 기존 열의 이름이고 이 열에 자동으로 생성된 통계 개체가 있는 경우 자동 생성된 통계에 대한 정보가 반환되며,

열 대상에 대해 자동으로 생성된 통계가 없으면 오류 메시지 2767이 반환됩니다.

Azure Synapse Analytics 및 PDW(Analytics Platform System)에서 대상 은 열 이름이 될 수 없습니다.

Microsoft Fabric 의 Warehouse에서 대상 은 단일 열 히스토그램 통계의 이름 또는 열일 수 있습니다. 대상에 열 이름을 사용하는 경우 이 명령은 자동으로 생성된 히스토그램 통계에 대한 배포 정보만 반환합니다. 수동으로 만든 히스토그램 통계에 대한 정보를 보려면 통계 이름을 대상으로 지정합니다.

https://learn.microsoft.com/ko-kr/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-ver16

 

 

---

3. 일부 컬럼 : EmailPromotion  통계 생성 

USE AdventureWorks2022;
GO
-- Create new statistic object called ContactMail1
-- on the BusinessEntityID and EmailPromotion columns in the Person.Person table.

CREATE STATISTICS ContactMail1
    ON Person.Person (EmailPromotion);
GO

CREATE STATISTICS PersonType_Title
ON Person.Person(PersonType, Title)
WITH SAMPLE 5 PERCENT
GO

---

4. 통계 확인

---

(1) 개체 탐색기에서 확인

1. 개체 탐색기에서 더하기 기호를 클릭하여 새 통계를 만들 데이터베이스를 확장합니다.
2. 더하기 기호를 클릭하여 테이블 폴더를 확장합니다.
3. 더하기 기호를 클릭하여 통계 속성을 볼 테이블을 확장합니다.
4. 더하기 기호를 클릭하여 통계 폴더를 확장합니다.
5. 속성을 볼 통계 개체를 마우스 오른쪽 단추로 클릭하고 속성을 선택합니다.
6. 통계 속성 - statistics_name 대화 상자의 페이지 선택 창에서 자세히를 선택합니다.

---

(2) SQL로 확인

  : EmailPromotion으로 조회 시 에러 메시지 : 2767 발생

   'ContactMail1' 로 조회

DBCC SHOW_STATISTICS ('Person.Person', 'ContactMail1');   
GO

DBCC SHOW_STATISTICS ('Person.Person', PersonType_Title) WITH HISTOGRAM
GO

 

RANGE_HI_KEY 히스토그램 단계의 상한 열 값입니다. 열 값은 키 값이라고도 합니다. RANGE_ROWS 히스토그램 단계 내에 열 값이 포함되는 행의 추정 개수입니다(상한 제외). EQ_ROWS 히스토그램 단계의 상한과 열 값이 같은 행의 추정 개수입니다. DISTINCT_RANGE_ROWS 히스토그램 단계 내에서 상한을 제외한 고유한 열 값이 있는 행의 추정 개수입니다. AVG_RANGE_ROWS 히스토그램 단계 내에서 중복된 열 값이 있는 행의 평균 개수입니다(상한값 제외, DISTINCT_RANGE_ROWS > 0인 경우 RANGE_ROWS / DISTINCT_RANGE_ROWS).

 

---

5. 실제 데이터와 비교

select EmailPromotion, count(*)
from person.person
group by EmailPromotion

 

 

---

6. 통계정보 삭제

use AdventureWorks2022

drop statistics Person.Person.ContactMail1;

 

 

---

7. SQL Server가 데이터베이스에 대해 누락된 모든 단일 열 통계를 생성한다.

/*이 명령을 실행하면 SQL Server가 데이터베이스에 대해 누락된 모든 단일 열 통계를 생성합니다.*/
EXEC sp_createstats @indexonly = 'NO', @fullscan = 'FULLSCAN', @norecompute ='NO'

 

모든 열에 대한 통계가 생성됨

 

DBCC SHOW_STATISTICS (table_name, statistics_name) WITH HISTOGRAM

DBCC SHOW_STATISTICS ('Person.Person', PersonType) WITH HISTOGRAM

DBCC SHOW_STATISTICS ('Person.Person', PersonType)

 

 

통게정보 삭제

drop STATISTICS Person.Person.middlename;
drop STATISTICS Person.Person.FirstName;
drop STATISTICS Person.Person.ModifiedDate;
drop STATISTICS Person.Person.NameStyle;
drop STATISTICS Person.Person.Suffix;
drop STATISTICS Person.Person.Title;
drop STATISTICS Person.Person.PersonType;
GO

 

---

8. AUTO_CREATE_STATISTICS

 

데이터베이스 수준에서 AUTO_CREATE_STATISTICS 옵션을 설정하면 쿼리 최적화 프로그램이 술어에 사용된 개별 열에 대한 통계를 만듭니다.이러한 통계는 쿼리 계획을 생성하는 데 필요합니다 .기존 통계 개체에 히스토그램이 없는 열에 대해 만들어집니다.

자동 생성된 통계의 이름에는 열 이름과 16진수 형식의 개체 ID가 포함됩니다: _WA_Sys_<column_name>_<XXXX>.이러한 통계는 쿼리 최적화 프로그램에서 최적의 쿼리 실행 계획을 결정하는 데 사용됩니다.

 

_WA_Sys_<column_name>_<XXXX> 통계 검색

SELECT stat.name AS '통계', 
OBJECT_NAME(stat.object_id) AS '개체', 
COL_NAME(scol.object_id, scol.column_id) AS '열' 
FROM sys.stats AS stat (NOLOCK) 
INNER JOIN sys.stats_columns AS scol (NOLOCK) 
ON stat.stats_id = scol.stats_id AND stat.object_id = scol.object_id 
INNER JOIN sys.tables AS tab (NOLOCK) on tab.object_id = stat.object_id 
WHERE stat.name like '_WA%' 
ORDER BY stat.name

쿼리 최적화 프로그램에서 자동으로 추가된 모든 통계에 대한 통계 이름, 테이블 이름 및 열 이름을 반환합니다. 이 정보는 이러한 열에 인덱스를 추가해야 할지 여부를 결정하는 데 유용할 수 있습니다.

/* index와 같이 컬럼 히스토그램 조회 가능 */
DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader','_WA_Sys_00000011_5F7E2DAC')
GO

/* 삭제도 가능 */
drop STATISTICS Sales.SalesOrderHeader._WA_Sys_00000011_5F7E2DAC;
GO

 

SQL Server에서 _WA_Sys... 통계 제거

_WA_Sys 통계는 통계가 누락된 경우 생성됩니다. 이러한 통계를 삭제할 수 있지만 데이터베이스에 이 통계 정보를 보관하는 것이 좋습니다. 삭제하면 해당 열과 관련된 다음 쿼리 실행 중에 자동으로 생성됩니다. 이 추가 단계는 쿼리 성능에 부정적인 영향을 미칩니다.

_WA_Sys 통계를 더 적절한 이름의 통계 객체로 변환하는 것을 권장합니다. 자동 생성된 통계를 삭제하고 sp_createstats를 실행하여 더 사용자 친화적인 이름으로 통계를 생성하면 됩니다.

/*이 명령을 실행하면 SQL Server가 데이터베이스에 대해 누락된 모든 단일 열 통계를 생성합니다.*/
EXEC sp_createstats @indexonly = 'NO', @fullscan = 'FULLSCAN', @norecompute ='NO'

 

 

---

8. 데이터 베이스 내의 모든 통계 업데이트

use AdventureWorks2022
EXEC SP_UPDATESTATS 
GO

 

 

 

---

10. 테이블의 모든 통계 수집 정보 이름 확인

SELECT name AS statistics_name  
    ,stats_id  
    ,auto_created  
    ,user_created  
    ,no_recompute  
    ,has_filter  
    ,filter_definition  
-- using the sys.stats catalog view  
FROM sys.stats  
-- for the Sales.SpecialOffer table  
WHERE object_id = OBJECT_ID('Sales.SalesOrderDetail');  
GO

 

 

LineTotal 통계 조회

DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', LineTotal) WITH HISTOGRAM

 

---

11. 테이블의 모든 통계 조회

- 조건 넣어 사용.

SELECT ss.name, ss.stats_id, shr.steps, shr.rows, shr.rows_sampled, 
    shr.modification_counter, shr.last_updated, sh.range_high_key, sh.range_rows, sh.equal_rows
FROM sys.stats ss
INNER JOIN sys.stats_columns sc 
    ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac 
    ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
CROSS APPLY sys.dm_db_stats_histogram(ss.object_id, ss.stats_id) sh
WHERE ss.[object_id] = OBJECT_ID('Sales.SalesOrderDetail') 
  AND ac.name = 'OrderQty'
  AND sh.range_high_key <= 30
   -- AND sh.range_high_key = CAST('Canada' AS CHAR(8))
--ORDER BY NAME, STATS_ID, LAST_UPDATED, range_high_key

 

 

 

 

https://learn.microsoft.com/ko-kr/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-ver16