About Me

My photo
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Wednesday, January 1, 2020

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
INTO vwFrag20200101
FROM vwFragmentation

-- 2nd data point
INTO vwFrag20200102
FROM vwFragmentation

-- Compare fragmentation from 1st to 2nd
SELECT  f1.DB, f1.TotalMB,
        f1.PercFrag FragBefore,
        f2.PercFrag FragAfter,
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