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
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 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
WHERE
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
BEGIN
--- reorganize the index.
set @execstr = 'alter index ' + rtrim(@indexName) + ' on ' + @tablename + ' REORGANIZE'
EXEC (@execstr);
FETCH NEXT FROM indexes
INTO @tablename, @frag, @indexName
END
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes
-- Delete the temporary table.
DROP TABLE #fraglist