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      




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


      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         


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



      -- Close and deallocate the cursor.         

      CLOSE indexes;         

      DEALLOCATE indexes         


 -- Delete the temporary table.         

 DROP TABLE #fraglist

Add comment