-- 인덱스 조각화 조회
SELECT
distinct 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 -- 시스템 개체 무시
and ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) > 10
-- 조각화 테이블에 대한 인덱스 리빌드 및 통계 업데이트.
set quoted_identifier OFF
DECLARE @tablename varchar(255)
DECLARE @tablename_header varchar(255)
declare @sql varchar(1000)
DECLARE tnames_cursor CURSOR FOR SELECT
distinct 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 -- 시스템 개체 무시
and ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) > 10
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @tablename_header = "Updating Indexes FOR table " + RTRIM(UPPER(@tablename))
PRINT @tablename_header
SET @sql = 'ALTER INDEX ALL ON ' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '
EXEC (@sql)
PRINT (@sql)
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT " "
PRINT " "
SELECT @tablename_header = "************* NO MORE TABLES" +
" *************"
PRINT @tablename_header
PRINT " "
PRINT "Indexes have been updated FOR ALL tables."
DEALLOCATE tnames_cursor
go
-- 통계 업데이트
EXEC sp_updatestats @resample='resample';
GO
set quoted_identifier ON
결과 로그
리빌드 후 인덱스 조각화 조회
'SQL > msSQL' 카테고리의 다른 글
| [Server Management Studio] 데이터 내보내기 (0) | 2023.02.21 |
|---|---|
| [SQL SM Studio] 계정 생성 및 백업 (0) | 2023.02.17 |
| [SQL 오류] '하나 이상의 개체가 이 열에 액세스 하므로 ...' (0) | 2021.08.17 |
