How to automate and schedule SQL Server index defragmentation

Introduction

SQL Server maintenance is not a one-time event, but rather a part of a continuous process. Apart from regular backups and integrity checks, performance improvements can be achieved with index maintenance. If done at regular intervals, it can free the server to focus on other requests rather than losing time scanning for fragmented indexes.

This article should serve as a guide as to how to automate and schedule SQL Server index defragmentation, and will cover three different ways of doing it:

  • Automate and schedule SQL Server index defragmentation using SQL Server maintenance plans
  • Automate and schedule SQL Server index defragmentation using SQL Server Agent jobs
  • Automate and schedule SQL Server index defragmentation using ApexSQL Defrag

Automate and schedule SQL Server index defragmentation using maintenance plans

Maintenance plans in SQL Server are a good way to automate some of the routine tasks. In this section will be explained how to automate and schedule SQL Server index defragmentation using maintenance plans.

To do that, first we need to open up SQL Server Management Studio.

When SSMS is opened, we need to go to Management and then right-click on Maintenance Plans and select Maintenance Plan Wizard

After the wizard opens, go to Next to go to the next page

Once there, enter the maintenance plan name, optionally enter the description, select whether to have a separate schedule for each task or single schedule for the entire plan or no schedule (for the purposes of this article, we are going to select the single schedule) and click on Change to edit the schedule

In the New Job Schedule, setup the schedule as per your individual needs.

For the schedule type, there are a few options – Start automatically when SQL Server Agent starts, Start whenever CPUs become idle, Recurring and One time. For the purpose of this article, Recurring is chosen since it allows to schedule precise time when to start the job.

The Frequency option allows choosing whether to do the job on a daily, weekly or monthly basis. In this case, Weekly is chosen since it allows to run the job on certain days of the week which can be beneficial by allowing the user to select the days with the least amount of traffic on the server.

Daily frequency allows to select a specific time of day to run the job, or a selected time span in which the job runs on an hourly basis.

Duration is used to choose on which day to start the job and optionally to specify the end date, after which the job will not run anymore

Moving on to the next page of the wizard, select the maintenance tasks. For the scope of this article, Reorganize Index and Rebuild Index tasks are chosen

The Select Maintenance Task Order page appears when there’s more than one task chosen. The order is made in this page.

Users can also make a separate maintenance plan for the reorganization of indexes as well as for the rebuilding of indexes, if needed.

On the next wizard page, Define Reorganize Index Task is where databases can be selected, as well as defining the Scan type and thresholds for the optimization in the Index Stats Options, such as fragmentation percentage, page count and the last time used

On the following wizard page, Define Rebuild Index Task, databases are selected

Free space options, the free space per page, or Fill factor, can be left as default values or changed (doing this changes the free space per page for all the indexes in the selected databases, which in some cases can be detrimental to the overall performance and fragmentation, despite the rebuild process)

Advanced options offer different options, such as sorting the results in tempdb, online index rebuild, maximum degree of parallelism among others.

Index Stats Options allows defining the thresholds and scan type

In the next wizard page, Select Report Options, it can be chosen to write a report to a text file, and/or send an email report

In the final page of the wizard the whole maintenance plan with all the tasks can be reviewed and created by pressing on the finish button

After pressing on the Finish button, the maintenance plan is being created and is presented at the end of the wizard as a series of operations, which, if successful, should look like in the image below

Automate and schedule SQL Server index defragmentation using a SQL Server Agent job

As well as maintenance plans, SQL Server Agent jobs are also a handy way to automate and schedule index defragmentation jobs in SQL Server. They are also configured from SSMS

To create a new job, right click on SQL Server Agent, select New and then Job

Next the New Job window will open. In it, enter the Job name, owner, optionally Category and Description

In the next tab, Steps, click on New to open a New Job Step window

In the New Job Step, insert the Step Name, select the type of step to be Transact-SQL script (T-SQL), select the database to defragment

Note: For SQL Server Agent job steps, only one database can be selected in a single step

In the Command: window, enter the following T-SQL script which was taken and adapted from MSDN Books Online article “Rebuild or reorganize indexes (with configuration)” :

USE <databasename>
SET NOCOUNT ON

DECLARE @reorg_frag_thresh   float		SET @reorg_frag_thresh   = 10.0
DECLARE @rebuild_frag_thresh float		SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint	        SET @fill_factor         = 80
DECLARE @report_only         bit			SET @report_only         = 1

