Fragmentation in SQL Server – how to detect its high level and automatically fix it

Introduction

When working with databases, availability and performance are of supreme importance. The key among them is low Fragmentation in SQL Server, which allows fast query processing. That can be facilitated using indexes. Indexes are like the table of contents in a book, providing shortcuts to where the information is located. Otherwise, the book should be read from the beginning until the needed information is found. The same goes for databases. If there weren’t for indexes, for every query there should need an entire table scan.

Looking at indexes, when inserting new data, pages of indexes get filled up gradually. The Fill factor comes into play here, as its value, in percentage terms, determines how much each page will be allowed to get filled. Setting the fill factor to 0% or 100% (effectively the same thing) means that once filled, there will be no space left on that page for additional data. When another piece of data needs to get inserted into a page, which is filled, an event called “page split”, is carried to adjust for the new data. What a page split does is to split the existing filled page into two new pages which are half-filled. The new piece of data is going to be inserted in its logical place. However, SQL Server now has more pages to read, which has an impact on performance and higher usage of memory, CPU, and IO bandwidth.

Getting the right Fill factor is not easy. It depends on the type of environment. If there is a static system or read-only system, where no changes occur, then the Fill factor of 100% is perfect. However, if there are lots of transactions, inserts, updates, deletes, then a Fill factor of around 70% to 90% is preferred for best results. But, in the end, it all depends on the individual system, user preferences, and the amount of IO.

Fragmentation in SQL Server database occurs in two forms, internal and external fragmentation. Internal fragmentation means that the index is bigger than it needs to be. It’s caused by operations that leave the pages unfilled and results in records being saved on new pages instead of being saved in the empty spaces on the existing pages, leading to a bigger index and slower performance, as the server needs to read more pages.

External fragmentation in the SQL Server database means that the logical order in an index does not match the physical order on the disk. When the pages are scattered on the disk, it takes time for the heads of the magnetic hard drives to jump from one page to another, which significantly decreases the performance and overall longevity of the hardware. In some cases, random reads on a magnetic hard drive can be as much as 100 times slower than sequential reads.

When dealing with fragmented indexes, there are two options available for fixing fragmentation in SQL Server. Those are Index Reorganization and Index Rebuild. A common practice is to Reorganize when index fragmentation is between 10%-30%, and Rebuild when fragmentation gets above 30%. However, it’s up to the user to ultimately choose the percentage at which to run Reorganization and Rebuild.

There are a couple of ways to automatically fix index fragmentation in SQL Server databases:

  • Reorganize/Rebuild SQL Server database indexes using Maintenance Plans for SQL Server
  • Reorganize/Rebuild SQL Server database indexes using ApexSQL Defrag

Reorganize/Rebuild SQL Server database index using SQL Server Maintenance Plans

Microsoft SQL Server Management Studio is an environment for Microsoft SQL Server. For this purpose, an AdventureWorks database is restored into our SQL server.

After connecting to the server, expand the Management node, right-click on the Maintenance Plans node and choose the Maintenance Plan Wizard option:

Maintenance Plan Wizard in Microsoft SQL Server Management Studio

When the Maintenance Plan Wizard window opens, click the Next button:

Fragmentation in SQL Server - Maintenance Plan Wizard window

In the Select Plan Properties window, a descriptive name must be entered, and optionally a description for the maintenance plan. Whit this scenario, indexes will be reorganized daily and rebuild every third day. Additionally, the Separate schedules for each task option can be selected before the Next button is clicked:

Maintenance Plan Wizard - Select Plan Properties

In the Select Maintenance Tasks window, check the Reorganize Index and Rebuild Index options and click the Next button:

Maintenance Plan Wizard - Select Maintenance Tasks

In the Select Maintenance Task Order window, click the Next button:

Maintenance Plan Wizard - Select Maintenance Task Order

In the Define Reorganize Index Task window, from the Databases drop-down list, the databases on which the task will be performed can be checked:

Fragmentation in SQL Server - Check the databases for maintenance task

After checking the database(s), the Scan type and conditions when the index should be reorganized can be set too. After that click the Change… button to set up a schedule when to run the Reorganize Index Task:

Fragmentation in SQL Server - Define Reorganize Index Task

