Our content follows trusted Indexes are important schema objects of MS SQL database files that enhance the data retrieving operations on a table. In simple words, these objects function like a table of contents of the database. These indexes have B-tree structure (where the letter “B” stands for balanced), which is a multilevel structure that has root, intermediate, and leaf levels. At the leaf level, a "doubly linked list" links the pages. This link is used to scan an index.
Types of SQL Server Indexes
There are two types of indexes in SQL Server – clustered and non-clustered.
A clustered index: physically stores the data within the table, defining the logical order of table. A non-clustered index: stores data separately from the table. It has a similar B-tree structure but points to the clustered index for its data.
How Index Fragmentation Occurs in SQL Server?
With time, continuous INSERT, UPDATE, and DELETE operations can fragment the database tables. When fragmentation increases, query performance degrades, and data concurrency and locks increase. Also, the server may choose not to use a fragmented index. Indexes are also prone to corruption, which can generate integrity errors.
Common SQL Server Index corruption errors
Indexes are also prone to corruption. If the indexes get corrupted, you can receive errors. Some common errors are:
Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching.
Msg 8955, Level 16, State 1, Line 1
Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.
Msg 8951, Level 16, State 1, Line 1
In case of high fragmentation or corruption in indexes, you can reorganize or rebuild the indexes to resolve the issue. In this post, let's see how to reorganize indexes and rebuild indexes in SQL Server, and also understand more about fragmentation.
What is Index Fragmentation in MS SQL?
Fragmentation is a natural phenomenon that happens in a database over time, when rows are continuously inserted, updated, and deleted. It is defined as a condition when data pages are not stored contiguously. In a B-tree index, fragmentation usually occurs when pages, where the logical ordering (within the index, based on the key values of the index), do not match the physical ordering of the index pages.
Example: When rows are added to a table page without enough space, SQL Server automatically performs a page split. It moves some data to a new page to make room. This process changes the indexes, which directly impacts the data continuity and order. However, SQL Server updates the pointer on the both pages (original and new) to maintain the logical sorting order in the index. But it still leads to fragmentation.
Types of Index Fragmentation in SQL Server
There are two types of index fragmentation:
Logical Fragmentation - When the logical order of the pages does not match the physical order. From a performance standpoint, logical fragmentation leads to an increase in the number of physical reads.
Internal Fragmentation - When data pages in the index contain free space. Internal fragmentation leads to an increase in logical reads.
Page Density (Page Fullness) -Page density shows how full each index page is. When it’s low, it means index pages contain too much free space.
- Low page density is commonly caused by:
- Frequent page splits
- Low FILL FACTOR settings
- Heavy INSERT and UPDATE activity
Unlike external fragmentation, low page density problems are not fully resolved by index reorganization alone and may require an index rebuild.
How to check Index Fragmentation in SQL Server?
You can identify the degree of fragmentation in a specific index or all indexes defined on a table or in a database using the Dynamic Management Function (DMF) - “sys.dm_db_index_physical_stats.”
SELECT
DB_NAME(ips.database_id) AS DatabaseName,
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID('EmployeeRecords'),
NULL,
NULL,
'DETAILED'
) AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;
GO
To determine index Fragmentation by SSMS Graphical user interface, here are the steps:
- Open SSMS, go to Object Explorer, and right‑click your database to check fragmentation. Click Reports>Standard Reports>Index Physical Statistics.
- It will display show fragmentation details.
SQL Server Index Fragmentation Thresholds: When to Use REORGANIZE vs REBUILD
According to Microsoft, if the fragmentation is less than 5%, there is no need for defragmentation. If the fragmentation is between 5% and 30%, you should reorganize the indexes. If the fragmentation is greater than 30%, you need to rebuild the indexes.
After getting the fragmentation percentage, you can choose the best method to defragment the indexes. According to Microsoft, ALTER INDEX REORGANIZE is generally preferred over REBUILD unless a rebuild is specifically required because reorganize operations are lightweight, online, and consume fewer resources.
You can rebuild/reorganize indexes using the SQL commands, SSMS, and maintenance plans. Let’s discuss these in detail.
How to Reorganize Indexes in SQL Server?
Reorganizing the index improves its structure by moving the pages into a more efficient search order. It also helps the index rows to deallocate unused pages. Swap the remaining pages in small transactions until all the pages are in logical order. The result will create logically ordered pages instead of physically contiguous pages. To reorganize the indexes, you can follow the below cited steps:
1. Reorganize Indexes using T-SQL Command
You can reorganize the index using the below T-SQL command.
ALTER INDEX IX_EmployeeName
ON EmployeeRecords
REORGANIZE;
GO
In the above command, EmployeeRecords is the table, with the index - IX_EmployeeName
To reorganize ALL INDEXES, run this command:
ALTER INDEX ALL
ON EmployeeRecords
REORGANIZE;
GO
It does not update statistic automatically, so after reorganizing indexes, you have to manually do this, by following command:
UPDATE STATISTICS EmployeeRecords;
GO
2. Reorganize the Indexes using the SSMS
You can also reorganize the indexes using the SQL Server Management Studio (SSMS). Follow the below cited steps:
- Open the SSMS. In Object Explorer, expand the database that contains the table on which you want to reorganize the indexes.
- Expand the Tables folder.
- Expand the table on which you want to reorganize the indexes.
- Right-click the Indexes folder and select Reorganize All.
- In the Reorganize Indexes dialog box, verify the indexes to be reorganized. To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key.
- Select the Compact large object column data checkbox to specify that all pages that contain large object (LOB) data are also compacted.
- Click OK.
3. Reorganize Index in a Maintenance Plan
You can also reorganize the index in a maintenance plan. SQL Server offers an option to schedule a maintenance plan that periodically carries out this activity. The maintenance plan will not only defragment the index but also allow you to perform other important operations, such as statistic maintenance and database backup.
- To create a new maintenance plan, right-click on the maintenance item from the Management menu. Then, select the New Maintenance Plan option.
- In the maintenance plan, you can add various tasks. To perform the Reorganize Index Task, drag and drop the Reorganize index task to the center of the screen.
- Once you have added one, you should set it. To set up the Reorganize Index Task.Specify on which databases that operation needs to be performed.
- Choose to reorganize only the necessary databases, the user databases, or the system databases.
- Choose to Reorganize the indexes only under certain conditions, such as above a certain percentage of fragmentation or above a certain number of pages.
How to Rebuild Indexes in SQL Server Database? (3 Methods)
The process of rebuilding indexes is different in comparison to reorganizing the indexes. This process builds a new index from scratch and drops the old one. This happens regardless of the fragmentation present in the older index. It requires enough space to build a new index.
You can perform rebuilding of indexes online or offline. The terms Online and Offline indicate whether the index can be kept accessible during the rebuild operation. The Standard version of MS SQL Server supports only the Offline type, while the Enterprise and Developer versions also support the Online type. Let’s see the ways to rebuild the indexes in SQL Server.
1. Rebuild Indexes using the T-SQL Command
If the fragmentation is higher, then you can rebuild the indexes. You can use the DBCC DBREINDEX command to rebuild an index. Here’s how to execute this command:
DBCC DBREINDEX
(
table_name
[ , index_name [ , fillfactor ] ]
)
[ WITH NO_INFOMSGS ]
This command does not support spatial indexes and memory-optimized column store indexes.
DBCC DBREINDEX is deprecated in newer SQL Server versions. Microsoft recommends using ALTER INDEX ... REBUILD instead. Use ALTER INDEX to refresh your index. Example:
ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]
REBUILD;
Command to Rebuild Index Online to Avoid Downtime
ALTER INDEX [IndexName]
ON [Schema].[Table]
REBUILD WITH (ONLINE = ON);
GO
It allow you continue accessing the table during the rebuild operation. However, availability depends on the SQL Server edition and index type.
To Rebuild All indexes in database use this command:
ALTER INDEX ALL
ON TableName
REBUILD;
GO
2. Rebuild the Indexes using SSMS
You can use the graphical user interface of the SQL Server Management Studio to rebuild the indexes. Here are the steps:
- Open SSMS and go Object Explorer. Expand the database that contains the table on which you want to rebuild an index.
- Expand the Tables folder.
- Expand the table on which you want to rebuild an index.
- Expand the Indexes folder.
- Right-click on the index and select Rebuild.
- In the Rebuild Indexes dialog box, verify the indexes to be rebuilt and click OK.
- Select the Compact large object column data checkbox to specify that all pages that contain large object (LOB) data are also compacted.
- Click OK.
3. Rebuild Indexes in a Maintenance Plan
You can also rebuild the index in a maintenance plan. To do so, follow the below steps:
- First, create a new maintenance plan. For this, from the Management menu, right-click on the maintenance item and then select the New Maintenance Plan option.
- In the maintenance plan, drag and drop the Rebuild Index Task to the center of the screen.
- Now, you need to setup the rebuild task. For this:
- Specify on which databases that operation needs to be performed.
- Choose to reorganize only the necessary databases, the user databases, or the system databases.
- Choose to rebuild the indexes only under certain conditions, such as above a certain percentage of fragmentation or above a certain number of pages.
- Now, save the maintenance plan and schedule it to run at a certain frequency.
How to Repair Corrupt Indexes in SQL Server?
If indexes are corrupted and reorganizing or rebuilding did not help, follow these repair methods:
1. Use DBCC CHECKDB command:
You can try the DBCC CHECKDB command with 'REPAIR_ALLOW_DATA_LOSS' option to repair the database. Here’s how to use this command:
First, change the mode of the database to SINGLE_USER by using the below command:
Next, run the DBCC CHECKDB command as given below to repair the database:
DBCC CHECKDB (N ’Entity’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
After repair, set the database mode to MULTI_USER by executing the below command:
The above DBCC CHECKDB command can help you resolve minor corruption issues in clustered and non-clustered indexes in SQL database. But it can result in data loss.
2. Restore from Backup
If the backup file is available and the indexes in MDF/NDF file is severely corrupted then you can restore them from bak file. It is safe method then DBCC CHECKDB.
3. Use Stellar Repair for MS SQL
If the backup file is not available, you can use Stellar Repair for MS SQL - professional MS SQL repair tool. It can quickly repair database (both MDF and NDF files) with complete integrity and precision. It can recover all the objects, including clustered and non-clustered indexes, triggers, pages, stored procedures, etc., from the corrupt database. It allows you to save all the data recovered from the database to a new SQL database file and in various other formats. It can help you resolve index corruption-related errors in SQL database.
SQL Server Index Maintenance Best Practices [2026]
Here are the best practices you should follow while maintaining indexes in SQL Server:
- Use sys.dm_db_index_physical_stats before every maintenance run — don't blindly rebuild all indexes on a schedule
- Apply the fragmentation threshold rule while implementing index maintenance methods, i.e., < 5% = skip, 5–30% = reorganize, > 30% = rebuild
- Ignore maintenance on small indexes (page_count < 1,000) as it can generate more overhead than benefit
- You should prefer REORGANIZE over REBUILD as your default — it's always online, less resource-intensive.
- Set an appropriate FILL FACTOR for write-heavy tables (80–85% is a common startingpoint)
- Always update statistics after reorganizing (rebuild does it automatically)
- Schedule maintenance during low-traffic windows — or use ONLINE = ON for 24/7 production systems
- Use resumable index rebuilds (SQL Server 2022+) for large indexes that run past maintenance windows
- You can monitor the suspect pages table — index corruption often surfaces alongside page corruption
To Conclude
Reorganizing or rebuilding the indexes helps you reduce fragmentation and resolve corruption. In this article, we have explained how to check the fragmentation percentage in indexes and how to rebuild the indexes. If, while or after the defragmenting process, your database gets corrupted and returns errors related to index corruption, then you can use Stellar Repair for MS SQL . This professional tool can repair and restore corrupt indexes from the database with complete precision. The tool can repair databases created in the latest SQL Server 2025 and lower versions. It also helps to restore deleted objects.
SQL Fill factor controls how much space SQL Server leaves free in each index page during rebuild. It helps reduce page splits and fragmentation.
Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing.
7 min read
