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
