Create and run SQL Server instance index policy templates (4255150)

This article explains the perks of having SQL Server instance index defragmentation policy templates, the easiness of setting them up, and the convenience of its later usage.

SQL index software is already packed with plenty of predefined index defragmentation policy templates but it still offers some tweaks and customizations in this department. Since those predefined policy templates cannot be further customized, having an option to create a user-created custom template along with the desired settings and fragmentation thresholds is a neat and welcomed feature to obtain.

Creating an index policy template

To create SQL Server index policy template and setting it up for later use, head over to the Policies tab and click the Templates button from the ribbon bar:

Once the Policy templates window is opened, presented is the list of predefined SQL Server instance policy templates as well as custom-created ones. Also, the following buttons are available here:

• Create – creates new index policy template
• Edit – edit selected template from the list
• Remove – deletes selected template from the list

Quick tip: The predefined policy templates cannot be edited or deleted and when attempted the appropriate message is displayed:

To initialize the creation of a custom index defragmentation policy template, click the Create button. This action will open the Create policy template window, where a set of index policy options needs to be configured:

In the General section of the window, provide a name and optionally a description for the new SQL Server instance policy template. For instance, the following template name requests tweak related to percentage level threshold above which the index rebuild will be initiated:

Additionally, settings related to the type of fragmentation scan mode that should be conducted, index target, and resource thresholds are within the reach for easy tweaks. Click the Finish button to create the new policy template and close the Create policy template window:

Where to find and how to use SQL Server instance index policy templates

Once created, new policy templates are listed in the Policy templates window where can be further edited or deleted. Click the Close button to exit the window:

Both, predefined and newly created custom index policy templates can be easily found and accessed under the From template option of the Create policy drop-down menu of the Policies tab:

Here, select the Rebuild above 50% index defragmentation policy template. This action opens the Create policy window where all previously configured policy template settings are already selected. Choose the index policy target, i.e. a SQL Server database(s) to run defragmentation operation against:

Under the Schedule tab, set date and time, as well as the policy frequency reoccurrence and duration:

Lastly, under the Notification tab provide an email address to receive a notification regarding the index policies outcome results. Once an email is provided, click the Finish button to create and schedule an index policy:

Quick tip: To learn more about the index policies, consult the Create SQL Server index defragmentation policies and Schedule and automate SQL Server instance index policies articles