Reindex all fragmented indexes in a database


USE AdventureWorks;

GO

— Reindex database – reindex all tables script

— Reindex fragmented indexes – rebuild all fragmented indexes

— DBREINDEX database – REBUILD all database indexes

CREATE PROCEDURE uspDBREINDEXFragmentedTables @FragmentationLimit DECIMAL

AS

BEGIN

CREATE TABLE #IndexFragmentation (

  ObjectName     CHAR(255),

  ObjectId       INT,

  IndexName      CHAR(255),

  IndexId        INT,

  Lvl            INT,

  CountPages     INT,

  CountRows      INT,

  MinRecSize     INT,

  MaxRecSize     INT,

  AvgRecSize     INT,

  ForRecCount    INT,

  Extents        INT,

  ExtentSwitches INT,

  AvgFreeBytes   INT,

  AvgPageDensity INT,

  ScanDensity    DECIMAL,

  BestCount      INT,

  ActualCount    INT,

  LogicalFrag    DECIMAL,

  ExtentFrag     DECIMAL)

INSERT #IndexFragmentation

EXEC( ‘DBCC SHOWCONTIG WITH TABLERESULTS , ALL_INDEXES’)

DELETE #IndexFragmentation

WHERE  left(ObjectName,3) = ‘sys’

UPDATE #IndexFragmentation

SET    ObjectName = s.name + ‘.’ + o.name

FROM   #IndexFragmentation i

       JOIN sys.objects o

         ON i.ObjectID = o.object_id

       JOIN sys.schemas s

         ON o.schema_id = s.schema_id

DECLARE  @Table      SYSNAME,

         @DynamicSQL NVARCHAR(1024)

DECLARE  @objectid INT,

         @indexid  INT

DECLARE  @Fragmentation    DECIMAL

DECLARE curIndexFrag CURSOR  FOR

SELECT   ObjectName,

         LogicalFrag = max(LogicalFrag)

FROM     #IndexFragmentation

WHERE    LogicalFrag >= @FragmentationLimit

         AND indexid != 0

         AND indexid != 255

GROUP BY ObjectName

OPEN curIndexFrag

FETCH NEXT FROM curIndexFrag

INTO @Table,

     @Fragmentation

WHILE @@FETCH_STATUS = 0

  BEGIN

    SELECT @DynamicSQL = ‘DBCC DBREINDEX (”’ + RTRIM(@Table) + ”’, ””, 70)’

    PRINT @DynamicSQL

    — Partial result:

    — DBCC DBREINDEX (‘Sales.SpecialOfferProduct’, ”, 70)

    EXEC sp_executesql @DynamicSQL

    FETCH NEXT FROM curIndexFrag

    INTO @Table,

         @Fragmentation

  END

CLOSE curIndexFrag

DEALLOCATE curIndexFrag

DROP TABLE #IndexFragmentation

END

GO

— Execute database reindexing (INDEX REBUILD) stored procedure

— Reindex indexes with 35% logical fragmentation or higher

EXEC uspDBREINDEXFragmentedTables 35.0

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: