<p>In the SSW rules to better SQL Server Database there is an example of a full database maintenance plan: <a href="http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLServerDatabases.aspx#MaintenancePlan" rel="noreferrer">SSW</a>. In the example they run both a Reorganize Index and then a Rebuild Index and then Update Statistics. Is there any point to this? I thought Reorganize Index was a fast but less effective version of Rebuild Index? and that an index rebuild would also update the statistics automatically (on the clustered index at least).</p>

[{'answer_id': 7595, 'author': 'Biri', 'author_id': 968, 'author_profile': 'https://Stackoverflow.com/users/968', 'pm_score': 5, 'selected': False, 'text': "<p>The reorganize and rebuild are different things.</p>\n\n<p>Reorganize: it's a defrag for indexes. Takes the existing index(es) and defragments the existing pages. However if the pages are not in a contiguous manner, they stays like before. Only the content of the pages are changing.</p>\n\n<p>Rebuild: actually it drops the index and rebuilds it from scratch. It means that you will get a completely new index, with defragmented and contiguous pages.</p>\n\n<p>Moreover with rebuild you can change partitioning or file groups, but with reorganize you can defrag not only the whole index, but also only one partition of the index.</p>\n\n<p>The update statistics is automatic on clustered indexes, but not on the non-clustered ones.</p>\n"}, {'answer_id': 7635, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': 3, 'selected': False, 'text': '<p>Exactly what <a href="https://stackoverflow.com/questions/7579/reorganise-index-vs-rebuild-index-in-sql-server-maintenance-plan?sort=Votes#7595">Biri</a> said. Here is how I would reindex an entire database:</p>\n\n<pre><code>EXEC [sp_MSforeachtable] @command1="RAISERROR(\'DBCC DBREINDEX(\'\'?\'\') ...\',10,1) WITH NOWAIT DBCC DBREINDEX(\'?\')"\n</code></pre>\n'}, {'answer_id': 41246, 'author': 'mrdenny', 'author_id': 4197, 'author_profile': 'https://Stackoverflow.com/users/4197', 'pm_score': 3, 'selected': False, 'text': '<p>When doing a reorg of an index, if the index is spread across two or more physical files the data will only be defragged within the data file.  Pages are not moved from one data file to another.</p>\n\n<p>When the index is in a single file the reorg and reindex will have the same end result.</p>\n\n<p>Some times the reorg will be faster, and some times the reindex will be faster depending on how fragmented the index is.  The less fragmented the index then a reorg will be faster, the more fragmented the slower the reorg will be, but the faster a reindex will be.</p>\n'}, {'answer_id': 9424260, 'author': 'Llew', 'author_id': 1229744, 'author_profile': 'https://Stackoverflow.com/users/1229744', 'pm_score': 6, 'selected': True, 'text': '<p>Doing a <code>REORGANIZE</code> and then a <code>REBUILD</code> on the same indexes is pointless, as any changes by the <code>REORGANIZE</code> would be lost by doing the <code>REBUILD</code>.</p>\n\n<p>Worse than that is that in the maintenance plan diagram from SSW, it performs a <code>SHRINK</code> first, which fragments the indexes as a side effect of the way it releases space. Then the <code>REBUILD</code> allocates more space to the database files again as working space during the <code>REBUILD</code> operation.</p>\n\n<ul>\n<li><p><code>REORGANIZE</code> is an online operation that defragments leaf pages in a clustered or non-clustered index page by page using little extra working space.</p></li>\n<li><p><code>REBUILD</code> is an online operation in Enterprise editions, offline in other editions, and uses as much extra working space again as the index size. It creates a new copy of the index and then drops the old one, thus getting rid of fragmentation. Statistics are recomputed by default as part of this operation, but that can be disabled.</p></li>\n</ul>\n\n<p>See <a href="http://msdn.microsoft.com/en-us/library/ms189858.aspx" rel="noreferrer">Reorganizing and Rebuilding Indexes</a> for more information.</p>\n\n<p>Don\'t use <code>SHRINK</code> except with the <code>TRUNCATEONLY</code> option and even then if the file will grow again then you should think hard as to whether it\'s necessary:</p>\n\n<p><a href="http://www.sqlservercentral.com/articles/SHRINKFILE/71414/" rel="noreferrer">sqlservercentral_SHRINKFILE</a> </p>\n'}, {'answer_id': 17312515, 'author': 'Chris KL', 'author_id': 58110, 'author_profile': 'https://Stackoverflow.com/users/58110', 'pm_score': 2, 'selected': False, 'text': "<p>Even better is:</p>\n\n<pre><code>EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REINDEX'\n</code></pre>\n\n<p>or</p>\n\n<pre><code>EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'\n</code></pre>\n"}, {'answer_id': 19003874, 'author': 'Ardalan Shahgholi', 'author_id': 2063547, 'author_profile': 'https://Stackoverflow.com/users/2063547', 'pm_score': 3, 'selected': False, 'text': "<p>I Use this SP </p>\n\n<pre><code>CREATE PROCEDURE dbo.[IndexRebuild]\nAS \nDECLARE @TableName NVARCHAR(500);\nDECLARE @SQLIndex NVARCHAR(MAX);\nDECLARE @RowCount INT;\nDECLARE @Counter INT;\n\nDECLARE @IndexAnalysis TABLE\n    (\n      AnalysisID INT IDENTITY(1, 1)\n                     NOT NULL\n                     PRIMARY KEY ,\n      TableName NVARCHAR(500) ,\n      SQLText NVARCHAR(MAX) ,\n      IndexDepth INT ,\n      AvgFragmentationInPercent FLOAT ,\n      FragmentCount BIGINT ,\n      AvgFragmentSizeInPages FLOAT ,\n      PageCount BIGINT\n    )\n\nBEGIN\n    INSERT  INTO @IndexAnalysis\n            SELECT  [objects].name ,\n                    'ALTER INDEX [' + [indexes].name + '] ON ['\n                    + [schemas].name + '].[' + [objects].name + '] '\n                    + ( CASE WHEN (   [dm_db_index_physical_stats].avg_fragmentation_in_percent &gt;= 20\n                                    AND [dm_db_index_physical_stats].avg_fragmentation_in_percent &lt; 40\n                                  ) THEN 'REORGANIZE'\n                             WHEN [dm_db_index_physical_stats].avg_fragmentation_in_percent &gt; = 40\n                             THEN 'REBUILD'\n                        END ) AS zSQL ,\n                    [dm_db_index_physical_stats].index_depth ,\n                    [dm_db_index_physical_stats].avg_fragmentation_in_percent ,\n                    [dm_db_index_physical_stats].fragment_count ,\n                    [dm_db_index_physical_stats].avg_fragment_size_in_pages ,\n                    [dm_db_index_physical_stats].page_count\n            FROM    [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL,\n                                                       NULL, 'LIMITED') AS   [dm_db_index_physical_stats]\n                    INNER JOIN [sys].[objects] AS [objects] ON (   [dm_db_index_physical_stats].[object_id] = [objects].[object_id] )\n                    INNER JOIN [sys].[schemas] AS [schemas] ON ( [objects].[schema_id]  = [schemas].[schema_id] )\n                    INNER JOIN [sys].[indexes] AS [indexes] ON (  [dm_db_index_physical_stats].[object_id] = [indexes].[object_id]\n                                                          AND  [dm_db_index_physical_stats].index_id = [indexes].index_id\n                                                          )\n            WHERE   index_type_desc &lt;&gt; 'HEAP'\n                    AND [dm_db_index_physical_stats].avg_fragmentation_in_percent &gt; 20\nEND\n\nSELECT  @RowCount = COUNT(AnalysisID)\nFROM    @IndexAnalysis\n\nSET @Counter = 1\nWHILE @Counter &lt;= @RowCount \n    BEGIN\n\n        SELECT  @SQLIndex = SQLText\n        FROM    @IndexAnalysis\n        WHERE   AnalysisID = @Counter\n\n        EXECUTE sp_executesql @SQLIndex\n\n        SET @Counter = @Counter + 1\n\n    END\n GO\n</code></pre>\n\n<p>and create One Job that execute this SP every week.</p>\n"}, {'answer_id': 26184119, 'author': 'mcfea', 'author_id': 984463, 'author_profile': 'https://Stackoverflow.com/users/984463', 'pm_score': 0, 'selected': False, 'text': '<p>My two cents... This method follows the spec outlined on tech net: <a href="http://technet.microsoft.com/en-us/library/ms189858(v=sql.105).aspx" rel="nofollow">http://technet.microsoft.com/en-us/library/ms189858(v=sql.105).aspx</a></p>\n\n<pre><code>USE [MyDbName]\nGO\n\nSET ANSI_NULLS OFF\nGO\n\nSET QUOTED_IDENTIFIER OFF\nGO\n\nCREATE PROCEDURE [maintenance].[IndexFragmentationCleanup]\nAS\nDECLARE @reIndexRequest VARCHAR(1000)\n\nDECLARE reIndexList CURSOR\nFOR\nSELECT INDEX_PROCESS\nFROM (\n    SELECT CASE \n            WHEN avg_fragmentation_in_percent BETWEEN 5\n                    AND 30\n                THEN \'ALTER INDEX [\' + i.NAME + \'] ON [\' + t.NAME + \'] REORGANIZE;\'\n            WHEN avg_fragmentation_in_percent &gt; 30\n                THEN \'ALTER INDEX [\' + i.NAME + \'] ON [\' + t.NAME + \'] REBUILD with(ONLINE=ON);\'\n            END AS INDEX_PROCESS\n        ,avg_fragmentation_in_percent\n        ,t.NAME\n    FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a\n    INNER JOIN sys.indexes AS i ON a.object_id = i.object_id\n        AND a.index_id = i.index_id\n    INNER JOIN sys.tables t ON t.object_id = i.object_id\n    WHERE i.NAME IS NOT NULL\n    ) PROCESS\nWHERE PROCESS.INDEX_PROCESS IS NOT NULL\nORDER BY avg_fragmentation_in_percent DESC\n\nOPEN reIndexList\n\nFETCH NEXT\nFROM reIndexList\nINTO @reIndexRequest\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    BEGIN TRY\n\n        PRINT @reIndexRequest;\n\n        EXEC (@reIndexRequest);\n\n    END TRY\n\n    BEGIN CATCH\n        DECLARE @ErrorMessage NVARCHAR(4000);\n        DECLARE @ErrorSeverity INT;\n        DECLARE @ErrorState INT;\n\n        SELECT @ErrorMessage = \'UNABLE TO CLEAN UP INDEX WITH: \' + @reIndexRequest + \': MESSAGE GIVEN: \' + ERROR_MESSAGE()\n            ,@ErrorSeverity = 9 \n            ,@ErrorState = ERROR_STATE();\n\n    END CATCH;\n\n    FETCH NEXT\n    FROM reIndexList\n    INTO @reIndexRequest\nEND\n\nCLOSE reIndexList;\n\nDEALLOCATE reIndexList;\n\nRETURN 0\n\nGO\n</code></pre>\n'}, {'answer_id': 28203806, 'author': 'vast', 'author_id': 1617079, 'author_profile': 'https://Stackoverflow.com/users/1617079', 'pm_score': 1, 'selected': False, 'text': '<p>I researched on web and found some of good articles. At the and i wrote the function and script below which is reorganize, recreate or rebuild all the indexes in a database.</p>\n\n<p>First you may need to read <a href="http://blogs.technet.com/b/sql_server_isv/archive/2010/10/18/index-fragmentation-if-it-isn-t-broke-don-t-fix-it.aspx" rel="nofollow">this article</a> to understand why we\'re not just recreate all indexes.  </p>\n\n<p>Second we need a function to build create script for index. So <a href="https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Generate-Index-fa790441" rel="nofollow">this article</a> may help. Also I\'m sharing working function below.</p>\n\n<p>Last step making a while loop to find and organize all indexes in the database. <a href="https://www.youtube.com/watch?v=-awkG4vt0Xo" rel="nofollow">This video</a> is grate example to make this. </p>\n\n<p>Function:</p>\n\n<pre><code>create function GetIndexCreateScript(\n    @index_name nvarchar(100)\n) \nreturns nvarchar(max)\nas\nbegin\n\ndeclare @Return   varchar(max)\n\nSELECT @Return = \' CREATE \' + \n    CASE WHEN I.is_unique = 1 THEN \' UNIQUE \' ELSE \'\' END  +  \n    I.type_desc COLLATE DATABASE_DEFAULT +\' INDEX \' +   \n    I.name  + \' ON \'  +  \n    Schema_name(T.Schema_id)+\'.\'+T.name + \' ( \' + \n    KeyColumns + \' )  \' + \n    ISNULL(\' INCLUDE (\'+IncludedColumns+\' ) \',\'\') + \n    ISNULL(\' WHERE  \'+I.Filter_definition,\'\') + \' WITH ( \' + \n    CASE WHEN I.is_padded = 1 THEN \' PAD_INDEX = ON \' ELSE \' PAD_INDEX = OFF \' END + \',\'  + \n    \'FILLFACTOR = \'+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + \',\'  + \n    -- default value \n    \'SORT_IN_TEMPDB = OFF \'  + \',\'  + \n    CASE WHEN I.ignore_dup_key = 1 THEN \' IGNORE_DUP_KEY = ON \' ELSE \' IGNORE_DUP_KEY = OFF \' END + \',\'  + \n    CASE WHEN ST.no_recompute = 0 THEN \' STATISTICS_NORECOMPUTE = OFF \' ELSE \' STATISTICS_NORECOMPUTE = ON \' END + \',\'  + \n    -- default value  \n    \' DROP_EXISTING = ON \'  + \',\'  + \n    -- default value  \n    \' ONLINE = OFF \'  + \',\'  + \n   CASE WHEN I.allow_row_locks = 1 THEN \' ALLOW_ROW_LOCKS = ON \' ELSE \' ALLOW_ROW_LOCKS = OFF \' END + \',\'  + \n   CASE WHEN I.allow_page_locks = 1 THEN \' ALLOW_PAGE_LOCKS = ON \' ELSE \' ALLOW_PAGE_LOCKS = OFF \' END  + \' ) ON [\' + \n   DS.name + \' ] \'  \nFROM sys.indexes I   \n JOIN sys.tables T ON T.Object_id = I.Object_id    \n JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid   \n JOIN (SELECT * FROM (  \n    SELECT IC2.object_id , IC2.index_id ,  \n        STUFF((SELECT \' , \' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN \' DESC \' ELSE \' ASC \' END \n    FROM sys.index_columns IC1  \n    JOIN Sys.columns C   \n       ON C.object_id = IC1.object_id   \n       AND C.column_id = IC1.column_id   \n       AND IC1.is_included_column = 0  \n    WHERE IC1.object_id = IC2.object_id   \n       AND IC1.index_id = IC2.index_id   \n    GROUP BY IC1.object_id,C.name,index_id  \n    ORDER BY MAX(IC1.key_ordinal)  \n       FOR XML PATH(\'\')), 1, 2, \'\') KeyColumns   \n    FROM sys.index_columns IC2   \n    --WHERE IC2.Object_id = object_id(\'Person.Address\') --Comment for all tables  \n    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4   \n  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id  \n JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id   \n JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id   \n JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id   \n LEFT JOIN (SELECT * FROM (   \n    SELECT IC2.object_id , IC2.index_id ,   \n        STUFF((SELECT \' , \' + C.name  \n    FROM sys.index_columns IC1   \n    JOIN Sys.columns C    \n       ON C.object_id = IC1.object_id    \n       AND C.column_id = IC1.column_id    \n       AND IC1.is_included_column = 1   \n    WHERE IC1.object_id = IC2.object_id    \n       AND IC1.index_id = IC2.index_id    \n    GROUP BY IC1.object_id,C.name,index_id   \n       FOR XML PATH(\'\')), 1, 2, \'\') IncludedColumns    \n   FROM sys.index_columns IC2    \n   --WHERE IC2.Object_id = object_id(\'Person.Address\') --Comment for all tables   \n   GROUP BY IC2.object_id ,IC2.index_id) tmp1   \n   WHERE IncludedColumns IS NOT NULL ) tmp2    \nON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id   \nWHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 \nAND I.[name] = @index_name\n\nreturn @Return\n\nend\n</code></pre>\n\n<p>Sql for while:</p>\n\n<pre><code>declare @RebuildIndex Table(\n    IndexId int identity(1,1),\n    IndexName varchar(100),\n    TableSchema varchar(50),\n    TableName varchar(100),\n    Fragmentation decimal(18,2)\n)\n\n\ninsert into @RebuildIndex (IndexName,TableSchema,TableName,Fragmentation)\nSELECT \n    B.[name] as \'IndexName\', \n    Schema_Name(O.[schema_id]) as \'TableSchema\',\n    OBJECT_NAME(A.[object_id]) as \'TableName\',\n    A.[avg_fragmentation_in_percent] Fragmentation\nFROM sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,\'LIMITED\') A \nINNER JOIN sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id  \nINNER JOIN sys.objects O ON O.[object_id] = B.[object_id]  \n where B.[name] is not null and B.is_primary_key = 0 AND B.is_unique_constraint = 0 and A.[avg_fragmentation_in_percent] &gt;= 5  \n\n--select * from @RebuildIndex\n\n declare @begin int = 1\n declare @max int\n select @max = Max(IndexId) from @RebuildIndex\n declare @IndexName varchar(100), @TableSchema varchar(50), @TableName varchar(100) , @Fragmentation decimal(18,2)\n\n while @begin &lt;= @max\n begin\n\n    Select @IndexName = IndexName from @RebuildIndex where IndexId = @begin\n    select @TableSchema = TableSchema  from @RebuildIndex where IndexId = @begin\n    select @TableName = TableName  from @RebuildIndex where IndexId = @begin \n    select @Fragmentation = Fragmentation  from @RebuildIndex where IndexId = @begin \n\n    declare @sql nvarchar(max)\n    if @Fragmentation &lt; 31\n    begin\n        set @sql = \'ALTER INDEX [\'+@IndexName+\'] ON [\'+@TableSchema+\'].[\'+@TableName+\'] REORGANIZE WITH ( LOB_COMPACTION = ON )\'\n        print \'Reorganized Index \' + @IndexName + \' for \' + @TableName + \' Fragmentation was \' + convert(nvarchar(18),@Fragmentation)\n    end\n    else\n    begin\n        set @sql = (select dbo.GetIndexCreateScript(@IndexName))\n        if(@sql is not null)\n        begin\n            print \'Recreated Index \' + @IndexName + \' for \' + @TableName + \' Fragmentation was \' + convert(nvarchar(18),@Fragmentation)\n        end \n        else\n        begin\n            set @sql = \'ALTER INDEX [\'+@IndexName+\'] ON [\'+@TableSchema+\'].[\'+@TableName+\'] REBUILD PARTITION = ALL WITH (ONLINE = ON)\'\n            print \'Rebuilded Index \' + @IndexName + \' for \' + @TableName + \' Fragmentation was \' + convert(nvarchar(18),@Fragmentation)\n        end\n    end\n\n    execute(@sql)\n\n\n    set @begin = @begin+1\n\nend\n</code></pre>\n'}, {'answer_id': 35035024, 'author': 'Alex Kirilov', 'author_id': 5078466, 'author_profile': 'https://Stackoverflow.com/users/5078466', 'pm_score': 5, 'selected': False, 'text': '<p>Before considering maintenance of indexes, it is important to answer two main questions:</p>\n\n<ol>\n<li>What is the degree of fragmentation?</li>\n<li>What is the appropriate action? Reorganize or rebuild?</li>\n</ol>\n\n<p>As described in this article <a href="http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/" rel="noreferrer">http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/</a>, and to help you determine if you should perform index rebuild or index reorganization, please understand the following:</p>\n\n<ul>\n<li><p>Index reorganization is a process where the SQL Server goes through existing index, and cleans it up. Index rebuild is a heavy-duty process where index is deleted and then recreated from scratch with entirely new structure, free from all piled up fragments and empty-space pages.</p></li>\n<li><p>While index reorganization is a pure cleanup operation which leaves system state as it is without locking-out affected tables and views, the rebuild process locks affected table for the whole rebuild period, which may result in long down-times that could not be acceptable in some environments.\nWith this in mind, it is clear that the index rebuild is a process with ‘stronger’ solution, but it comes with a price – possible long locks on affected indexed tables.</p></li>\n</ul>\n\n<p>On the other side, index reorganization is a ‘lightweight’ process that will solve the fragmentation in a less effective way – since cleaned index will always be second to the new one fully made from scratch. But reorganizing index is much better from the efficiency standpoint, since it does not lock affected indexed table during the course of operation.</p>\n\n<p>The above mentioned article also explains how to reorganize and rebuild indexes using SSMS, T-SQL (to reorganize/rebuild indexes in a table) and a 3rd party tool called ApexSQL Backup.</p>\n'}]
['https://Stackoverflow.com/questions/7579', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/961/']