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.
- More about this topic can be found in the Notes – SQL Server Index Fragmentation, Types, and Solutions article
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.
- More about this topic can be found in the Optimize index maintenance to improve query performance and reduce resource consumption article
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:
When the Maintenance Plan Wizard window opens, click the Next button:
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:
In the Select Maintenance Tasks window, check the Reorganize Index and Rebuild Index options and click the Next button:
In the Select Maintenance Task Order window, click the Next button:
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:
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:
After defining the schedule, in the New Job Schedule window, click the OK button:
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:
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:
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:
Click the Close button in the Maintenance Plan Wizard Progress window, and that’s it, the Maintenance Plan is created:
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:
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:
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:
The Schedule tab provides policy options for Frequency and Duration (Start date, and End date):
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):
- 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:
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:
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:
- Why, when and how to rebuild and reorganize SQL Server indexes
- Reorganize and Rebuild Indexes
- SQL Server Index Fragmentation, Types and Solutions
February 8, 2017
