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 fragmented indexes in a database?

Execute the following T-SQL example script in Microsoft SQL Server Management Studio Query Editor to reindex all tables in the Northwind database with over 35% logical fragmentation.


-- SQL Server dbreindex fragmented indexes - index rebuild script
-- SQL Server REBUILD indexes - rebuild all indexes
USE Northwind;
GO
-- Create temporary table to hold meta data information about indexes
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'
 
-- SQL cursor
-- SQL dynamic SQL
-- SQL while loop
DECLARE  @Table      SYSNAME,
         @DynamicSQL VARCHAR(512)
 
DECLARE  @objectid INT,
         @indexid  INT
 
DECLARE  @Fragmentation    DECIMAL,
         @MaxFragmentation DECIMAL
 
SET @MaxFragmentation = 35.0
 
DECLARE curIndexFrag CURSOR  FOR
SELECT   ObjectName,
         LogicalFrag = max(LogicalFrag)
FROM     #IndexFragmentation
WHERE    LogicalFrag >= @MaxFragmentation
         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 (' + QUOTENAME(RTRIM(@Table)) + ', '''', 80)'
    
    PRINT @DynamicSQL
    
    EXEC( @DynamicSQL)
    
    FETCH NEXT FROM curIndexFrag
    INTO @Table,
         @Fragmentation
  END
 
CLOSE curIndexFrag
 
DEALLOCATE curIndexFrag
GO
/* Messages
 
DBCC DBREINDEX ([Order Details], '', 80)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
DBCC DBREINDEX ([Orders], '', 80)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
*/
-- Cleanup
DROP TABLE #IndexFragmentation

GO

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

-- Index rebuild with SQLMAINT - SQL Server 2000 - 100% FILLFACTOR

exec xp_sqlmaint '-D pubs -RebldIdx 100'

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

Related link:

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

 

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