Compare fragmentation before and after running a defrag routine

To compare fragmentation before and after running a defrag, you can use this code.  The vwFragmentation view contains NumPages, Rows, and TotalMB so you have all you need to make your judgement.  Recall that TotalMB is more important that Rows.

Use Admin

-- 1st data point

SELECT *

INTO vwFrag20250101

FROM vwFragmentation

WHERE DB = 'MyDB'

-- 2nd data point

SELECT *

INTO vwFrag20250102

FROM vwFragmentation

WHERE DB = 'MyDB'

-- Compare fragmentation from 1st to 2nd

SELECT  f1.DB, f1.TotalMB,

        f1.PercFrag FragBefore,

        f2.PercFrag FragAfter,

        f1.ObjName,

        f1.IndexName

FROM vwFrag20250101 f1

JOIN vwFrag20250102 f2

    ON  f2.DB = f1.DB

    AND f2.ObjName = f1.ObjName

    AND f2.IndexName = f1.IndexName

    AND F2.IndexLevel = f1.IndexLevel

WHERE f1.PercFrag > 50

ORDER BY f1.TotalMB desc