After defining the schedule, in the New Job Schedule window, click the OK button:

Maintenance Plan Wizard - New Job Schedule

In the Define Rebuild Index Task window, the Free space options can be defined. The Default free space per page option leaves the fill factor for every Index the same as it was. With the Change free space per page to option a set percentage can be entered, which changes all indexes to the same fill factor, which is not recommended in larger databases with different indexes.

As for the Advanced options, the Sort results in tempdb and Keep index online (if it is supported) options can be set. In this tab the Index Stats Options are present as well. When everything is set, click the Next button:

Fragmentation in SQL Server - Define Rebuild Index Task

In the Select Report Options tab, there are options for writing a report and save it to a predefined location, also this report can be sent to the email address:

Maintenance Plan Wizard - Options for saving a report of the maintenance plan action

The Complete the Wizard tab provides a list of the planned tasks. If there are additional changes to be made, the Back button should be clicked for revising the changes. Otherwise, click on the Finish button will create a maintenance plan:

Fragmentation in SQL Server - Complete the Wizard

Click the Close button in the Maintenance Plan Wizard Progress window, and that’s it, the Maintenance Plan is created:

Fragmentation in SQL Server - Maintenance Plan Wizard Progress

Maintenance Plans are a good option for fragmentation in SQL Server databases. However, unless specifically choosing the indexes to run the Plan on, it will run against all of the indexes in the selected database which is over 10% fragmented and 30% fragmented, reorganizing or rebuilding them.

Reorganize/Rebuild SQL Server database indexes using ApexSQL Defrag

ApexSQL Defrag is a 3rd party tool made specifically to automatically fix index fragmentation in SQL Server databases. This tool provides index analysis to manage index defragmentation, including rebuild and reorganize fragmented indexes. A functionality that puts this tool ahead of other tools is the policy-making feature, from the pre-defined templates or by using the custom parameters.

Creating a schedule in ApexSQL Defrag to automatically detect index fragmentation in SQL Server and reorganize/rebuild fragmented indexes can be done in a couple of simple steps.

In the Policies tab, there is an option to create a custom policy:

Fragmentation in SQL Server - Creating custom policy

The Create policy window provides fields for policy Name, Description, and Targets. SQL Server index rebuilding operation can be executed online or offline.

More on this can be found in Online vs offline SQL Server index rebuild in SQL Server article.

Click the Elipse button in the Targets field, to see and select indexes for maintenance. Here, in the Policy targets dialog, after expanding the tree view, the whole database can be selected, all of the indexes bound to a certain table, or individual indexes. After making the selection, click the OK button to continue:

ApexSQL Defrag - Policy targets window

The Thresholds tab leads users to options to select when to reorganize indexes when to rebuild indexes and to choose a Fragmentation scan mode. There are additional options in the Resources threshold area, that can greatly help users to execute the database maintenance based on their specific requirements:

Fragmentation in SQL Server - Thresholds

The Schedule tab provides policy options for Frequency and Duration (Start date, and End date):

Apex SQLDefrag - Schedule tab

A feature that is very useful for DBA’s is to send notifications to the desired email addresses. There are options for adding email addresses in the Notification tab and selecting the cases in which the notification will be sent (Succes, Failure, Canceled job, Warning, Expired job):

Fragmentation in SQL Server - Notification setup

  • Note: To add an email address, an email account should be set up first in the Configuration tab of the application

After the Finish button is clicked in the Create policy window the policy will be created and displayed in the Policies tab. The policy will now run automatically at the scheduled time. However, for running the policy on-demand there two ways, either right-click on the policy and choose the Run command or click the Run button in the Policy ribbon:

Fragmentation in SQL Server - Running Policies

After the policy is completed, ApexSQL Defrag will send notifications to the set email address, and in the Activities tab will be displayed an excellent stats overview of the index fragmentation in SQL Server, before and after the maintenance:

ApexSQL Defrag - stats overview

Conclusion

It’s important to keep in mind that there’s a SQL Server maintenance cost involved with keeping indexes up-to-date, as each one has to be revised every time there’s a table update.

ApexSQL Defrag provides all the necessary information for the DBA’s to easily create index maintenance policies, which will greatly help to reduce these maintenance costs.

Related links:

 

February 8, 2017