SQL SERVER ADMINISTRATION: REORGANIZE OR REBUILD CLUSTERED AND NON-CLUSTERED INDEXES

In this blog entry, I would like to describe how to reorganize or rebuild indexes.

First of all, SQL server stores row or index data on 8KB sized memory Pages. For each table (no matter how big), a own page is created. If the table exceeds a certain size due to manipulation of data (e.g. new row inserts), the page will be extended („Page Split“) to a second page etc.

 

The new page will also cause the creation of a new page in the upper level with the index tree, which may also lead to a page split.

Page Spilts are time consuming, cause fragmentation and index pages aren’t phyically ordered as supposed. All this results to a decrease of performance.

Let's have a brief look on the architecture of indexes in SQL Server.

In a table without a clustered index, the data will be stored within the page unsorted („Heap“).

Indexes have a root page, which is the starting point. The tree is split between non-leaf (root and intermediate leafs) and leaf-levels.

In a clustered index, the leaf level is the actual data page. This means the data is stored here in ordered form.

The most significant difference in the index architecture between clustered and non-clustered, is that the leaf level in non-clustered indexes contain key values and not the actual data. 

In case of a heap, there is a row locator in the leaf node pointing to the correct data page.

A non-clustered index in a table that has a clustered index, will refer to the clustered index for pointing the row location.

Disadvantage here is that non-clustered indexes will have to be rebuild if the clustered index is rebuild or dropped. An advantage here is that a Reorganize of a clustered index, will mean that the non-clustered index does not have to be reorganized.

To solve the above listed issues concerning performance, indexes need to be REORGANIZEd or REBUILD.

The level of fragmentation can be analyzed with the help of system function sys.dm_db_index_physical_stats

Here is a more precise query:
SELECT sch.name  + ' ' + tab.name             AS 'Table',
idx.name                                                       AS 'Index',
stat.avg_fragmentation_in_percent,
stat.page_count,
idx.type_desc                                                AS IndexType
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS stat
INNER JOIN sys.tables  AS tab                   ON tab.object_id = stat.object_id
INNER JOIN sys.schemas AS sch               ON tab.schema_id = sch.schema_id
INNER JOIN sys.indexes AS idx                 ON idx.object_id = stat.object_id
AND stat.index_id = idx.index_id
ORDER BY stat.avg_fragmentation_in_percent DESC
If the average fragmention is between 5 and 30 %, a REORGANIZE is fine, if it's above 30% REBUILD is advised.

Following T-SQL syntax is required for REORGANIZE:

ALTER INDEX indexname

ON tablename REORGANIZE

In case of a rebuild, there is an option for a fill factor. This means the page will be filled with the percentage value defined. The rest is left open to avoid a fast fragmentation.

ALTER INDEX indexname

REBUILD WITH (FILLFACTOR = 80)

The fillfactor by default will only relate to the leaf level of the tree. If you want to also cover the other pages PAD_INDEX = ON will do the job.

ALTER INDEX indexname 
REBUILD WITH (FILLFACTOR = 80, PAD_INDEX = ON)