About Me

My photo
Colorado
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.

Friday, October 4, 2013

This object combines info from vwTable and vwIndexPhysicalStats.  

This view shows all of the information necessary for determining if you should REBUILD (formerly DBCC Reindex) or REORGANIZE (formerly DBCC IndexDefrag) your indexes.  

USE Admin
IF Object_ID('dbo.vwIndexPhysicalStatsTableSize') Is Not Null
      DROP VIEW dbo.vwIndexPhysicalStatsTableSize
go

create VIEW dbo.vwIndexPhysicalStatsTableSize
AS
/*    DATE        AUTHOR            REMARKS
      4/6/13            PPaiva            Initial creation.
     
      SELECT *
      FROM vwIndexPhysicalStatsTableSize
      WHERE DB = 'MyDB'
        AND PercFrag > 20

      SELECT *
      FROM vwIndexPhysicalStatsTableSize
      WHERE DB = 'MyDB'
      ORDER BY 1, 2, 3, 4, 5

      -- Cannot rebuild
      SELECT *
      FROM vwIndexPhysicalStatsTableSize
      WHERE DB = 'MyDB'
        AND (AllowPgLocks = 0 OR IsDisabled = 1)

     
*/
SELECT  s.Server,
            s.DB,
            s.SchemaName,
            s.ObjName,
            s.IndexName,
            s.PercFrag,
            s.TotalFrags,
            s.PagesPerFrag,
            dbo.udfFormatInteger(s.NumPages) NumPagesFMT,

            t.RowsFmt,
            t.DataMB,
            t.IndexMB IndexMBTotal,
            t.FileGroup,
            t.NumPartitions,
            t.NumCols,
           
            s.PartNum,
            s.IndexType,
            s.AllocDesc,
            s.IsUniq,
            s.IsPK,
            s.IsUniqCon,
            s.IndexDepth,
            s.IndexLevel,
            s.AllowPgLocks,
            s.IsDisabled,
            s.NumPages,
            t.Rows,
            s.object_id,
            s.database_id,
            s.ViewCreateDate
FROM vwIndexPhysicalStats s
LEFT JOIN vwTable t
      ON t.DB = s.DB
      AND t.object_id = s.object_id