-- added (DS) : page_count_thresh is used to check how many pages the current 
table uses
DECLARE @page_count_thresh smallint SET @page_count_thresh = 1000 -- Variables required for processing. DECLARE @objectid int DECLARE @indexid int DECLARE @partitioncount bigint DECLARE @schemaname nvarchar(130) DECLARE @objectname nvarchar(130) DECLARE @indexname nvarchar(130) DECLARE @partitionnum bigint DECLARE @partitions bigint DECLARE @frag float DECLARE @page_count int DECLARE @command nvarchar(4000) DECLARE @intentions nvarchar(4000) DECLARE @table_var TABLE( objectid int, indexid int, partitionnum int, frag float, page_count int ) -- Conditionally select tables and indexes from the -- sys.dm_db_index_physical_stats function and -- convert object and index IDs to names. INSERT INTO @table_var SELECT [object_id] AS objectid, [index_id] AS indexid, [partition_number] AS partitionnum, [avg_fragmentation_in_percent] AS frag, [page_count] AS page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE [avg_fragmentation_in_percent] > @reorg_frag_thresh AND page_count > @page_count_thresh AND index_id > 0 -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM @table_var -- Open the cursor. OPEN partitions -- Loop through the partitions. WHILE (1=1) BEGIN FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag, @page_count IF @@FETCH_STATUS < 0 BREAK SELECT @objectname = QUOTENAME(o.[name]), @schemaname = QUOTENAME(s.[name]) FROM sys.objects AS o WITH (NOLOCK) JOIN sys.schemas as s WITH (NOLOCK) ON s.[schema_id] = o.[schema_id] WHERE o.[object_id] = @objectid SELECT @indexname = QUOTENAME([name]) FROM sys.indexes WITH (NOLOCK) WHERE [object_id] = @objectid AND [index_id] = @indexid SELECT @partitioncount = count (*) FROM sys.partitions WITH (NOLOCK) WHERE [object_id] = @objectid AND [index_id] = @indexid -- Build the required statement dynamically based on options and index
stats.
SET @intentions = @schemaname + N'.' + @objectname + N'.' + @indexname + N':' + CHAR(13) + CHAR(10) SET @intentions = REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) + @intentions SET @intentions = @intentions + N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) +
CHAR(10) + N' PAGE COUNT: ' + CAST(@page_count AS nvarchar) + CHAR(13) +
CHAR(10) IF @frag < @rebuild_frag_thresh BEGIN SET @intentions = @intentions + N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10) SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE; ' + N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + N' ' + @indexname + ';' END IF @frag >= @rebuild_frag_thresh BEGIN SET @intentions = @intentions + N' OPERATION: REBUILD' + CHAR(13) + CHAR(10) SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD' END IF @partitioncount > 1 BEGIN SET @intentions = @intentions + N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) +
CHAR(10) SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)) END IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN SET @intentions = @intentions + N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10) SET @command = @command + N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')' END -- Execute determined operation, or report intentions IF @report_only = 0 BEGIN SET @intentions = @intentions + N' EXECUTING: ' + @command PRINT @intentions EXEC (@command) END ELSE BEGIN PRINT @intentions END PRINT @command END -- Close and deallocate the cursor. CLOSE partitions DEALLOCATE partitions GO

Note: Make sure to enter the database name in the first line of the script according to the environment in which the job will be used

In the advanced tab of the same window, action on success, action on failure can be set from the dropdown menus, as well as the ability to export the script to a file, log the action to table and include the step output in history

After creating the step, the New Job window should look something like this

On the next tab, Schedules, click on New… to open the New Job Schedule

In the New Job Schedule, same as when creating the schedule for maintenance plans, a detailed schedule can be set up

After creating the schedule, the New Job window should look as follows

On the Alerts tab, alerts can be setup in case of specific events

On the Notifications tab, email notifications, pager messages can be sent, an entry in the Windows Application event log can be made and the job can be deleted automatically upon success or failure

After pressing OK, the job will be created, and can afterwards be viewed in the Object Explorer under the SQL Server Agent – Jobs

Automate and schedule SQL Server index defragmentation using ApexSQL Defrag

Another way to automate and schedule SQL Server index defragmentation is by using a 3rd party tool such as ApexSQL Defrag. ApexSQL Defrag is an enterprise SQL Server tool which is used for monitoring, analyzing and managing SQL Server index defragmentation which works down to a single index level. Spanning across multiple databases and servers, ApexSQL Defrag takes care of reorganizing indexes, rebuilding online or offline, whether the defragmentation process is started manually or is run automatically through a policy

To automate and schedule SQL Server index defragmentation using ApexSQL Defrag, first, download the application from this link and install it

Note: For installation and troubleshooting guide, consult this article

After running ApexSQL Defrag, presented is the main interface of the application

Going to the policies tab, presented is the policy explorer. On the main ribbon, click on Create to start the Policy creation wizard

In the Policy creation wizard, under General tab, insert policy name, optionally a description, rebuild type (online or offline) and setup a schedule

In the same tab, by pressing on the three dots at Targets, a policy targets explorer opens where the indexes can be chosen for maintenance. As it can be seen, targets can be on a different servers, different databases, and can be selected individually. Also, filters for index page number and top % of indexes can be turned on or off and configures in the explorer itself

After selecting the targets, going to Thresholds tab of the policy creation wizard, reorganize and rebuild thresholds can be set using the sliders, limited, sampled and detailed fragmentation scan mode can be chosen, as well as setting the Resources thresholds

In the Notifications tab of the same wizard, email notifications can be set to be sent on success and/or failure of the policy.

Note: SMTP settings need to be set up from the Configuration tab in the main application window to be able to set up email notifications in the policies.

After that, our policy is created and can be run on schedule or started manually from the Policies explorer

On the Policy explorer, operations such as editing, enabling and disabling can be run. Also, policy templates can be used for faster creation of policies with the most common types already predefined in the application itself. All the user needs to do is set up the schedule and select policy targets

As well as creating policies from templates, users can also create their own templates

User created templates can easily be accessed when creating policies from the drop down menu when clicked on Create button in the Policy main ribbon which will automatically load the template settings when creating policies

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

 

June 5, 2017