Index Defragmentation

Index Defragmentation

In flashback

In my previous article, I am trying to discuss related to Index architecture, how clustered index works and the fragmentation of the index.
If you needed any references, you can find it from

Related to Clustered Index
Index fragmentation
Introductions

In this article I am trying to discuss related to the Index defragmentation. I have learned several article related to it and collects and gather the facts related to it.

Fragmentation of the Index can be de-fragmented by two ways; it depends on size of the table and the level of the defragmentation.

  1. Reorganize Index
  2. Rebuild Index
Reorganize Index

It does not take much more system resources. That means it can be done when the user access the table objects where the index exists. This process reorganizes the leaf nodes of the index physically to match it with logical order. If the physical order matches the logical order of the index it increases the performance.  

To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause. This statement replaces the DBCC INDEXDEFRAG statement of the previous version of SQL Server.


To reorganize a single partition of a partitioned index, use the PARTITION clause of ALTER INDEX.

Reorganizing an index defragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance.

The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.

Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.

The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates.

Reorganize an index when the index is not heavily fragmented. See the previous table for fragmentation guidelines. However, if the index is heavily fragmented, you will achieve better results by rebuilding the index.

Besides reorganizing one or more indexes, large object data types (LOBs) that are contained in the clustered index or underlying table are compacted by default when an index is reorganized.


The data types image, text, ntext,  varchar(max),  nvarchar(max),  varbinary(max), and xml are large object data types.  Compacting this data can cause better disk space use.


Reorganizing a specified clustered index will compact all LOB columns that are contained in the leaf level (data rows) of the clustered index.


Reorganizing a non-clustered index will compact all LOB columns that are non-key (included) columns in the index.


When ALL is specified, all indexes associated with the specified table or views are reorganized and all LOB columns associated with the clustered index, underlying table, or non-clustered index with included columns are compacted.


The LOB_COMPACTION clause is ignored if LOB columns are not present.

Rebuild Index

Rebuild index means the dropping an existing index of the table and create a new index. The older index is gone with the drop and in the new index the logical order matches the physical order of the index and improves the performance.

In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages and allocating new pages as needed. This can improve disk performance by reducing the number of page reads required to obtain the requested data.

To rebuilds clustered and non-clustered indexes ALTER INDEX with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement of previous version of the SQL Server and the CREATE INDEX with the DROP_EXISTING clause.

If we rebuilding is a cluster index, then our tables will be unavailable to the user during the rebuild process and if it is a non-clustered index only a shared lock will be placed on the table so users will be able to access the table, but would not be allowed to do modifications on it.
SQL Server 2005 the online option for index rebuilds is only available in Enterprise edition while in SQL Server 2008 it is available in Enterprise, Developer, and Evaluation editions.
When we are creating an index online, it does not drop the existing index rather it keeps the original index to be used by the users and creates an index parallel to it. During this process only the source index will be available for use by the users. Once the index rebuild operation is complete, the original index will be dropped and the new one will be updated and available for read and write operations.

Reorganize Index Example

— Base Table Defination

CREATE TABLE emp_record
       (empID      INT          IDENTITY(1,1) NOT NULL,
        empNAME    VARCHAR(50)  NOT NULL,
        empDEMP    CHAR(1),
        CONSTRAINT PK_emp_record PRIMARY KEY CLUSTERED(empID)
       )
— Reorganize Index

ALTER INDEX PK_emp_record ON emp_record REORGANIZE
— Reorganize All Index

ALTER INDEX ALL ON emp_record REORGANIZE

Rebuild Index Example

— Rebuild Index

ALTER INDEX PK_emp_record ON emp_record REBUILD

— Rebuild Index with option specified

ALTER INDEX ALL ON emp_record
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)

— Rebuild Index online

ALTER INDEX PK_emp_record ON emp_record REBUILD WITH (ONLINE = ON)

ALTER INDEX ALL ON emp_record REBUILD WITH (ONLINE = ON)

