A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Database defragmentation at x%. SQL 2k

 

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 2K

 -- 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);         

    

      INSERT INTO #fraglist      

      EXEC ('DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');

 

       

      -- Declare the cursor for the list of indexes to be defragged.         

      DECLARE indexes CURSOR FOR         

      SELECT rtrim(ltrim(ObjectName)) objectname

            , ObjectId

            , IndexId

            , LogicalFrag

            , IndexName         

      FROM #fraglist    

      WHERE         

      LogicalFrag >= @maxfrag

      and countpages > 1000

 

         

      -- Open the cursor.         

      OPEN indexes         

           

      -- Loop through the indexes.         

      FETCH NEXT FROM indexes

      INTO @tablename, @objectid, @indexid, @frag, @indexName         

           

      WHILE @@FETCH_STATUS = 0         

      BEGIN         

            --- defragment the table.

            SET @execstr = 'DBCC INDEXDEFRAG ('+db_name()+', ' + RTRIM(@objectid) + ', ''' + RTRIM(@IndexName) + ''')'  

            EXEC (@execstr); 

 

            FETCH NEXT FROM indexes

            INTO @tablename, @objectid, @indexid, @frag, @indexName         

      END         

           

      -- Close and deallocate the cursor.         

      CLOSE indexes;         

      DEALLOCATE indexes         

           

 -- Delete the temporary table.         

 DROP TABLE #fraglist

Add comment