{"id":72,"date":"2010-11-30T15:57:57","date_gmt":"2010-11-30T22:57:57","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=72"},"modified":"2012-12-06T16:29:09","modified_gmt":"2012-12-06T23:29:09","slug":"sql-server-rebuild-indexes-the-fastest-way","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2010\/11\/sql-server-rebuild-indexes-the-fastest-way\/","title":{"rendered":"SQL Server Rebuild Indexes – The Fastest Way"},"content":{"rendered":"

Want to rebuild all the indexes in your MS SQL database?\u00a0 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:<\/p>\n

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

    <\/p>\n

    SET NOCOUNT ON
    \nGO<\/p>\n

    --Set the fillfactor
    \nDECLARE @FillFactor TINYINT
    \nSELECT @FillFactor=80<\/p>\n

    DECLARE @StartTime DATETIME
    \nSELECT @StartTime=GETDATE()<\/p>\n

    if object_id('tempdb..#TablesToRebuildIndex') is not null
    \nbegin
    \ndrop table #TablesToRebuildIndex
    \nend<\/p>\n

    DECLARE @NumTables VARCHAR(20)<\/p>\n

    SELECT
    \ns.[Name] AS SchemaName,
    \nt.[name] AS TableName,
    \nSUM(p.rows) AS RowsInTable
    \nINTO #TablesToRebuildIndex
    \nFROM
    \nsys.schemas s
    \nLEFT JOIN sys.tables t
    \nON\u00a0 s.schema_id = t.schema_id
    \nLEFT JOIN sys.partitions p
    \nON\u00a0 t.object_id = p.object_id
    \nLEFT JOIN sys.allocation_units a
    \nON\u00a0 p.partition_id = a.container_id
    \nWHERE
    \np.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
    \nAND p.rows IS NOT NULL
    \nAND a.type = 1\u00a0 -- row-data only , not LOB
    \nGROUP BY
    \ns.[Name],
    \nt.[name]
    \nSELECT @NumTables=@@ROWCOUNT<\/p>\n

    DECLARE RebuildIndex CURSOR FOR
    \nSELECT
    \nROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
    \nttus.SchemaName,
    \nttus.TableName,
    \nttus.RowsInTable
    \nFROM
    \n#TablesToRebuildIndex AS ttus
    \nORDER BY
    \nttus.RowsInTable
    \nOPEN RebuildIndex<\/p>\n

    DECLARE @TableNumber VARCHAR(20)
    \nDECLARE @SchemaName NVARCHAR(128)
    \nDECLARE @tableName NVARCHAR(128)
    \nDECLARE @RowsInTable VARCHAR(20)
    \nDECLARE @Statement NVARCHAR(300)
    \nDECLARE @Status NVARCHAR(300)<\/p>\n

    FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
    \nWHILE ( @@FETCH_STATUS = 0 )
    \nBEGIN
    \nSET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
    \nRAISERROR (@Status, 0, 1) WITH NOWAIT\u00a0 --RAISERROR used to immediately output status<\/p>\n

    SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
    \nEXEC sp_executesql @Statement<\/p>\n

    FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
    \nEND<\/p>\n

    CLOSE RebuildIndex
    \nDEALLOCATE RebuildIndex<\/p>\n

    drop table #TablesToRebuildIndex<\/p>\n

    Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'<\/p>\n

    GO<\/p>\n

     <\/p><\/blockquote>\n

    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<\/a> article).<\/p>\n","protected":false},"excerpt":{"rendered":"

    Want to rebuild all the indexes in your MS SQL database?\u00a0 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,3],"tags":[13,10,4,9,12,11],"class_list":["post-72","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-scripts","tag-indexes","tag-ms-sql","tag-performance","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/72","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=72"}],"version-history":[{"count":7,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/72\/revisions"}],"predecessor-version":[{"id":348,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/72\/revisions\/348"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=72"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=72"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=72"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}