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/19      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