통계

/*
통계는 SQL 서버가 인덱스 키에 대해서 관리하는 메타 데이터이며
인덱스가 없는 컬럼에 대해서도 선택적으로 관리할 수 있다.

- 카디널리티
데이터의 카디널리티(cardinality)는 데이터에 유일한(unique) 값들이 얼마나 많이 있는지를 나타내는 데이터이다.


- 밀도(density)
데이터 집합 내에서 값의 유일성(uniqueness)를 나타낸다. 인덱스의 밀도는 주어진 키 값에 해당하는 행의 개수를
테이블에 있는 행의 개수로 나누어서 계산한다.
UNIQUE 인덱스의 경우에는 1을 테이블의 전체 행의 수로 나눈 값이 된다.
밀도는 0부터 1까지이며, 밀도가 낮을수록 좋다.


- 선택성(selectivity)
특정 쿼리 기준에 의해 반환되는 행의 개수의 정도를 말한다.
사용자의 쿼리 기준과 인덱스의 키 값 간의 관계를 나타낸다.
선택성은 요청한 키의 개수를 액세스하는 행의 개수로 나누어 계산한다.
높은 선택성을 가지는 쿼리 기준(주로 where절에 기술)이 쿼리 옵티마이저에게 가장 유용하다.
왜냐하면 쿼리를 수행하기 위하여 I/O가 얼마나 필요한지를 확실하게 예측할 수 있도록 해 주기 때문이다.


- 성능 이슈
밀도가 높은 인덱스는 쿼리 옵티마이저가 무시할 가능성이 높다.
쿼리 옵티마이저에 있어서 가장 유용한 인덱스는 밀도 값이 0.10 이나 0.10 미만인 인덱스이다.

인덱스의 선택도는 높을수록 좋지만 오히려 역효과가 발생하는 지점이 있다.
인덱스를 보다 선택적으로 만들기 위하여 인덱스에 컬럼을 추가하면, 인덱스의 B-트리를 검색하는 오버헤드가 증가한다.
인덱스를 더 크게 만들면, 인덱스를 따라 가는 데 드는 비용이 증가한다.
어떤 지점에서는 B-트리를 따라 검색해 가는 오버헤드를 유할하는 것보다 데이터 자체를 그냥 스캔하는 것이 오히려
비용이 적게 든다.


- 저장
SQL 서버는 인덱스 키나 sysindexes의 statblob 컬럼에 있는 컬럼에 대하여 통계를 저장한다.
Statblob는 인덱스 키나 컬럼에 있는 값들의 샘플링을 포함하는 히스토그램(데이터 집합에서 각 고유 값의
발생 빈도를 측정하는 것)을 저장하는 image 데이터타입이다.
복합 인덱스의 경우에는, 첫 번째 컬럼만 표본으로 추출하지만, 밀도 값은 다른 컬럼을 위해 관리된다.
인덱스에 컬럼이 하나만 있으면, 통계는 하나의 히스토그램과 하나의 밀도값으로 이루어진다.
인덱스에 컬럼이 여러 개 있는 경우에는, 히스토그램은 하나만 관리되고, 밀도는 인덱스에 있는 컬럼들을
왼쪽에서 오른쪽으로 조합한 각각에 대하여 관리된다.
옵티마이저는 인덱스의 히스토그램과 밀도, 즉 인덱스의 통계를 사용하여 특정 쿼리를 수행하는데 있어서
그 인덱스가 얼마나 유용한지를 결정한다.
ex) 인덱스가 (col1, col2, col3) 컬럼에 대해 만들어져 있다면, 세 가지 조합 각각에 대하여 밀도값을 관리
(col1), (col1, col2), (col1, col2, col3)

히스토그램이 복합 인덱스의 첫 번째 컬럼을 위해 저장된다는 사실은 다중 컬럼 인덱스에서 가장 선택적인 컬럼을
맨 처음에 위치시켜야 하는 이유 중 하나이다.


- 컬럼 통계
인덱스에 만들어지는 통계 외에 SQL 서버는 인덱스가 없는 컬럼에 대해서도 통계를 만들 수 있다.
참고로, 데이터베이스의 AUTO_CREATE_STATISTICS 옵션을 활성화해 두면, 인덱스가 없는 컬럼이 쿼리될 때
SQL 서버가 통계를 자동으로 만들어준다. 주어진 값이 컬럼에서 발생할 수 있는 가능성을 확인할 수 있기 때문에,
쿼리 옵티마이저가 쿼리를 처리하기 위하여 어떤 결정을 내리는 것이 최적인가를 결정하는 데 있어서 가치 있는
정보를 제공한다.


- 통계 정보 확인
SQL 서버는 통계를 사용하여 테이블에 있는 키 값의 분포를 추적한다. 인덱스의 통계의 일부로 저장되는 히스토그램은,
인덱스의 첫 번째 키 컬럼에 대하여 200개 값들의 표본 추출을 포함한다.
히스토그램 외에 statblob도 역시 다음의 정보를 포함한다.
  -히스토그램과 밀도의 기반이 되는 행의 개수
  -인덱스 키의 평균 길이
  -마지막 통계 생성 일시
  -키 컬럼의 접두사(prefix) 조합에 대한 밀도 값
  
    각각의  200개 히스토그램 표본 값 사이의 키 값들의 범위를 "스탭(STEP)"이라고 한다.
    각 표본 값은 스탭의 끝을 나타내며, 각 스탭에는 세 개의 값이 저장된다.
    1. EQ_ROWS - 표본 값과 일치하는 키 값을 가지는 행의 개수
    2. RANGE_ROWS - 범위 내에 있는 다른 값들의 개수
    3. RANGE_DENSITY - 범위 자체에 대한 밀도 계산

    DBCC SHOW_STATISTICS는 EQ_ROWS와 RANGE_ROWS 값을 보여 주며,
    RANGE_DENSITY를 사용하여 스탭에 대한 DISTINCT_RANGE_ROWS와 AVG_RANGE_ROWS를 계산한다.
    1을 RANGE_DENSITY로 나누어서 DISTINCT_RANGE_ROWS(STEP의 범위 내에 있는 서로 다른 행의 총 개수)
    를 계산하며, RANGE_ROWS에 RANGE_DENSITY를 곱해서 AVG_RANGE_ROWS(서로 다른 키 값별 평균 행 개수)
    를 계산한다. (AVG_RANGE_ROWS = RANGE_ROWS / DISTINCT_RANGE_ROWS)


- 통계 업데이트
1. AUTO_UPDATE_STATISTICS 데이터베이스 옵션 사용
    ALTER DATABASE나 sp_dboption 을 사용하여 활성화할 수 있다.
    자동 통계 업데이트와 밀접하게 연관되는 것은 자동 통계 생성이다. 자동 통계 생성은 AUTO_CREATE_STATISTICS
    데이터베이스 옵션이 활성화되어 있는 상태에서 인덱스가 없는 컬럼에 대하여 원하는 조건에 맞는 데이터를 걸러내는 쿼리를
    수행할 때 일어난다.
2. UPDATE STATISTICS 명령어를 사용
    UPDATE STATISTICS는 자동 업데이트가 발생할 때, 표본 추출을 사용하여 자동 업데이트를 수행할 수도 있고, 아니면
    테이블 전체를 스캔하여 자동 업데이트를 수행할 수도 있다. 테이블 전체를 스캔하면 더 나은 통계가 만들어지지만
    시간이 더 오래 걸린다.
    UPDATE STATISTICS와 밀접하게 연관되는 것은 CREATE STATISTICS 명령어이다.
    CREATE STATISTICS는 컬럼 통계를 수동으로 만들어 준다.
    * SP_UPDATESTATS는 현재 데이터베이스에 있는 모든 사용자 정의 테이블들에 대하여 UPDATE STATISTICS를 수행한다.
    UPDATE STATISTICS와 달리 SP_UPDATESTATS는 통계를 만들기 위해 테이블 전체를 스캔할 수 없으며, 항상 표본
    추출을 사용한다. 전체 스캔 통계를 원하면 UPDATE STATISTICS를 사용해야 한다.
     * SP_CREATESTATS는 데이터베이스의 모든 자격이 있는 테이블, 모든 자격이 있는 컬럼에 대하여 컬럼 통계의 생성을
    자동화할 수 있다.
    자격이 있는 컬럼이란, 컬럼 인덱스 통계나 첫 번째 컬럼 인덱스 통계를 가지지 않으면서 데이터 타입이 text, ntext, image가
    아니고 계산되지 않은 컬럼을 말한다.
    자격이 있는 테이블이란, 시스템 테이블이 아닌 사용자 테이블을 말한다.
    * SP_AUTOSTATS를 사용하면 테이블 레벨과 인덱스 레벨에서 자동 통계 업데이트를 제어할 수 있다. 단순히
    AUTO_UPDATE_STATISTICS 데이터베이스 옵션에 의존하지 않고, 보다 주기적으로 자동 통계 생성을 활성/비활성할 수 있다.


 - SP_SHOWSTATDATE
 통계 업데이트 관련 정보확인. 통계 유형, 마지막으로 업데이트된 시각, 인덱스 통계와 컬럼 통계를 보여 준다.
 
*/

