Defragmenting Indexes in SQL Server

When data is inserted, deleted, or updated in a SQL Server tables, the indexes which are defined for that tables are automatically updated to reflect those changes. When these indexes are modified, the information stored in them becomes fragmented, resulting in the information being scattered across the data files. When this occurs, the logical ordering and physical order data doen't match, which can lead to a deterioration of query performance.

To fix this problem, you should ReOrganize or Rebuild the indexes periodically i.e. defragmented, so the physical order of the leaf-level pages matches the logical order of the leaf nodes.This means that you should analyze your indexes.

To analyze the Indexes in SQL Server 2005 or 2008, you should use the system function called as  sys.dm_db_index_physical_stats to determine which indexes are fragmented and the extent of that fragmentation. You can use the function to analyze all the indexes in SQL Server 2005 or 2008, all indexes in a database, all indexes defined on a table, or a specific index.

The sys.dm_db_index_physical_stats function takes the following parameters:
    Database ID
    Object ID
    Index ID
    Partition number
    Mode

• Database ID: Represents the ID number of a database (Amallint value). If null is specified, the function retrieves index-related data of all databases from that SQL Server instance. If you specify null, you must also specify null for the object ID, index ID, and partition number.
• Object ID: Represents the ID number of a table or view (Int value). If null is specified, the function retrieves index-related data for all the tables and views of specificed database or SQL Server instance. If you specify null, you must also specify null for the index ID and partition number.
• Index ID: Represents the ID number of an index (int value). If null is specified, the function retrieves index-related data for all indexes defined on the specified table or view. If you specify null, you must also specify null for the partition number. Also, if the object ID refers to a heap, use 0 as the index ID.
• Partition number: Represents the partition number of an index or heap (Int value). If null is specified, the function retrieves index-related information for all partitions related to a specific object.
• Mode: The scan level used to obtain index-related information. Valid inputs include NULL, DEFAULT, or one of the following three modes:

You must specify all five parameters, even if their values are null.

The sys.dm_db_index_physical_stats function returns a number of values that provide details about the indexes you specify

• avg_fragmentation_in_percent: Percentage of the logical index that is fragmented.
• fragment_count: Number of fragments in the leaf level.
• avg_fragment_size_in_pages: Average number of pages in a leaf-level fragment.
• page_count: Number of index or data pages. etc.

Based on these index analysis, we can determine what action to take. As per Microsoft recommends that we reorganize when avg_fragmentation_in_percent  value of index is less than or equal to 30%, and rebuild the index if the value is greater than 30%.

We should still analyze the indexes regularly, and the sys.dm_db_index_physical_stats function is the best tool to use. So let’s take a look at an example of how to use the function to retrieve index-related statistics

SELECT
    object_id AS ObjectID, index_id AS IndexID, avg_fragmentation_in_percent AS PercentFragment,
    fragment_count AS TotalFrags, avg_fragment_size_in_pages AS PagesPerFrag,  page_count AS NumPages
    FROM
    sys.dm_db_index_physical_stats(DB_ID('CSCD'), NULL, NULL, NULL , 'DETAILED')
    WHERE
    avg_fragmentation_in_percent > 0

the result like below

ObjectID            IndexID      PercentFragment         otal Frags      PagesPerFrag                NumPages
76579361            1                46.8271621091195            3594             2.12659988870339           7643
76579361            1                39.2857142857143            28                1                                     28
76579361            2                60.071359065845             1865             1.65308310991957            3083
76579361            2                44.4444444444444            31                1.16129032258065            36
252579988          1                44                                    13                1.92307692307692            25
etc....


Here I used the sys.dm_db_index_physical_stats function to retrieve the four values described above, plus the object_id and index_id values. in the result you will get number of indexes, it grow quite large.
you might want to narrow down the result set by using the Object ID and other parameter values

SELECT
    b.name AS IndexName, a.avg_fragmentation_in_percent AS PercentFragment,
    a.fragment_count AS TotalFrags,a.avg_fragment_size_in_pages AS PagesPerFrag,
    a.page_count AS NumPages
    FROM
    sys.dm_db_index_physical_stats(DB_ID('CSCT'),OBJECT_ID('Data_collection'), NULL, NULL , 'DETAILED') AS a
    JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id
    WHERE a.avg_fragmentation_in_percent > 0


Result Like

IndexName                PercentFragment     TotalFrags       PagesPerFrag            NumPages
IX_01                           60.071359065845        1865               1.65308310991957        3083
IX_01                           44.4444444444444       31                  1.16129032258065        36
PK_DataEvaluation       46.8271621091195       3594               2.12659988870339        7643
PK_DataEvaluation       39.2857142857143       28                  1                                  28


Reorganizing Indexes

When we reorganize an index, SQL Server physically reorders the leaf-level pages to match the logical order of the leaf nodes. The process uses the existing pages only and does not allocate new ones, but it does compact the index pages. In addition, reorganization uses minimal resources and is automatically performed online, without blocking queries or updates. You should reorganize indexes only if they’re lightly fragmented, otherwise, you should rebuild them.

To reorganize an index, run an ALTER INDEX statement and include the keyword REORGANIZE

ALTER INDEX PK_DataEvaluation
  ON CSCT.dbo.Data_evaluation
REORGANIZE




Rebuilding Indexes

Rebuilding an index is generally more effective than reorganizing it because it drops the original index and builds a new one. As a result, the index starts clean with minimal fragmentation and the pages are compacted, and new pages are allocated as needed. In addition, you can choose to rebuild an index offline (the default) or online.

Rebuilding an index can be as simple as reorganizing one. Once again, use the ALTER INDEX statement, but specify the REBUILD keyword, rather than REORGANIZE,



ALTER INDEX IX_01
  ON CSCT.dbo.Data_evaluation
REBUILD


When you rebuild an index, you can include a WITH clause after the REBUILD keyword. In the WITH clause, you can specify one or more options that define how the index will be rebuilt

ALTER INDEX IX_01
  ON CSCT.dbo.Data_evaluation
REBUILD WITH (
  FILLFACTOR = 70,
  ONLINE = ON
)

In this case, the FILLFACTOR option is set to 70, which determines the percentage of how much the leaf level of each index page will be filled during index creation or alteration. As a result, the leaf level will be filled to only 70%. The ONLINE option is set to ON, which means that the StoreContact table and IX_StoreContact_ContactTypeID index will be available for queries and data modification during the index operation.

Another method you can use to rebuild an index is the CREATE INDEX statement. To use this statement, you must create an index by the same name as the original and include the DROP_EXISTING option in the WITH clause, as shown in the following example:

CREATE UNIQUE NONCLUSTERED IX_01
ON CSCT.dbo.Data_evalaution
WITH (
DROP_EXISTING = ON,
FILLFACTOR = 70,
ONLINE = ON
) ON [PRIMARY]

In this case, I’m rebuilding the AK_StoreContact_rowguid index, which is a unique, nonclustered index. Notice that the DROP_EXISTING option is set to ON, which means that the original index will be dropped and a new one created