SQL script executes forever - possible loop?

Question

I hope that someone can help me with this script. I wanted to create an SQL script to rebuild all non clustered index with "online ON" option to prevent locking the tables while rebuilding the indexes, and also without using a cursor (which slow things down). For that reason I used While loop (at least what most SQL developers suggested), so I wrote an SQL script. What I noticed, SQL script executes forever. On one test machine I left it running for 12 minutes, after which I aborted the operation...

Can some SQL guru please tell me what is causing a bottle neck? Is there a better way to do this, or perhaps to "fine tune" current script?

Sample of old code:

USE MASTER
GO
DECLARE @DbName AS VARCHAR(50),
    @DBIndexName AS VARCHAR(250), 
    @DBTableName AS VARCHAR(100), 
    @AlterCommand AS VARCHAR(500),
    @SwitchDB AS NVARCHAR(50)

SELECT name INTO #DatabaseList FROM master..sysdatabases
WHILE EXISTS (SELECT * FROM #DatabaseList)
BEGIN
    SELECT TOP 1 @DbName = name FROM #DatabaseList
    ORDER BY name ASC

    SET @SwitchDB = 'USE '+@DbName
    EXEC(@SwitchDB)

    -- pronaći sve index-e koji imaju fragmentaciju veću od 10%, te staviti u tablicu
    SELECT object_name(dt.object_id) Tablename,si.name
    IndexName,dt.avg_fragmentation_in_percent AS
    ExternalFragmentation,dt.avg_page_space_used_in_percent AS
    InternalFragmentation
    INTO #FragmIndex
    FROM
    (
        SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
        FROM sys.dm_db_index_physical_stats (db_id(@DbName),null,null,null,'DETAILED')  
        WHERE index_id <> 0
    ) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
    AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
    AND dt.avg_page_space_used_in_percent<75 and si.type_desc = 'NONCLUSTERED' ORDER BY avg_fragmentation_in_percent DESC 

    -- Napraviti petlju koja će izvršiti rebuild svih indexa
    SET @SwitchDB = 'USE master'
    EXEC (@SwitchDB)

    WHILE EXISTS (SELECT * FROM #FragmIndex)
    BEGIN
        SELECT TOP 1 @DBIndexName = IndexName, @DBTableName = Tablename FROM #FragmIndex
        ORDER BY IndexName ASC

        -- rebuild index command setiranje na "ONLINE ON" kako bi se izbjeglo "zaključavanje tablice".
        SET @AlterCommand ='ALTER INDEX '+@DBIndexName+' ON '+ @DbName+'.dbo.'+@DbTableName +'REBUILD WITH (FILLFACTOR=80,STATISTICS_NORECOMPUTE = ON,ONLINE=ON)'
        EXEC(@AlterCommand)

        DELETE #FragmIndex 
        WHERE IndexName = @DBIndexName
    END

    DELETE #DatabaseList 
    WHERE name = @DbName
END
DROP TABLE #DatabaseList
GO

Thank you very much, and best regards.

---

Just to add new "corrected code" - for all of you that wish to use it, modify it or whatever... :)

USE MASTER
GO
DECLARE @DbName AS VARCHAR(50),
    @DBIndexName AS VARCHAR(250), 
    @DBTableName AS VARCHAR(100), 
    @AlterCommand AS VARCHAR(500),
    @SwitchDB AS NVARCHAR(50),
    @numEntries AS BIGINT,
    @numIndexEntries AS BIGINT

SELECT name INTO #DatabaseList FROM master..sysdatabases
SET @numEntries = (SELECT COUNT(*) FROM #DatabaseList)

WHILE @numEntries > 0 --EXISTS (SELECT * FROM #DatabaseList)
BEGIN
    SELECT TOP 1 @DbName = name FROM #DatabaseList
    ORDER BY name ASC

    SET @SwitchDB = 'USE '+@DbName
    EXEC(@SwitchDB)

    -- pronaći sve index-e koji imaju fragmentaciju veću od 10%, te staviti u tablicu
    SELECT object_name(dt.object_id) Tablename,si.name
    IndexName,dt.avg_fragmentation_in_percent AS
    ExternalFragmentation,dt.avg_page_space_used_in_percent AS
    InternalFragmentation
    INTO #FragmIndex
    FROM
    (
        SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
        FROM sys.dm_db_index_physical_stats (db_id(@DbName),null,null,null,'DETAILED')  
        WHERE index_id <> 0
    ) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
    AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
    AND dt.avg_page_space_used_in_percent<75 and si.type_desc = 'NONCLUSTERED' ORDER BY avg_fragmentation_in_percent DESC 

    -- Napraviti petlju koja će izvršiti rebuild svih indexa
    SET @SwitchDB = 'USE master'
    EXEC (@SwitchDB)

    SET @numIndexEntries = (SELECT COUNT(*) FROM #FragmIndex)
    WHILE @numIndexEntries > 0 --EXISTS (SELECT * FROM #FragmIndex)
    BEGIN
        SELECT TOP 1 @DBIndexName = IndexName, @DBTableName = Tablename FROM #FragmIndex
        ORDER BY IndexName ASC

        -- rebuild index command setiranje na "ONLINE ON" kako bi se izbjeglo "zaključavanje tablice".
        SET @AlterCommand ='ALTER INDEX '+@DBIndexName+' ON '+ @DbName+'.dbo.'+@DbTableName +'REBUILD WITH (FILLFACTOR=80,STATISTICS_NORECOMPUTE = ON,ONLINE=ON)'
        EXEC(@AlterCommand)

        DELETE #FragmIndex WHERE IndexName = @DBIndexName
        SET @numIndexEntries = (SELECT COUNT(*) FROM #FragmIndex)
    END

    DELETE #DatabaseList WHERE name = @DbName
    SET @numEntries = (SELECT COUNT(*) FROM #DatabaseList)
END
DROP TABLE #DatabaseList
GO

P.S: if you have any other suggestions, or found bug in the script - for improving the code, please let me know.

Answer 1

Two possible problems might cause that effect.

First of all, the DELETE statement may not really delete anything. You can easily check this by PRINTing the number of entries in #DatabaseList after every DELETE statement.

Secondly, the EXISTS (SELECT * FROM #DatabaseList) could just be evaluated once, not upon every iteration of the loop.

I'd change this as follows:

...

DECLARE @numEntries BIGINT
SET @numEntries = (SELECT COUNT(*) FROM #DatabaseList)

WHILE @numEntries > 0
BEGIN
    ...

    DELETE #DatabaseList WHERE name = @DbName
    SET @numEntries = (SELECT COUNT(*) FROM #DatabaseList)
END

...