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 vwFrag20200101
FROM vwFragmentation
WHERE DB = 'MyDB'
-- 2nd data point
SELECT *
INTO vwFrag20200102
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 vwFrag20200101 f1
JOIN vwFrag20200102 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
