February 22, 2010 at 2:08 am
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
SELECTOBJECT_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@TableNameNVARCHAR(MAX)
DECLARE@IndexNameNVARCHAR(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
February 22, 2010 at 4:19 am
Hi Stefan,
Have you looked at Michelle Ufford's Index Defrag Script?
You can find it here: http://sqlfool.com/2009/06/index-defrag-script-v30/
Paul
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply