A union of curiosity and data science

Knowledgebase and brain dump of a database engineer

Database defragmentation at x%. SQL 2k5 and 2K8



After the defragmentation of each table, I generally store the time , object name and fragmentation level in a logging table. This gives me a good baseline for object fragmentation over time.



--SQL 2K5,2K8
 -- Declare variables         
 SET NOCOUNT ON         
      DECLARE @tablename VARCHAR(128)        
      DECLARE @execstr   VARCHAR(256)         
      DECLARE @objectid INT         
      DECLARE @indexid   INT         
      DECLARE @frag      decimal         
      DECLARE @maxfrag   decimal         
      DECLARE @indexName varchar(255)        
      -- maximum fragmentation limit    
      SELECT @maxfrag = 30.0;         
 -- Create the table.         
      CREATE TABLE #fraglist(         
      ObjectName CHAR(255), ObjectId INT, IndexName CHAR(255), IndexId INT, Lvl INT,         
      CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT,         
      ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT,         
      ScanDensity decimal, BestCount INT, ActualCount INT, LogicalFrag decimal, ExtentFrag decimal);         
      --- This will change to a DMF (dynamic management function) call in the future.
      --- Currently, this statement runs many many times faster than calling the dmf.
      --- (sys.dm_db_index_physical_stats)
      INSERT INTO #fraglist      
      -- Declare the cursor for the list of indexes to be defragged.         
      DECLARE indexes CURSOR FOR 
      SELECT isnull(s.name, '') + '.' + rtrim(ltrim(ObjectName)) objectname
            , LogicalFrag
            , IndexName 
      FROM #fraglist f
      join sys.objects o on o.object_id = f.ObjectID
      join sys.schemas s on s.schema_id = o.schema_id
      LogicalFrag >= @maxfrag
      and countpages > 1000
      and nullif(indexName, '') is not null
      -- Open the cursor.         
      OPEN indexes         
      -- Loop through the indexes.         
      FETCH NEXT FROM indexes
      INTO @tablename, @frag, @indexName         
      WHILE @@FETCH_STATUS = 0         
            --- reorganize the index.
            set @execstr = 'alter index ' + rtrim(@indexName) + ' on ' + @tablename + ' REORGANIZE'
            EXEC (@execstr); 
            FETCH NEXT FROM indexes
            INTO @tablename, @frag, @indexName         
      -- Close and deallocate the cursor.         
      CLOSE indexes;         
      DEALLOCATE indexes         
 -- Delete the temporary table.         
 DROP TABLE #fraglist





Add comment