Quickly rebuilding the indexes of your DMS database

The easiest way to rebuild the indexes of your DMS database is done in the following way:

SQL Script
  1. SET NOCOUNT ON
  2. GO
  3. –Set the fillfactor
  4. DECLARE @FillFactor TINYINT
  5. SELECT @FillFactor=80
  6. DECLARE @StartTime DATETIME
  7. SELECT @StartTime=GETDATE()
  8. if object_id('tempdb..#TablesToRebuildIndex') is not null
  9. begin
  10. drop table #TablesToRebuildIndex
  11. end
  12. DECLARE @NumTables VARCHAR(20)
  13. SELECT
  14. s.[Name] AS SchemaName,
  15. t.[name] AS TableName,
  16. SUM(p.rows) AS RowsInTable
  17. INTO #TablesToRebuildIndex
  18. FROM
  19. sys.schemas s
  20. LEFT JOIN sys.tables t
  21. ON  s.schema_id = t.schema_id
  22. LEFT JOIN sys.partitions p
  23. ON  t.object_id = p.object_id
  24. LEFT JOIN sys.allocation_units a
  25. ON  p.partition_id = a.container_id
  26. WHERE
  27. p.index_id IN ( 0, 1 ) — 0 heap table , 1 table with clustered index
  28. AND p.rows IS NOT NULL
  29. AND a.type = 1  — row-data only , not LOB
  30. GROUP BY
  31. s.[Name],
  32. t.[name]
  33. SELECT @NumTables=@@ROWCOUNT
  34. DECLARE RebuildIndex CURSOR FOR
  35. SELECT
  36. ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
  37. ttus.SchemaName,
  38. ttus.TableName,
  39. ttus.RowsInTable
  40. FROM
  41. #TablesToRebuildIndex AS ttus
  42. ORDER BY
  43. ttus.RowsInTable
  44. OPEN RebuildIndex
  45. DECLARE @TableNumber VARCHAR(20)
  46. DECLARE @SchemaName NVARCHAR(128)
  47. DECLARE @tableName NVARCHAR(128)
  48. DECLARE @RowsInTable VARCHAR(20)
  49. DECLARE @Statement NVARCHAR(300)
  50. DECLARE @Status NVARCHAR(300)
  51. FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
  52. WHILE ( @@FETCH_STATUS = 0 )
  53. BEGIN
  54. SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
  55. RAISERROR (@Status, 0, 1) WITH NOWAIT  –RAISERROR used to immediately output status
  56. SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
  57. EXEC sp_executesql @Statement
  58. FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
  59. END
  60. CLOSE RebuildIndex
  61. DEALLOCATE RebuildIndex
  62. drop table #TablesToRebuildIndex
  63. Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'
  64. GO

This script is particular usable if you have a huge database since it optimizes the small ones first.

Example output:

Code Snippet
  1. Table 22 of 37: Rebuilding indexes on dbo.ReferringSites (40474 rows)
  2. Table 23 of 37: Rebuilding indexes on dbo.Locations (131276 rows)
  3. Table 24 of 37: Rebuilding indexes on dbo.Keywords (267301 rows)
  4. Table 25 of 37: Rebuilding indexes on dbo.UserAgents (337697 rows)
  5. Table 26 of 37: Rebuilding indexes on dbo.GeoIps (1573434 rows)
  6. Table 27 of 37: Rebuilding indexes on dbo.Cache_TrafficByDay (1755931 rows)
  7. Table 28 of 37: Rebuilding indexes on dbo.VisitorTags (1985036 rows)
  8. Table 29 of 37: Rebuilding indexes on dbo.AutomationStates (1987782 rows)
  9. Table 30 of 37: Rebuilding indexes on dbo.Profiles (3315763 rows)

Enjoy!