Summarize the Differences

  1. Index Rebuild drops the existing Index and Recreates the index from scratch.

  2. Index Reorganize physically reorganizes the leaf nodes of the index.
  3. Rebuild the Index when an index is over 30% fragmented.

  4. Reorganize the Index when an index is between 10% and 30% fragmented.

  5. If fragmentation is below 10%, no action required.
    Rebuilding takes more server resources and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions.
Defrag Index Stored Procedure

This stored procedure defrags the index accordingly. This is prototype only. You can modify it …

IF OBJECT_ID(‘proc_DEFRAG’) IS NOT NULL
   BEGIN
       DROP PROCEDURE proc_DEFRAG
   END
GO

CREATE PROCEDURE proc_DEFRAG
       (
         @p_DBName   VARCHAR(50) = NULL,
         @p_SchemaNm VARCHAR(50) = NULL,
         @p_TBLName  VARCHAR(50) = NULL,
         @p_IndxName VARCHAR(50) = NULL
       )
AS
  DECLARE @v_FrgReOrgLmt   DECIMAL(10,2),
          @v_FrgReBuildLmt DECIMAL(10,2),
          @v_CurrentFreg   DECIMAL(10,2),
          @v_Flag          DECIMAL(1),
          @v_fillfactor    INT,
          @v_Str           VARCHAR(MAX),
          @v_Message       VARCHAR(MAX)
BEGIN
  SET @v_FrgReOrgLmt   = 10.00
  SET @v_FrgReBuildLmt = 30.00
  SET @v_CurrentFreg   = 0.00
  SET @v_Flag = 0
  SET @v_fillfactor = 80
 
  IF ISNULL(@p_DBName, )<>
         AND ISNULL(@p_TBLName, )<>
               AND ISNULL(@p_IndxName, )<>
                     AND ISNULL(@p_SchemaNm, )<>
     BEGIN
         SELECT @v_CurrentFreg = avg_fragmentation_in_percent
         FROM   sys.dm_db_index_physical_stats(DB_ID(@p_DBName), NULL, NULL, NULL , NULL)a
                INNER JOIN sys.tables b WITH (nolock) ON a.OBJECT_ID = b.OBJECT_ID
                INNER JOIN sys.indexes c WITH (nolock) ON a.OBJECT_ID = c.OBJECT_ID
                                                          AND a.index_id = c.index_id
                WHERE  b.name = @p_TBLName
                       AND c.name = @p_IndxName
          –Reorganizing the index
          IF ((@v_CurrentFreg >= @v_FrgReOrgLmt) 
                   AND (@v_CurrentFreg <= @v_FrgReBuildLmt))
             BEGIN
                SET @v_Str = ‘ALTER INDEX ‘ + @p_IndxName +
                           ‘ ON [‘ + RTRIM(LTRIM(@p_SchemaNm)) +
                           ‘].[‘ + RTRIM(LTRIM(@p_TBLName)) +
                           ‘] REORGANIZE’ 
                SET @v_Flag = 1
                SET @v_Message = ‘Rebuild Index with REORGANIZE’
                GOTO WAYOUT
             END
          –Rebuilding the index
          IF (@v_CurrentFreg>@v_FrgReBuildLmt)
             BEGIN
               SET @v_Str = ‘ALTER INDEX ‘ + @p_IndxName +
                   ‘ ON [‘ + RTRIM(LTRIM(@p_SchemaNm)) +
                   ‘].[‘ + RTRIM(LTRIM(@p_TBLName)) +
                   ‘] REBUILD WITH (FILLFACTOR = ‘ +
                   CONVERT(VARCHAR(3),@v_fillfactor) +
                   ‘, STATISTICS_NORECOMPUTE = OFF)’
               SET @v_Flag = 1
               SET @v_Message = ‘Rebuild Index with REBGUILD’  
             END
WAYOUT:
          IF @v_Flag = 1   
             BEGIN
                   EXEC (@v_Str)
             END
         ELSE
            BEGIN
                   SET @v_Message = ‘Parameter is NOT properly Supplied’  
            END
         RAISERROR(@v_Message, 0, 1) WITH NOWAIT
         RAISERROR(, 0, 1) WITH NOWAIT  
     END
END
GO

Hope you like it.
Posted by: MR. JOYDEEP DAS