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