SQL Server Index Maintenance for Enterprise Environments

 SQL indexes allow you to improve the performance of queries. However, if you do not maintain the indexes on a regular basis, the database will become slow. Implementing indexes for Enterprise environments requires some maintenance work. 

In this article, we will talk about index maintenance in detail, the need for index maintenance in enterprise environments, and also mention a script that will help you carry out index maintenance in an automated way.

Index Maintenance

When you have a table with new indexes, the index works fine at the beginning. However, in the long run, when you insert and remove data, the index becomes a little less efficient. When you update, insert and delete data, you generate empty space on data pages. This is called fragmentation. Fragmentation is used to measure the state of the index. When fragmentation occurs, you need to maintain the index. 

What is Fill Factor?

Fill factor is a setting for records in SQL Server. You can set the fill factor value to determine the space percentage on each leaf-level page to be utilized. For example, if you set a 90% fill factor when reconstructing a grouped list, the SQL Server will leave 10% of each leaf-level page unfilled. 

How Fill Factor Destroys Performance? 

Having a lot of void space on your information pages is terrible for execution. Your information is more fanned out. So, you most likely need to add more pages to memory. That is not good. More pages must be read for each query that performs a table scan or leaf-level scan on a non-clustered index. In some cases, a level may be added to an index’s B-tree structure, since there will be more pages at the data level and possibly more pages at each index level. 

Increased index size, reduces the index’s space efficiency because you cannot tune the fill factor value at the page level. Page splits with skewed data distribution occur frequently, even when there is available reserved space. 

How to Maintain an Index in SQL Server?

Depending on the fragmentation percentage, you may need to reorganize or rebuild the indexes. For example, if the fragmentation is between 15-30%, you can reorganize the index. However, if the fragmentation is higher than 30%, it is recommended to rebuild the index.

Need for Index Maintenance for Enterprise Environments

In a big enterprise, you need to automate the process to maintain the database, including the indexes. There are several indexes in different tables and all of them need to be maintained. 

To do so, we will present a script from Ola Hallengren. This script will allow you to easily rebuild or reorganize the indexes.

Implementing Index Maintenance Scripts from Ola Hallengren

To carry out the SQL Server Maintenance Solution according to your support plan set, utilize the scripts from Ola Hallengren's and open it in SSMS. The script includes several tables and procedures to maintain the index. Run the script to create the stored procedure. Then, you can use the IndexOptimize stored procedure like this:

EXECUTE dbo.IndexOptimize

 @Databases = SPECIFY_YOUR_DATABASE_HERE',
@FragmentationLow = NULL, -- If the fragmentation is low, do not do anything
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', -- If the fragmentation is medium, reorganize the index
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', --If the fragmentation is high,
 @Resumable = 'Y'

Basically, this script allows to reorganize or rebuild indexes using the stored procedure, named dbo.IndexOptimize. 

What to do if the Index is Damaged? 

If your index is damaged or the database gets corrupted, you can use SQL repair software to repair the data. The software scans and repairs the MDF and NDF files and recovers all the components, such as indexes, triggers, keys, rules, etc. It also recovers erased records from the SQL Server database. In addition, it supports the recovery of XML indexes and data types, sparse columns, column set property, and file stream data types.