반응형
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
반응형
'[DataBase] > MSSQL' 카테고리의 다른 글
[MSSQL] 현재 락 걸린 쿼리 확인 & 죽이기 (0) | 2022.04.04 |
---|---|
[MSSQL] 싱글유저 모드에서 멀티유저 모드 변경 오류 (0) | 2022.03.24 |
[MSSQL] 보안 주의점 해킹시도 (0) | 2022.02.10 |
[MSSQL][시점복구] DB 원하는 시점으로 복구하기(+복구시점찾기, + 복구진행현황보기) (0) | 2022.01.18 |
[MSSQL TARNSACTION] 제발 작업할때 트랜좀 걸고해 (0) | 2022.01.17 |