CREATE PROC sp_showstatdate @tabmask sysname = '%', @indmask sysname = '%'
AS
SELECT   
    LEFT(CAST(USER_NAME(uid) + '.' + o.name AS sysname), 30) AS TableName,
    LEFT(i.name, 30) AS IndexName,
    CASE WHEN INDEXPROPERTY(o.id, i.name, 'IsAutoStatistics') = 1 THEN 'AutoStatistics'
            WHEN INDEXPROPERTY(o.id, i.name, 'IsStatistics') = 1 THEN 'Statistics'
    ELSE 'Index'
    END AS Type,
    STATS_DATE(o.id, i.indid) AS StatsUpdated,
    rowcnt,
    rowmodctr,
    ISNULL(CAST(rowmodctr / CAST(NULLIF(rowcnt, 0) AS decimal(20, 2)) * 100 AS int), 0) AS PercentModifiedRows,
    CASE i.status & 0x1000000 WHEN 0 THEN 'No' ELSE 'Yes' END AS [NoRecompute?],
    i.status
FROM dbo.sysobjects o JOIN dbo.sysindexes i ON (o.id = i.id)
WHERE o.name LIKE @tabmask
    AND i.name LIKE @indmask
    AND OBJECTPROPERTY(o.id, 'IsUserTable') = 1
    AND i.indid BETWEEN 1 AND 254
ORDER BY TableName, IndexName
GO
USE pubs;
GO
EXEC sp_showstatdate;