Maintain the indexes is vital to keep a database up to speed with optimal performance. To rebuild an index, use the command to alter and rebuild. Technically speaking, the query rebuilding the index delete it and then creates it again. This operation reorders the index rows in adjacent pages and the index has no more fragmentation. But how to rebuild a SQL Server index?
How to rebuild an existing SQL index to optimize query performance ?
Let’s consider the index from the first article of the index series. It manages the performance of the sales queries on the year and month. Use the following SQL code to rebuild the index with default options.
ALTER INDEX indexYearMonth ON [dbo].[SALES] REBUILD;
To check the index fragmentation of your database use this SQL query:
select * from sys.dm_db_index_physical_stats (db_id(), null, null, null, null);
Typically:
- No maintenance operation if the index fragmentation in under 10%.
- If it’s between 10 and 30% then a reorganize operation is enough.
- An index with a fragmentation percentage above 30 % needs to be rebuilt.
The article shows how to rebuild a SQL Server index. The alter index official documentation explains in depth the features and possibilities of the index rebuild but also the advantages and drawbacks.
Some FAQ about SQL Server indexes rebuild
Index fragmentation determines the need to rebuild indexes. But how check the index fragmentation? Just select the system function sys.dm_db_index_physical_stats and check the average fragmentation in percent column. Typically rebuild a database index if the fragmentation percentage is higher than 30%.
Let’s ask this question in order to understand the impacts of the indexes structure on the database design. Disabling indexes before an important inserts to avoid performance issues. An index cannot be enabled, it’s simply rebuild. I.e. the data is physically moved on the disk.
