0

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.

0

1 Answer 1

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

...
Sign up to request clarification or add additional context in comments.

7 Comments

Hi Thorsten, I already simulated DELETE #DatabaseList and it "removes" that specific database name from the list, even though, your suggestion is definitely better. I have a feeling that calculating the fragmantation of indexes might be a problem, since one database has 50GB size and 505 tables, probably between 1000-1500 indexes.
Actually, while I was writing the comment above, calculation of fragmentation has finished. There is total of 622 non clustered indexes. That was the issue, or should I say a bottle neck. It took full 32min 55 sec to complete, just for one database.
Just additional question, I once read that it is not wise to rebuild clustered indexes since you may broke database integrity. I am not sure if understand correctly, every rebuild is dropping the index and rebuilding it along with primary and foreign key. Is this true?
@dovla091 I'm the wrong person to ask for such specific details on indices :-) I suggest you create a new question for some expert to answer.
No. Rebuilding clustered indexes will not break database integrity. I suggest you add some RAISERROR WITH NOWAIT to spit out some messages while your script runs. mssqltips.com/sqlservertip/1660/…
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.