MODX Revolution PHP Cross Reference Content Management Systems

Source: /core/model/modx/processors/system/databasetable/defragment-indexes.sql - 162 lines - 4547 bytes - Text - Print

   1  /*
   2  Added a @ViewOnly variable that will allow you to run this script as a test only and review proposed actions.
   3  I also enhanced to perform either a reorg or rebuild, based on range of fragmentation value.
   4  ~Paul DeBrino : infinityrd.com
   5  
   6  --INSPECT INDEXES' FILL FACTOR VALUES:
   7  use YourDBName
   8  select * from sys.indexes order by name
   9  
  10  --VIEW THE DEFAULT FILL FACTOR OF YOUR SERVER:
  11  use YourDBname
  12  exec sp_configure 'show advanced options', 1
  13  go
  14  reconfigure
  15  go
  16  exec sp_configure 'fill factor'
  17  go
  18  
  19  */
  20  -- Specify your Database Name
  21  --USE [[+dbname]]
  22  --GO
  23  
  24  -- Declare variables
  25  SET NOCOUNT ON
  26  DECLARE @tablename VARCHAR(128)
  27  DECLARE @execstr VARCHAR(255)
  28  DECLARE @objectid INT
  29  DECLARE @indexid INT
  30  DECLARE @frag decimal
  31  DECLARE @maxreorg decimal
  32  DECLARE @maxrebuild decimal
  33  DECLARE @IdxName varchar(128)
  34  DECLARE @ViewOnly bit
  35  DECLARE @ReorgOptions varchar(255)
  36  DECLARE @RebuildOptions varchar(255)
  37  
  38  -- Set to 1 to view proposed actions, set to 0 to Execute proposed actions:
  39  SET @ViewOnly=0
  40  
  41  -- Decide on the maximum fragmentation to allow for a reorganize.
  42  -- AVAILABLE OPTIONS: http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
  43  SET @maxreorg = 20.0
  44  SET @ReorgOptions = 'LOB_COMPACTION=ON'
  45  -- Decide on the maximum fragmentation to allow for a rebuild.
  46  SET @maxrebuild = 30.0
  47  -- NOTE: only specifiy FILLFACTOR=x if x is something other than zero:
  48  SET @RebuildOptions = 'PAD_INDEX=OFF, SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON'
  49  
  50  -- Declare a cursor.
  51  DECLARE tables CURSOR FOR
  52  SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
  53  +'.'+CAST(TABLE_NAME AS VARCHAR(100))
  54  AS Table_Name
  55  FROM INFORMATION_SCHEMA.TABLES
  56  WHERE TABLE_TYPE = 'BASE TABLE'
  57  
  58  -- Create the temporary table.
  59  if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%')
  60  drop table #fraglist
  61  
  62  CREATE TABLE #fraglist (
  63  ObjectName CHAR(255),
  64  ObjectId INT,
  65  IndexName CHAR(255),
  66  IndexId INT,
  67  Lvl INT,
  68  CountPages INT,
  69  CountRows INT,
  70  MinRecSize INT,
  71  MaxRecSize INT,
  72  AvgRecSize INT,
  73  ForRecCount INT,
  74  Extents INT,
  75  ExtentSwitches INT,
  76  AvgFreeBytes INT,
  77  AvgPageDensity INT,
  78  ScanDensity decimal,
  79  BestCount INT,
  80  ActualCount INT,
  81  LogicalFrag decimal,
  82  ExtentFrag decimal)
  83  
  84  -- Open the cursor.
  85  OPEN tables
  86  
  87  -- Loop through all the tables in the database.
  88  FETCH NEXT
  89  FROM tables
  90  INTO @tablename
  91  
  92  WHILE @@FETCH_STATUS = 0
  93  BEGIN
  94  -- Do the showcontig of all indexes of the table
  95  INSERT INTO #fraglist
  96  EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
  97  WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
  98  FETCH NEXT
  99  FROM tables
 100  INTO @tablename
 101  END
 102  
 103  -- Close and deallocate the cursor.
 104  CLOSE tables
 105  DEALLOCATE tables
 106  
 107  -- Declare the cursor for the list of indexes to be defragged.
 108  DECLARE indexes CURSOR FOR
 109  SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
 110  FROM #fraglist
 111  WHERE ((LogicalFrag >= @maxreorg) OR (LogicalFrag >= @maxrebuild))
 112  AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
 113  
 114  -- Open the cursor.
 115  OPEN indexes
 116  
 117  -- Loop through the indexes.
 118  FETCH NEXT
 119  FROM indexes
 120  INTO @tablename, @objectid, @indexid, @frag, @IdxName
 121  
 122  WHILE @@FETCH_STATUS = 0
 123  BEGIN
 124  IF (@frag >= @maxrebuild)
 125  BEGIN
 126  IF (@ViewOnly=1)
 127  BEGIN
 128  PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
 129  END
 130  ELSE
 131  BEGIN
 132  PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
 133  SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'
 134  EXEC (@execstr)
 135  END
 136  END
 137  ELSE IF (@frag >= @maxreorg)
 138  BEGIN
 139  IF (@ViewOnly=1)
 140  BEGIN
 141  PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
 142  END
 143  ELSE
 144  BEGIN
 145  PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
 146  SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'
 147  EXEC (@execstr)
 148  END
 149  END
 150  
 151  FETCH NEXT
 152  FROM indexes
 153  INTO @tablename, @objectid, @indexid, @frag, @IdxName
 154  END
 155  
 156  -- Close and deallocate the cursor.
 157  CLOSE indexes
 158  DEALLOCATE indexes
 159  
 160  -- Delete the temporary table.
 161  DROP TABLE #fraglist
 162  --GO

title

Description

title

Description

title

Description

title

title

Body