1 인덱스 단편화에 따른 인덱스 재생성 또는 조각모음 스크립트 생성

Use 데이터베이스명 -- 여기에 인덱스 조각모음을 할 인덱스 데이터베이스명을 적는다.
GO

Create Table Rebuild_Index_Table(
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)
GO

INSERT INTO Rebuild_Index_Table
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')
GO

--70% 이하로 조각난 것들
SELECT ObjectName, IndexName, CountPages,  
CAST(BestCount As Numeric) /  CAST(ActualCount As Numeric) * 100 'ScanDensity'
FROM Rebuild_Index_Table
WHERE CAST(BestCount As Numeric) /  CAST(ActualCount As Numeric) * 100 < 70
AND ObjectName NOT LIKE 'sys%'
AND ActualCount > 0
AND IndexName > ''

--인덱스 재생성 스크립트
SELECT 'DBCC DBREINDEX ([' + CAST(RTRIM(ObjectName) AS VARCHAR(255)) + '],' + CAST(RTRIMIndexName) AS VARCHAR(255)) + ',' + '100)'
FROM Rebuild_Index_Table
WHERE CAST(BestCount As Numeric) /  CAST(ActualCount As Numeric) * 100 < 70
AND ObjectName NOT LIKE 'sys%'
AND ActualCount > 0
AND IndexName > ''

--인덱스 조각모음 스크립트
SELECT 'DBCC INDEXDEFRAG(0,[' + CAST(RTRIM(ObjectName) AS VARCHAR(255)) + '],' + CASTRTRIM(IndexName) AS VARCHAR(255)) +')'
FROM Rebuild_Index_Table
WHERE CAST(BestCount As Numeric) /  CAST(ActualCount As Numeric) * 100 < 70
AND ObjectName NOT LIKE 'sys%'
AND ActualCount > 0
AND IndexName > ''

Drop table Rebuild_Index_Table
--인덱스 조각화 정보를 보관하시려면 Drop Table을 하지 않으셔도 됩니다.