MSSQL 인덱스 재구성, 인덱스 조각화 모음

MSSQL 인덱스 재구성, 인덱스 조각화 모음

인덱스 조각화 모음이 필요한 이유.

• 인덱스 생성 후 해당 테이블에 데이터 변경이 가해져 페이지에 빈 공간이 생기거나 페이지들의 순서가 맞지 않게 된다.
• 인덱스 조각화가 심하면 조회시 성능 저하, 불필요한 공간 차지 등이 발생한다.

해결방법에는 인덱스 재구성 or 인덱스 조각화 모음이 있다.

• 인덱스 재구성
  • 장점 : 효과 가장 좋음
  • 단점 : 서비스 중단 발생
• 인덱스 조각모음
  • 장점 : 서비스 무중단
  • 단점 : 완벽한 조각모음 아님

특정 테이블 인덱스 재구성

특정 테이블 인덱스 조각화 정도 확인 쿼리

• 테이블로 보기

DBCC SHOWCONTIG (테이블명) WITH TABLERESULTS

• 한번에 보기

DBCC SHOWCONTIG (테이블명)

특정 테이블 인덱스 재구성 쿼리

DBCC INDEXDEFRAG(DB명, 테이블명, 인덱스명)

---

모든 테이블 한번에 조각화 모음

모든 테이블 조각화 정도 확인 쿼리

 SELECT

            DB_NAME() AS 'DatabaseName'

          , OBJECT_NAME(S.OBJECT_ID) AS 'TableName'

          , I.NAME AS 'IndexName'

          , ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %'

 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S

 INNER JOIN SYS.INDEXES AS I

 ON S.OBJECT_ID = I.OBJECT_ID AND S.INDEX_ID = I.INDEX_ID

 WHERE S.DATABASE_ID = DB_ID() -- 현재 데이터베이스

 AND I.NAME IS NOT NULL -- HEAP은 무시

 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시

 ORDER BY [Fragmentation %] DESC

모든 테이블 인덱스 조각 모음 쿼리

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 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 @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '
 EXEC (@sql)
  PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor