MSSQL 통계 업데이트 & 인덱스 리빌딩

1. MSSQL 모든테이블 통계 업데이트

DECLARE @SQL VARCHAR(5000)
SELECT @SQL = ''

DECLARE TAB_CUR CURSOR FOR
      SELECT A = 'UPDATE STATISTICS ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE'
      
OPEN TAB_CUR
FETCH NEXT FROM TAB_CUR INTO @SQL
WHILE(@@FETCH_STATUS = 0)
BEGIN
   EXEC (@SQL)
   FETCH NEXT FROM TAB_CUR INTO @SQL
END      

CLOSE TAB_CUR
DEALLOCATE TAB_CUR

 

2. 인덱스 리빌드

1) 이유

조각화는 디스크상에 페이지들이 연속적으로 위치해 있지 않고 공간을 두고 떨어져 있는 현상.

인덱스를 처음 생성했을 때는 연속해서 페이지들이 위치해 있지만 데이터의 변경(INSERT, UPDATE, DELETE)이 일어나다 보면 나중에는 페이지가 비연속적으로 위치하게 되어 성능을 떨어뜨리게 되는 경우가 있다.

따라서 이러한 데이터 페이지 및 인덱스 페이지들은 다시 순차적인 데이터 저장 형태로 재 조정할 필요가 있다. 

 

2) 지정한 테이블이나 뷰의 데이터와 인덱스에 대한 조각화 정보를 표시

 

① DBCC SHOWCONTIG 명령으로 테이블과 인덱스의 조각화 정도 확인  

DBCC SHOWCONTIG
    [     ( { table_name | table_id | view_name | view_id }
             [ , INDEX_name | INDEX_id ]
        ) 
    ] 
     [ WITH { ALL_INDEXES
                 | FAST [ , ALL_INDEXES ]
                 | TABLERESULTS [ , { ALL_INDEXES } ]
                 [ , { FAST | ALL_LEVELS } ] 
            }
    ]

DBCC SHOWCONTIG (Table)
DBCC SHOWCONTIG (Table, INDEX)
DBCC SHOWCONTIG (Table) WITH FAST 
/* 인덱스의 고속 검색과 최소 정보 출력 수행 여부 지정.
고속 검색은 인덱스의 리프 또는 데이터 수준 페이지를 읽지 않는다. */
WITH FAST
/* 심각도가 0에서 10 사이인 모든 정보 메시지를 표시하지 않는다. */
WITH NO_INFOMSGS

∂ 예시

DBCC ShowContig('tbStuSet')

DBCC SHOWCONTIG이(가) 'tbStuSet' 테이블을 검색하는 중...
테이블: 'tbStuSet'(1010102639); 인덱스 ID: 1, 데이터베이스 ID: 5
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 453
- 검색한 익스텐트 ..............................: 61
- 익스텐트 스위치..............................: 158
- 익스텐트당 평균 페이지 수........................: 7.4
- 검색 밀도[최적:실제].......: 35.85% [57:159]
- 논리 검색 조각화 상태 ..................: 29.36%
- 익스텐트 검색 조각화 상태 ...................: 67.21%
- 페이지당 사용 가능한 평균 바이트 수.....................: 1038.9
- 평균 페이지 밀도(전체).....................: 87.16%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

Completion time: 2022-10-01T16:47:04.3474445+09:00

항목	설명
검색한 페이지	테이블이나 인덱스의 페이지 수
검색한 익스텐트	테이블이나 인덱스의 익스텐트 수
익스텐트 스위치	DBCC 문이 테이블이나 인덱스의 페이지를 이동한 동안 익스텐트 간에 이동한 횟수
익스텐트당 평균 페이지 수	페이지 체인에서 익스텐트당 페이지 수
검색 밀도[최적:실제]	백분율. Best Count 대 Actual Count의 비율. 모든 데이터가 인접한 경우 이 값은 100이고 이 값이 100보다 작으면 일부 데이터가 조각화된 것. 최적은 모든 데이터가 인접해서 연결되어 있는 경우 이상적인 익스텐트 변경 횟수이고 실제는 실제 익스텐트 변경 횟수
논리 검색 조각화 상태	인덱스의 리프 페이지 검색에서 반환된 순서가 잘못된 페이지의 비율
익스텐트 검색 조각화 상태	인덱스의 리프 페이지 검색에서 순서가 잘못된 익스텐트의 비율
페이지당 사용 가능한 평균 바이트 수	검색된 페이지에서 사용 가능한 평균 바이트 수. 이 값이 클수록 페이지의 사용률이 낮음. 인덱스의 임의 삽입 횟수가 적은 경우 이 값이 작은 것이 좋음. 이 값은 행 크기에 따라 달라지며 행 크기가 크면 값이 커짐.
평균 페이지 밀도(전체)	평균 페이지 밀도(백분율). 이 값은 행 크기의 영향을 받음. 따라서 이 값은 페이지의 꽉 찬 정도를 보다 정확하게 반영. 이 백분율 값이 클수록 좋음.

 

∂ 아래 명령을 통해 현재 선택된 데이터베이스 모든 테이블에 있는 인덱스에 대해서 Logical Fragmentation, ScanDensity 등을 한 번에 확인할 수 있다.

DBCC ShowContig('tbStuSet')
WITH TABLERESULTS, ALL_INDEXES

논리적 조각화 상태는 0%에 가까울 수록 좋다. 

 

② DMV 쿼리를 사용하여 인덱스 조각화 정도 확인  

SELECT  
	DB_NAME(database_id) AS [Database Name], 
	OBJECT_NAME(a.object_id) AS [Table Name], 
	a.index_id, b.name AS [Index Name], a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.tblb_Bbs'), NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b
	ON a.object_id = b.object_id
    AND a.index_id = b.index_id
