Blog

SQL Server Rebuild Indexes – The Fastest Way

You can write SQL which outperforms and is bug free.

Get your copy of my SQL Best Practices and start writing the SQL which sails through code reviews.

Cost? Pay what you want :)


Student or just starting your career? Give yourself a break and get it for free.

Database professional? Think about your hourly rate and how much time you'll save with crisp and clear best practices.
Contribute over $27 and I'll send you the Word version of the doc so you can easily customize it for yourself and your team.

Get SQL Best Practices

Want to rebuild all the indexes in your MS SQL database?  The script below will rebuild all the indexes in your SQL Server 2008 or SQL Server 2005 (SP2) database and offers a number of enhancements that make this the best SQL Server index rebuild script in all the land and the first choice of SQL Server consultants:

  1. Rebuilds indexes on smallest tables first, allowing the maximum number of indexes to be rebuilt in the shortest amount of time.
  2. Real time progress updates, allowing you to estimate how much time is remaining before completion.
  3. Correctly handles multiple schemas, a common flaw in other scripts.

SET NOCOUNT ON
GO

--Set the fillfactor
DECLARE @FillFactor TINYINT
SELECT @FillFactor=80

DECLARE @StartTime DATETIME
SELECT @StartTime=GETDATE()

if object_id('tempdb..#TablesToRebuildIndex') is not null
begin
drop table #TablesToRebuildIndex
end

DECLARE @NumTables VARCHAR(20)

SELECT
s.[Name] AS SchemaName,
t.[name] AS TableName,
SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM
sys.schemas s
LEFT JOIN sys.tables t
ON  s.schema_id = t.schema_id
LEFT JOIN sys.partitions p
ON  t.object_id = p.object_id
LEFT JOIN sys.allocation_units a
ON  p.partition_id = a.container_id
WHERE
p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
AND p.rows IS NOT NULL
AND a.type = 1  -- row-data only , not LOB
GROUP BY
s.[Name],
t.[name]
SELECT @NumTables=@@ROWCOUNT

DECLARE RebuildIndex CURSOR FOR
SELECT
ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
ttus.SchemaName,
ttus.TableName,
ttus.RowsInTable
FROM
#TablesToRebuildIndex AS ttus
ORDER BY
ttus.RowsInTable
OPEN RebuildIndex

DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
RAISERROR (@Status, 0, 1) WITH NOWAIT  --RAISERROR used to immediately output status

SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
EXEC sp_executesql @Statement

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
END

CLOSE RebuildIndex
DEALLOCATE RebuildIndex

drop table #TablesToRebuildIndex

Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'

GO

 

A quick followup based on the comment from Jack Smith. There is no need to specify ALLOW_PAGE_LOCKS=ON since it is actually the default for SQL Server 2005 through SQL Server 2012 (per MSDN ALTER INDEX Transact-SQL article).

Tags: , , , , ,

No comments yet.

Leave a Reply