CreateVwIndexPhysicalStats

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

As always, the database-centric views presented in this blog are written to UNION all the databases in the instance, allowing you to do maintenance on the whole server instance rather than on one database at a time.

USE Admin

IF Object_ID('dbo.CreateVwIndexPhysicalStats') Is Not Null

      DROP PROC dbo.CreateVwIndexPhysicalStats

go

CREATE PROC dbo.CreateVwIndexPhysicalStats

      @Mode varchar(20) = 'Limited',

      @ShowSql bit = 0,

      @ShowSysObjects bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      3/3/13            PPaiva            Intial creation.

     

      DESCRIPTION

            Creates a view in the Admin database to see

                  fragmentation stats for all objects in all databases

                  on this server.

            System objects are excluded by default;

                  use @ShowSysObjects = 1 to show them.

            Coded to use Convert(varchar, ServerProperty('ServerName'))

                  rather than @@ServerName since the latter can be erroneous.

      USAGE

            Exec Admin..CreateVwIndexPhysicalStats

            Exec Admin..CreateVwIndexPhysicalStats 'Limited'

            Exec Admin..CreateVwIndexPhysicalStats 'Limited', 1

            Exec Admin..CreateVwIndexPhysicalStats 'Limited', 1, 1

           

            Exec Admin..CreateVwIndexPhysicalStats 'Sampled'

            Exec Admin..CreateVwIndexPhysicalStats 'Sampled', 1

            Exec Admin..CreateVwIndexPhysicalStats 'Sampled', 1, 1

           

            Exec Admin..CreateVwIndexPhysicalStats 'Detailed'

            Exec Admin..CreateVwIndexPhysicalStats 'Detailed', 1

            Exec Admin..CreateVwIndexPhysicalStats 'Detailed', 1, 1

           

      DEBUG

            SELECT *

            FROM vwIndexPhysicalStats

            WHERE DB = 'Admin'           

            ORDER BY 1, 2, 3, 4

            -- Cannot rebuild

            SELECT *

            FROM vwIndexPhysicalStats

            WHERE DB = 'Admin'           

              AND (AllowPgLocks = 0 OR IsDisabled = 1)

            ORDER BY 1, 2, 3, 4

            SELECT *

            FROM sys.databases

*/

SET NOCOUNT ON

DECLARE @s varchar(max),

            @DB varchar(100),

            @MaxDB varchar(100),

            @i int,

            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)

SET @s = 'IF Object_ID(''dbo.vwIndexPhysicalStats'') Is Not Null

      DROP VIEW dbo.vwIndexPhysicalStats'

IF @ShowSql = 1

      Print @s

Exec(@s)

     

Print ''

SET @s = 'CREATE VIEW vwIndexPhysicalStats

AS

/*    DATE        AUTHOR            REMARKS

      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Initial creation.

      DESCRIPTION

            Provides a list of physical stats of all indexes in all databases

                  on this instance.

            This view is auto-generated via executing

                  Admin.dbo.CreateVwIndexPhysicalStats. 

            If a new database is added this view won''t show it unless

                  you run the refresh code below. 

            If a database is deleted this view will malfunction unless

                  you run the refresh code below. 

                 

      To REFRESH VIEW when new databases are added:

            Exec Admin.dbo.CreateVwIndexPhysicalStats

           

      -- Sample of view      

      SELECT TOP 100 *

      FROM Admin.dbo.vwIndexPhysicalStats

      ORDER BY PercFrag desc

*/

'

-- Will refer to these system databases more than once,

-- so load into table for easy reference.

CREATE TABLE #SysDB(

      Name varchar(30) NOT NULL

      )

INSERT INTO #SysDB VALUES ('master')

INSERT INTO #SysDB VALUES ('tempdb')

INSERT INTO #SysDB VALUES ('model')

INSERT INTO #SysDB VALUES ('msdb')

INSERT INTO #SysDB VALUES ('Admin')

INSERT INTO #SysDB VALUES ('distribution')

SELECT name

INTO #DBs

FROM sys.databases

WHERE state_desc = 'online'

IF @ShowSysObjects = 0

      DELETE #DBs

      FROM #DBs d

      JOIN #SysDB s

            ON s.Name = d.name

SELECT  @MaxDB = Max(Name),

            @DB = '',

            @i = 0

FROM #DBs

DECLARE @IsSystemDB char(1)

WHILE @DB < @MaxDB

      BEGIN

            SET @i = @i + 1

            SELECT @DB = Min(Name)

            FROM #DBs

            WHERE Name > @DB

           

            IF @DB In (SELECT Name FROM #SysDB)

                  SET @IsSystemDB = '1'

            ELSE

                  SET @IsSystemDB = '0'

            SET @s = @s + '

SELECT  '

            SET @s = @s + 'Convert(varchar, ServerProperty(''ServerName'')) as Server,

            ''' + @DB + ''' as DB,

            s.name COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,

            o.Name COLLATE SQL_Latin1_General_CP1_CI_AS ObjName,

            i.name COLLATE SQL_Latin1_General_CP1_CI_AS IndexName,

            Convert(numeric(5,1), dmv.avg_fragmentation_in_percent) AS PercFrag,

            dmv.fragment_count AS TotalFrags,

            Convert(numeric(5,1), dmv.avg_fragment_size_in_pages) AS PagesPerFrag,

            dmv.page_count AS NumPages,        

            dmv.partition_number PartNum,

            dmv.index_type_desc IndexType,

            dmv.alloc_unit_type_desc AllocDesc,

            i.is_unique IsUniq,

            i.is_primary_key IsPK,

            i.is_unique_constraint IsUniqCon,

            dmv.index_depth IndexDepth,

            dmv.index_level IndexLevel,

            i.is_disabled IsDisabled,

            i.allow_page_locks AllowPgLocks,

            i.allow_row_locks AllowRowLocks,

            dmv.object_id,

            dmv.database_id,

            ''' + @sNow + ''' ViewCreateDate

FROM sys.dm_db_index_physical_stats(db_id(''' + @DB + '''), Null, Null, Null, ''' + @Mode + ''') dmv

JOIN [' + @DB + '].sys.indexes i

      ON  i.object_id = dmv.object_id

      AND i.index_id = dmv.index_id

JOIN [' + @DB + '].sys.objects o

      ON o.object_id = dmv.object_id

JOIN [' + @DB + '].sys.schemas s

      ON s.schema_id = o.schema_id

'

                                   

            IF @DB <> @MaxDB

                  SET @s = @s + '    UNION ALL '

                 

      END

IF @ShowSql = 1

      BEGIN

            Print @s

      END

Exec (@s)