Brad’s Sure Guide to SQL Server Maintenance Plans- P25: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 8 Reorganize Index Task After the Reorganize Task runs it produces a text report similar to the following Microsoft R Server Maintenance Utility Unicode Version Report was generated on HAWAII. Maintenance Plan MaintenancePlan Duration 00 00 15 Status Succeeded. Details Reorganize Index HAWAII Reorganize index on Local server connection Databases AdventureWorks Object Tables and views Compact large objects Task start 2009-07-30T15 05 51. Task end 2009-07-30T15 06 06. Success Command USE AdventureWorks GO ALTER INDEX PK_AWBuildVersion_SystemInformationID ON dbo . AWBuildVersion REORGANIZE WITH LOB_COMPACTION ON GO USE AdventureWorks GO ALTER INDEX PK_DatabaseLog_DatabaseLogID ON dbo . DatabaseLog REORGANIZE WITH LOB_COMPACTION ON GO While the above is an abbreviated report yours will show the ALTER INDEX command run for every index in every table in your selected databases. If there are any problems or error messages you will see them here also. Reorganize Versus Rebuild Now that you have a little background about Rebuild and Reorganize 1 think it s time for a more comprehensive summary of the pros and cons of using the Reorganize Index task versus using the Rebuild Index task. With this information you will be better able to make an educated decision on which option is right for your particular circumstances. 121 Chapter 8 Reorganize Index Task Reorganize Index Task Rebuild Index Task Removing empty space and logical fragmentation Performs a less thorough index defragmentation than Rebuild Index. If an index does not have any fragmentation then it is not reorganized saving resources. Virtually all wasted free space and logical fragmentation is removed. All indexes are rebuilt from scratch whether they need it or not. Performance impact Does not require long blocking locks. An online task that allows users to access the database during the task. Requires potentially long blocking locks that prevent users from accessing the indexes being rebuilt. A .
