NAV SQL Indexes

In some companies it’s almost impossible to make a full index rebuild, because they have only a few hours of maintenance windows

You can adapt this script to rebuild a specific number of indexes during each day, during a specific hours. If you have several companies in a database you can even adapt it to rebuild each company at a specific hour of day.

This scripts only runs in SQL Server 2005.
This script isn’t complete you have to adapt it to your current company and define fragmentation values.

Create a table to store indexes and values.

-- TABLE OF INDEXES

CREATE TABLE IndexFragList
( 

DatabaseId     INT,
IndexId           INT,
ObjectID         INT,
IndexName     CHAR (255),
AvgFrag         DECIMAL,
TableName    VARCHAR(255)

)

DECLARE @Maxfrag DECIMAL
DECLARE @Minfrag DECIMAL
DECLARE @DatabaseID INT
DECLARE @IndexId INT

DECLARE @IndexName VARCHAR(255)
DECLARE @AvgFrag DECIMAL
DECLARE @ObjectID INT
DECLARE @TableName VARCHAR(6000)
DECLARE @Command VARCHAR(255)

DECLARE @Count INT

SELECT @Maxfrag = 30.0
SELECT @Minfrag = 0.05

SET @COUNT = (SELECT COUNT(*) FROM IndexFragList)
PRINT 'COUNT.....' + CONVERT(VARCHAR(10), @COUNT)

IF @COUNT = 0
BEGIN
-- FILL INDEXES
INSERT INTO IndexFragList(DatabaseId, IndexId, ObjectID, IndexName, AvgFrag, TableName)

SELECT
a.database_id, a.index_id, a.object_id, b.name, avg_fragmentation_in_percent avg_frag, t.name
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
JOIN sys.tables as t ON a.object_id = t.object_id
AND avg_fragmentation_in_percent > @Minfrag
END

-- CUSTOMIZE THIS
-- IF CAN FILTER BY COMPANY, ETC
DECLARE indexes CURSOR FOR
SELECT DatabaseID, IndexId, ObjectID, IndexName, IndexId, AvgFrag, TableName
FROM IndexFragList ORDER BY AvgFrag DESC
OPEN indexes
FETCH NEXT FROM indexes INTO
@DatabaseID, @IndexId, @ObjectID, @IndexName, @IndexId, @AvgFrag, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Command='ALTER index [' + RTRIM(@IndexName) + '] ON [' + @TableName + '] '
-- REBUILD INDEX

IF @AvgFrag > @Maxfrag BEGIN
SET @Command=@Command + ' REBUILD'
EXEC(@Command)
END

-- REORGANIZE INDEX
ELSE IF (@AvgFrag > @Minfrag) AND (@AvgFrag > @Maxfrag) BEGIN
SET @Command=@Command + ' REORGANIZE'
EXEC(@Command)
END

DELETE FROM IndexFragList
WHERE CURRENT OF indexes

-- CUSTOMIZE THIS
-- YOU CAN BREAK BY HOURS, NUMBER OF INDEXES, ETC
BREAK;

FETCH NEXT FROM indexes INTO
@DatabaseID, @IndexId, @ObjectID, @IndexName, @IndexId, @AvgFrag, @TableName
END

CLOSE indexes
DEALLOCATE indexes