Database Reference
In-Depth Information
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctFrag BETWEEN 20.0 AND 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName
+ '.' + @SchemaName + '.' + @TableName + ' REORGANIZE';
EXEC sp_executesql @Defrag;
PRINT 'Reorganize index: ' + @DBName + '.' + @SchemaName + '.'
+ @TableName + '.' + @IndexName;
END
ELSE
IF @PctFrag > 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON '
+ @DBName + '.' + @SchemaName + '.' + @TableName
+ ' REBUILD';
EXEC sp_executesql @Defrag;
PRINT 'Rebuild index: ' + @DBName + '.' + @SchemaName
+ '.' + @TableName + '.' + @IndexName;
END
FETCH NEXT FROM cList
INTO @DBName,@TableName,@SchemaName,@IndexName,@PctFrag;
END
CLOSE cList;
DEALLOCATE cList;
DROP TABLE #Frag;
GO
To automate the fragmentation analysis process, you can create a SQL Server job from SQL Server Enterprise
Manager by following these simple steps:
1.
Open Management Studio, right-click the SQL Server Agent icon, and select New
➤
Job.
2.
On the General page of the New Job dialog box, enter the job name and other details,
as shown in Figure
13-22
.