WHERE a.database_id = DB_ID()

3) 데이터 및 인덱스 페이지를 순차적인 순서로 바꾸는 방법에는 다음의 5가지

 

① 해당 테이블 및 데이터베이스를 백업한 후 리스토어 하는 방법.

 

② DROP INDEX, CREATE INDEX를 이용해 인덱스를 재생성하는 방법. 

클러스터드 인덱스를 이 방법으로 적용하면, 삭제와 생성에 따라 모든 넌클러스터드 인덱스는 2번 리빌드된다.

인덱스에 PK, FK, Unique Constraint 가 있다면, DROP INDEX 가 불가능하다.

 

③ CREATE INDEX시 DROP_EXISTING옵션으로 재생성하는 방법.

DROP / REBUILD 가 하나의 트랜잭션으로 발생한다. 과거 인덱스의 모든 스키마를 알아야 하는 불편함은 있지만, 새롭게 인덱스를 생성하는 구문이므로, 모든 컬럼의 속성이나 인덱스 정의의 변경이 가능하다.

클러스터드 인덱스키 속성을 바꾸면 모든 넌클러스터드 인덱스는 한번 리빌드 된다. 정확히 같은 키로 리빌드한다면, 넌클러스터드 인덱스는 리빌드되지 않는다. 그러나, 클러스터드 인덱스이자 PK 등의 제약조건이 같이 있는 인덱스의 경우, 키 컬럼을 변경하면 에러가 발생한다.

 

④ ALTER INDEX  인덱스명 ON 테이블명 REBUILD로 인덱스를 재구축하는 방법

인덱스를 내부적으로 재생성한다. 재생성이 끝나면 기 존재하는 인덱스를 삭제한다. REBUILD 중 통계정보가 다시 계산된다.

인덱스가 LOB 데이타(TEXT, NTEXT, VARCHAR(max), NVARCHAR(max), NVARBINARY(max), IMAGE AND XML Data Type 컬럼)을 포함하고 있다면, Online으로 빌드 될수 없다.

INDEX REBUILD를 할 경우 ALL 옵션을 사용하지 않을 경우, 해당 인덱스가 클러스터 인덱스이고, 그 클러스터 인덱스에 관계된 Secondary Index가 있는 경우 Secondary Index는 자동으로 REBUILD 되지 않는다. 별도로 Secondary Index를 REBUILD 해주거나 ALL 옵션을 꼭 줘야한다.

다중의 파티션에 걸쳐 있는 인덱스를 Online 환경에서 REBUILD할 경우 전체 인덱스를 모두 REBUILD해야 한다.(전체 파티션을 모두 REBUILD)

 

⑤ ALTER INDEX 인덱스명 ON 테이블명 REORGANIZE로 인덱스 조각화를 조절하는 방법. 

인덱스 리프노드를 물리적으로 재구성한다. 통계 정보를 생성 하지 않는다.

REORGANIZE는 본질적으로 페이지를 다른 페이지와 바꾸는 것이기 때문에 다른 Free Space를 요구하지 않는다.

COMPACTION(꽉 채움, 간결화)와 DEFRAG(조각모음)을 수행하면서 사용되지 않는 Page들을 삭제한다.

REORGANIZE는 대부분의 논리적인 Fragmentation을 제거할수있다. 하지만 전후의 Extents가 물리적으로 계속되고 있는 확장된 Fragmentation를 반드시 바로 잡을수는 없다.

REORGANIZE는 DBCC INDEXDEFRAG와 거의 동일하지만, DBCC INDEXDEFRAG는 내부 잠금이 발생하여 온라인으로 사용할수 없지만 REORGANIZE 는 내부 잠금이 없기 때문에 온라인으로 사용할 수 있다.

파티셔닝 된 인덱스에서 하나의 인덱스가 여러개의 파티션으로 구성된 상황에서 하나의 인덱스 파티션만 REORGANIZE 할수 있다.

 

 

[한방에 인덱스를 정리하는 쿼리]

DECLARE @v_Num INT 
DECLARE @v_SQL VARCHAR(1000)
DECLARE @v_TableName VARCHAR(1000)
DECLARE @v_OwnerName VARCHAR(1000)

SET @v_Num = 1

DECLARE DB_Cursor CURSOR FOR 
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @v_OwnerName, @v_TableName
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @v_SQL = 'ALTER INDEX ALL ON ' + @v_OwnerName + '.' + @v_TableName + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '
     EXEC(@v_SQL)
 
    PRINT CONVERT(VARCHAR(16), @v_Num) + '__' + @v_OwnerName + '.' + @v_TableName + '............ OK'
 
    SET @v_Num = @v_Num + 1

    FETCH NEXT FROM DB_Cursor INTO @v_OwnerName, @v_TableName
END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

ALTER INDEX ALL ON dbo.tbStuSet REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90)

 

4) 인덱스 정리후 변화

DBCC ShowContig('tbStuSet')

DBCC SHOWCONTIG이(가) 'tbStuSet' 테이블을 검색하는 중...
테이블: 'tbStuSet'(1010102639); 인덱스 ID: 1, 데이터베이스 ID: 5
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 438
- 검색한 익스텐트 ..............................: 57
- 익스텐트 스위치..............................: 56
- 익스텐트당 평균 페이지 수........................: 7.7
- 검색 밀도[최적:실제].......: 96.49% [55:57]
- 논리 검색 조각화 상태 ..................: 0.68%
- 익스텐트 검색 조각화 상태 ...................: 14.04%
- 페이지당 사용 가능한 평균 바이트 수.....................: 797.3
- 평균 페이지 밀도(전체).....................: 90.15%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

Completion time: 2022-10-01T16:59:03.3453526+09:00