DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to reindex all fragmented indexes in a database?

Execute the following Microsoft SQL Server T-SQL example scripts in SSMS Query Editor to create a reindexing stored procedure and execute the sproc to reindex all tables with at least 35% logical fragmentation:

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

------------

Related article:

SQL Server Index Maintenance Checklist

http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE