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.