This article explains how to create customized policies for index defragmentation jobs for SQL Server
Introduction
When creating indexes, database administrators should look for the best settings to ensure minimal performance impact and degradation. However, over time, indexes will get fragmented, which can severely impact server performance.
Regular index maintenance is important in these cases as it restores the performance to previous levels. Performing index maintenance on a regular schedule, however, can be very time consuming and frustrating for the database administrator.
ApexSQL Defrag offers easy to use scheduling and policies to enable worry-free index maintenance, more so when faced with small maintenance windows or even when encountered with environments which must be online at all times.
Online index rebuilds – what are they and how do they work
Online index rebuilds are crucial for environments which have to be online and available at all times, such as e-commerce websites. Online index rebuilds make it possible to perform index maintenance while maintaining the database without interruption. It essentially enables multiple users to update and query the data in the index while it is being rebuilt. This is opposed to offline index rebuilds, where the data definition language (DDL) operations performed offline acquire and hold exclusive locks on the underlying data and indexes associated with that data, which prevent any modification and query to the underlying data as long as the index operation is in progress.
The way online index rebuild works is by creating source and target structures. The existing index is considered as the source structure. During the rebuild, there are a couple of phases in the lifecycle of the rebuild both on the source and target structures, those being Preparation, Build and Final.
- On the source structure, when starting the online index rebuild, the first phase, Preparation, a new index is created and set to write-only. That new index is considered a target structure.
- After that, the source structure enters the Build phase, in which the data gets scanned, sorted, then merged and inserted to the target structure in bulk load operations. Any user operations made in that time, such as insert, update or delete operations are applied to both the source and target structures.
- After the Build phase comes the Final phase, where the system metadata is updated to replace the source with the target structure. After that, source structure gets dropped if that is required.
On the target structure, there are also three phases, Preparation, Build and Final.
- In the Preparation phase of the target structure is the process of creating the new index and setting it to write-only.
- In the Build phase, the data gets inserted from the source structure, as well as any user modifications.
- In the Final phase, index metadata gets updated and the index is set to have a read/write status. After that, any new queries use the new index
Some support exceptions exist. Clustered indexes containing large object (LOB) data types: image, ntext, text, must be rebuilt offline. Also, local temp tables indexes must be rebuilt offline. This restriction, though, does not apply on global temp tables.
In SQL Server 2017 online index rebuilds, which can be resumed after an interruption, such as unexpected failure, introduce a PAUSE command or database failover. See Alter Index on Microsoft Docs for more information on RESUMABLE parameters of an online index rebuild.
There also need to be some consideration for the disk space needed for such an operation, as it creates one more index for each index being rebuild and fills it with the same data. Even though online index rebuilds permit user update activity at the same time as the rebuild, the operation itself will take longer if the user update activity is very heavy. In most cases, online index rebuilds are slower than the equivalent offline index rebuild, regardless what the concurrent update activity on the index is.
Also, large-scale index rebuilds, whether they are performed offline or online, will generate large data loads that can, in the result, fill the transaction log quickly. In order to make sure that the index rebuild can be rolled back should there be a need for it, transaction log can’t be truncated until the rebuild is finished, however, it can be backed up during the rebuild, and it must have enough space to store the index rebuild transactions and any user activity transactions performed while the rebuild was in progress
While available for all environments, online index rebuilds are mostly recommended for environments which, despite the noticeable performance impact when performing a rebuild online versus offline, enable a high level of database availability. For environments where there are maintenance windows, or being offline periods are an option, for example in periods of lower traffic, offline index rebuilds are preferred since they are generally faster and less resource intensive compared to online rebuilds.
Setting custom policies
ApexSQL Defrag has a list of predefined policies, as well as the ability to create your own, customized policies for defragmentation jobs which you can then use to create index defragmentation jobs with ease just by selecting the indexes you need to maintain
First, you need to have ApexSQL Defrag installed and configured, which can be done by following this article.
To start, go to Policies tab on the main application window and click on Templates
After clicking on Templates, we are presented with a window displaying a couple of default templates, which cannot be edited nor deleted. They represent some of the most common practices in index maintenance. They are differentiated by the type of index rebuild, whether it’s offline index rebuild or online index rebuild.
Note: Online index rebuilds are supported starting from SQL Server 2005 only in Enterprise edition. From version SQL Server 2008 online index rebuilds are available in Enterprise, Developer and Evaluation editions
After pressing the Create button, we are presented with a Create policy template wizard
Under General, we can find the Name and Description of the policy.
Under Index fragmentation thresholds, we can select the type of rebuild between offline and online.
Following that, we have the sliders where we select on the percentage of fragmentation on which the index will be reorganized, percentage when the index will be rebuilt, and the lower threshold percentage below which the indexes won’t be rebuilt nor reorganized
After that, we can select the Fragmentation scan mode between Limited, Sampled and Detailed
A limited mode is the fastest as it scans the least number of pages. It only scans the pages above the leaf level. Sampled mode scans and returns a 1% sample of all the pages in the index. Detailed mode scans all pages of an index and returns the most accurate statistics. The modes get slower when moving from Limited towards Detailed as more work is performed in each subsequent mode
Under Index targets thresholds, we can enable or disable couple of options in our template. The first two options, Include indexes larger than: and Exclude indexes larger than: are pretty self-explanatory, giving users the option to target only indexes which are larger than a set number of index pages, and smaller than a set number of index pages. The third option, Include first percentage of indexes, selects the only preset number of indexes which fit in the set top percentage of the policy targets. The targets are sorted by fragmentation in a descending order
Under Resource thresholds, we can apply CPU load option, which checks if the load on the CPU is less than the specified and in case it is, then the job will run as normal. Page density ensures the job will run only if the page density for selected indexes is lower than specified. Memory usage works similar like CPU load, the job will run if there is more than specified RAM memory available. Hard disk usage ensures the job will run only if there is more than specified storage space available
After pressing OK, the policy template is created and can be seen in the list
Creating customized policies in ApexSQL Defrag
Now that we have created a policy template which suits our maintenance needs, we can proceed to create a defragmentation policy
Go to Policies tab and click on the Create button, hover over From template and in the following menu choose our previously created policy template
In the Policy wizard, we enter the name of the policy, Description is already filled with information from the policy template. On Targets, click the three dots to the right and in the following Policy targets window select the indexes you want maintenance done on
Similarly, in the Thresholds tab of the wizard, we can see all the options are preset with the ones we have set up in the template we created earlier
In the Schedule tab we can schedule the policy. In Frequency group, choose policy to be executed Once, Daily, Weekly or Monthly. We can also set Daily frequency and Duration (validity period). In Summary area schedule settings preview is displayed.
After creating the policy, we can see it in the Policies activity tab
Another option when creating customized policies is the ability to save the policy we have been creating to a template from the Save as template button in the Policy wizard itself
We are presented with the familiar template creation wizard where we need to enter the policy template name and check other options listed in it and click on OK
After that, we can select that template at any time when creating new policies
September 18, 2017
