Hi all,
because we use the the express Database in some cases we decided to look for an alternative for the SQL Server Agent Rebuild Index mechanism. So I tried this Code, called by our leading system every night:
DECLARE IndexCursor CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT OBJECT_SCHEMA_NAME(OBJECT_ID) + '.' + OBJECT_NAME(OBJECT_ID) AS TableName,
SI.NAME AS IndexName
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') AS DPS
INNER JOIN sysindexes AS SI
ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDID
WHERE AVG_FRAGMENTATION_IN_PERCENT > 5
AND (INDEX_TYPE_DESC = 'CLUSTERED INDEX' OR INDEX_TYPE_DESC = 'NONCLUSTERED INDEX')
DECLARE @TableName NVARCHAR(MAX)
DECLARE @IndexName NVARCHAR(MAX)
OPEN IndexCursor
WHILE (1=1) BEGIN
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName
IF (@@FETCH_STATUS <> 0) BREAK
EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD;')
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
After a few tests it looks good, but in one big database I get problems with timeouts and filegrowth.
Is my approach generally practicable? Could this be the reason for my timeouts?
Regards from Germany
Stefan