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

vwFragmentation

This is a comprehensive view for fragmentation, together with size in bytes (from vwTable).  Keep in mind that when assessing fragmentation, we are concerned with larger tables in bytes, not necessarily larger tables in rows.  This is because we are concerned with disk I/O.  Although a tall skinny table with millions of rows may appear as a candidate to scrutinize, it is possible for a table with far fewer rows to incur more disk I/O if it is a wide table.  

This view works well for instances that do not have too many databases (say under 20).  If there are 50-100+ databases, this becomes slower.  For those, I have another solution which will be posted next month.

IF OBJECT_ID('dbo.vwFragmentation') Is Not Null

       DROP VIEW dbo.vwFragmentation

GO

CREATE VIEW dbo.vwFragmentation

AS

/*     DATE          AUTHOR        REMARKS

       12/17/24      PPaiva        Initial creation.

      

       SELECT TOP 1000 *

       FROM vwFragmentation

       WHERE DB = 'MyDB'

         AND TotalMB > 100

       ORDER BY PercFrag desc

      

*/

SELECT  i.Instance,

              i.DB,

              t.Rows,

              t.TotalMB,

              PercFrag,

              i.SchemaName,

              ObjName,

              TotalFrags,

              PagesPerFrag,

              NumPages,

              IndexName,

              PartNum,

              IndexType,

              AllocDesc,

              IsUniq,

              IsPK,

              IsUniqCon,

              IndexDepth,

              IndexLevel,

              IsDisabled,

              AllowPgLocks,

              AllowRowLocks,

              i.object_id,

              database_id,

              i.ViewCreateDate,

              Convert(varchar(16), GetDate(), 120) InsertDate

FROM dbo.vwIndexPhysicalStats i

JOIN vwTable t

       ON t.Instance = i.Instance

       AND t.DB = i.DB

       AND t.TableName = i.ObjName

GO