Resolve SQL Server Index Fragmentation

Understanding SQL Server Index Fragmentation

Indexes play a critical role in the performance of SQL Server applications. Indexes are created on columns in tables or views and provide a fast way to look up data based on the values of those columns. An index is an on-disk structure that consists of keys that point to one or more columns in the table or view. Indexes are stored on disk using a B-tree structure. When you create an index on a column and then search for a row based on the values in the index, SQL Server will first find that value in the index and then use the index to quickly locate the row data in the database. If you didn’t have the index, SQL Server would have to perform a table scan to locate the row data, which would require significantly more I/O and would be much slower.

Index Fragmentation Can Hinder Performance

Indexes are a great thing for relational database performance. Fragmentation happens when the order of pages in an index doesn't match the physical order in the data file. When an index is first created, there’s no fragmentation. However, as the database is used and data is inserted, updated, and deleted, the indexes become fragmented, which can degrade performance. There are two types of index fragmentation:

Internal Fragmentation

Internal fragmentation occurs when data pages have excessive free space. As you insert data into a table, if the data is under the SQL Server’s data page size, then SQL Server will allocate one page to store that data. Otherwise, SQL Server will allocate multiple pages to store the data, and these data pages are often not full. Deletes cause internal fragmentation by leaving empty space in the data page. For example, if a table starts off with 1,000 data pages that are 100% full, there’s no index fragmentation. But after that table is used extensively, it might have 1,200 data pages that are only 80% full. As SQL Server scans the index, it needs to do 20% more work by processing 1,200 pages instead of the original 1,000. Sparsely populated data pages also increase SQL Server’s cache size, as caching happens at the page level.

External Fragmentation

External fragmentation occurs when data pages are out of order. It can occur from an insert operation or an update that makes an existing record longer and an index leaf page is full and more space is required. SQL Server then performs a page split operation where it creates a new page and moves 50% of the rows from the full page to the new page. This causes logical fragmentation because the new page typically isn’t physically contiguous to the original page. External fragmentation results in increased random I/O, where SQL Server needs to read index data from many places rather than just reading the data sequentially.

Addressing Index Fragmentation

As a table with indexes continues to experience insert, update, and delete operations, the indexes for that table will become more fragmented. Periodic maintenance is necessary to keep your SQL Server index fragmentation from impacting performance. You can fix index fragmentation by rebuilding or defragmenting the index. If the fragmentation level is low, you can defragment the index. If it’s high, then you should rebuild the index.

You can use SQL Server Management Studio (SSMS) or T-SQL to get started managing index fragmentation. To check for index fragmentation using SSMS, open Object Explorer and navigate to the index you want to check. Right click the index, click Properties, then click Fragmentation to see the fragmentation and page fullness percentages. You can also use the system function sys.dm_db_index_physical_stats to report fragmentation of data and indexes for a specified table or view.

Many companies use maintenance plans to perform regular index defragmentation or rebuilds. However, this approach often results in wasted resources and an increase in transaction log size as they typically rebuild or defragment every index in the database whether it’s needed or not. It’s better to use a more granular approach in which you only defragment or rebuild indexes when they reach a specified level of fragmentation.

SQLsentry

SolarWinds SQL Sentry

With SQL Sentry, our installed software solution, you can effectively monitor, diagnose, and optimize your entire database environment.

Key Functionality for a Performance Monitoring Tool that Supports Defragmenting SQL Server Indexes

Although you can manually check index fragmentation using SSMS or T-SQL, specialized database performance management tools can make this task a lot easier and more efficient. Some of the key index maintenance capabilities that you should look for in a tool include:

Automatic and manual multi-server index defragmentation
Customized maintenance schedules for SQL Server instances, databases, and indexes
Policy-based reorganization and rebuild operations
Setting index size thresholds and fill percentages
Setting the maximum duration of index maintenance operations
Performing concurrent defragmentation and the ability to set concurrency levels
Reporting index fragmentation levels

Make Better Decisions About Defragmentation Operations

Luckily, SQL Server fragmentation doesn’t have to take up a lot of time and waste resources. SQL Sentry Fragmentation Manager allows you to make the best decisions about when and how to defragment your indexes. Create an environment-wide schedule to automate the process of index defragmentation operations. Customize the schedule to suit your needs for specific instances, tables, databases, and respective indexes. With concurrent operations, you can perform index defragmentation faster and more productively.

Identify your most expensive operators when performing SQL query optimization operations with insightful color coding. Focus on specific resources operating costs by I/O or CPU.

Fragmentation Manager also allows you to save time when defragmenting your indexes by reducing maintenance window times and running concurrent defrag operations.

Check out this video for an overview of the Indexes tab within the SentryOne Performance Analysis Dashboard and a walk-through of how to configure Fragmentation Manager to perform automated index maintenance activities.

Resolving SQL Server Index Fragmentation