Blog

SQL Server Rebuild Indexes – The Fastest Way

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).

Looking to improve your SSRS skills? This online course at Udemy looks like a great option: Practical SQL Reporting with SSRS

Tags: , , , , ,

36 Responses to “SQL Server Rebuild Indexes – The Fastest Way”

  1. Mahender May 15, 2012 at 5:33 am #

    great script.. good one and very fast, i test it on my staging server, but can i put this on my production server?????

  2. dbo May 21, 2012 at 8:53 am #

    You can definitely run this on a production server, just keep in mind that rebuilding indexes definitely has a performance impact on both your database and the server as a whole so it's best to run during periods with the least likely chance of creating conflicts with user activities!

  3. bhavtosh May 30, 2012 at 11:55 pm #

    good one and fast too :)
    thanks

  4. dbo June 13, 2012 at 2:22 pm #

    Thanks! Glad you found it useful!

  5. sunil July 19, 2012 at 12:24 am #

    It's a good article.
    It solved my problem.

  6. dbo August 1, 2012 at 10:36 am #

    Excellent Sunil!

  7. Prashanth August 15, 2012 at 8:30 pm #

    Very good script, thanks a lot for providing it

  8. vanitha September 18, 2012 at 1:49 am #

    Can any one help me by suggesting a query to rebuild indexes?

  9. dbo September 26, 2012 at 11:57 am #

    Glad it did the trick for you!

  10. dbo September 26, 2012 at 11:57 am #

    Were you able to use the script in this post?

  11. Andrea October 19, 2012 at 3:15 am #

    Your script worked fine, thanks for the excellent post!

  12. Jack Smith October 19, 2012 at 1:23 pm #

    Quick question, should this script also set allow_page_locks= on
    ? Thanks.

  13. dbo October 19, 2012 at 3:30 pm #

    Glad it helped Andrea!

  14. dbo October 19, 2012 at 3:34 pm #

    Good question Jack. ALLOW_PAGE_LOCKS=ON is actually the default for SQL Server 2005 through SQL Server 2012 so there is no need to specify it.

  15. Doug October 25, 2012 at 7:40 pm #

    Great article. I will be using this for a partial delete on a non-prod database. One of my first steps is to disable indexes. This script will help to re-enable them and get them updated. Thank you

  16. Pete M October 26, 2012 at 4:24 am #

    ???? I get an error: "Msg 195, Level 15, State 10, Line 39
    'ROW_NUMBER' is not a recognized function name."
    Anyone know why ? Ta !!

  17. Phil Steffek October 26, 2012 at 8:52 am #

    Hi Pete,
    What version of SQL Server are you on?

  18. Phil Steffek October 26, 2012 at 8:57 am #

    No problem Doug. Good luck!

  19. Dennis December 4, 2012 at 2:26 pm #

    Excellent post! I can't wait to try it!

    I typically do individual tables based on how bad the indexes have become fragmented.

    I notice that you don't use a "rebuild with" that I usually add:
    Rebuild with(Sort_in_tempdb = on);

    I do that because my tempdb is on the local HDD and hopefully reduce the time needed to create the index.

    Any drawbacks that you would be aware of in regards to adding this to a maintenance plan to run at the wee hours of the morning when the system is virtually quiet?

  20. Phil Steffek December 6, 2012 at 4:28 pm #

    Hope it works out for you Dennis. Running this in the "wee hours" is the perfect time. You may want to consider modifying the script to not output all the status messages though or alternately to send the messages to the log using the WITH LOG option of RAISEERROR.

  21. lorrenzo Mokgosana December 12, 2012 at 1:14 am #

    i'm getting the same error""Msg 195, Level 15, State 10, Line 39
    'ROW_NUMBER' is not a recognized function name." and below is my SQL version

    "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

  22. Phil Steffek December 14, 2012 at 2:41 pm #

    Bad news: row_number wasn't added till SQL Server 2005. It should be possible to do something similar in 2000 by creating a temp table with an identity column...

  23. ramesh January 6, 2013 at 11:42 pm #

    it is very useful for me. i need one help . how to schedule this script in jobs.

  24. Maisha January 15, 2013 at 10:53 am #

    Thanks a lot! I've been trying to Rebuild Index by script since two week now...have executed all the script I found online and none of them actually rebuild the indexes. Today is the last day my task is due and I've been blessed by your script!! God bless you!!!

  25. Phil Steffek January 15, 2013 at 2:48 pm #

    Wow! That's great to hear :)

  26. GOT May 17, 2013 at 12:42 pm #

    I rarely do post comments...

    BUT this script ! I LOVE YOU DUDE !
    Solved a BIG headache of mine.

    Had 5 big super slow servers with awful architecture(may god bless them !) to maintain.

    1st server, took all precautions and executed the script. after 33 minutes...voila ! perfect !

    then, 2-5 server at a go...and this comment and off to sleep.......
    may god bless you :)

  27. Phil Steffek August 2, 2013 at 4:39 pm #

    Right on GOT. Glad it did the trick and thanks for the feedback!

  28. Johan September 13, 2013 at 1:17 am #

    Great script, thanks man!

    If you're looking to improve it, perhaps insert information into a log-table?

  29. Mahesh Padekar October 21, 2013 at 6:08 am #

    Great Script. But I don't see any try-catch code. Won't it be problematic if the script fails due to some error like this "The transaction log for database is full due to 'LOG_BACKUP'"?

  30. Phil Steffek October 21, 2013 at 9:00 am #

    Glad you liked the script Mahesh and thank for leaving a comment! In the case where the transaction log gets full the script would end up failing at that specific point (with no impact on the indexes which had already been rebuilt). What were you thinking you would have in the catch block?

  31. ab workout plan for women November 8, 2013 at 5:17 pm #

    The person wantfing to use weight training equipment and techniques
    could end up spending thousands on an aat home system while the man witgh the need
    to just firm up his middle could spend next to nothing learning new floor exercises.
    But, like a lot of things in life, the regular crunches that we used to do in gym class need improved into a much better exercise
    and one that is overall much better for your whole system.
    Never in a million years would you think to do these exercises in
    orderto get great abs.

  32. Davis December 10, 2013 at 3:01 am #

    Thank you So much!!

  33. David January 28, 2014 at 4:13 pm #

    Trying it now. Is there a way to just focus on one database?

  34. vijay February 21, 2014 at 8:30 pm #

    This is just awesome ... Just what I was looking for.

    Is there any way I can avoid rebuilding just one table.

  35. Dennis June 5, 2014 at 7:34 am #

    >>Posted November 30, 2010

    It is now June 2014. Is this still a relevant script?

    -Dennis

  36. Dimssy September 17, 2014 at 9:34 am #

    How would you include the rebuild of the non-clustered Indexes

Leave a Reply