How to automate SQL Server defragmentation using policies

Introduction

Apart from numerous factors, poor index maintenance can be a reason for decreased SQL Server performance. If a database contains tables with numerous entries, that get updated frequently, it is most likely that high index fragmentation will occur. For smaller indexes, high fragmentation does not necessarily degrade the performance of the queries that are run on a table. But for the larger tables, with indexes that consist of 1000 pages and more, fragmentation could cause noticeable performance issues. Luckily, performing index maintenance tasks on a regular basis can eliminate the risk of degrading performance significantly. The most effective ways for treating index fragmentation are reorganize and rebuild index operations.

For smaller databases, index maintenance tasks can be run manually, when the need arises. Detailed instructions for running reorganize and rebuild jobs manually can be found in the article: Why, when and how to rebuild and reorganize SQL Server indexes. But most larger and high traffic databases require index maintenance on a regular basis: weekly, or even daily. For these use cases, it is wise to configure a policy that would run automatically, on a schedule set by the DBA.

This article shows three solutions for creating fully functional SQL Server defragmentation policy:

1. Configuring the policy as SQL Server Agent job – this requires some skill with querying and some basic knowledge of T-SQL. Although a bit complex, this option is the most customizable. The SQL Server agent service needs to be installed and running
2. Using a maintenance plan in SQL Server Management Studio – this option is a bit more user-friendly but does not allow the user to defragment a specific index. Defragmentation is allowed only on the table level. This option also requires SQL Server Agent to be installed on the server
3. Using ApexSQL Defrag policies – ApexSQL Defrag is simple, yet powerful 3^rd party solution for index fragmentation management in SQL Server. All operations are defined and deployed in graphical user interface and do not require the use of T-SQL or SQL Server Agent

Detecting SQL Server index fragmentation

Before we apply any of the listed solutions, it is necessary to run index analysis first, in order to detect highly fragmented indexes. Running a reorganize or rebuild job on each database, schema or table index might sound like the easiest solution, but is not advised, since it can be a quite time consuming and resource intensive task. The best way to check index fragmentation in SQL Server is to use the built-in function sys.dm_db_index_physical_stats. This function returns size and fragmentation information for all indexes on a server, all indexes in a database, all indexes in a table, or just a single index, depending on provided parameters. Querying the raw function with basic parameters should yield all necessary information for the analysis, but the results will be quite unorganized. Therefore, we use the modified query:

--Script 1: Detecting index fragmentation

SELECT dbschemas.[name] AS 'Schema',
dbtables.[name] AS 'Table',
dbindexes.[name] AS 'Index',
indexstats.avg_fragmentation_in_percent AS 'Frag (%)',
indexstats.page_count AS 'Page count'

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent DESC

Running the query on AdventureWorks2014 database yields the following results:

Schema, table, name, fragmentation percentage and page count is displayed for each index in the database. Results are sorted by fragmentation percentage descending, so we could easily isolate indexes with highest fragmentation.

Depending on the fragmentation percentage value, fragmentation may be recognized as:

1. High fragmentation – fragmentation percentage values are above 30%. Highly fragmented indexes usually require rebuild operation
2. Medium fragmentation – for the values between 10% and 30%. The best method to correct medium fragmentation is to use reorganize operation
3. Low fragmentation – for the values below 10%. Indexes with low fragmentation do not require defragmentation, since they have minimal impact on the server performance

As soon as analysis is completed, we can write the defragmentation script.

Writing a defragmentation script

To write a custom script, decide which indexes to include in it, depending on the results from the previous query. Generally, indexes with fragmentation levels between 10% and 30% need to be reorganized, while those with higher fragmentation need to be rebuilt. Another important factor when choosing which indexes to include in a script is index size. Small indexes can be included in the script, but defragmenting them does not affect server performance significantly. In general, indexes smaller than 1000 pages are rarely included in defragmentation jobs.

To reorganize all indexes in a table or just specific index, use the following statements:

--Script 2: Reorganize script

--2.1 Reorganize single index

ALTER INDEX Index_name ON Table_name 
REORGANIZE ;   

--2.2 Reorganize all indexes in the table

ALTER INDEX ALL ON Table_name
REORGANIZE ;

Rebuild script has the same syntax, but uses REBUILD statement instead:

--Script 3: Rebuild script

--2.1 Rebuild single index

ALTER INDEX Index_name ON Table_name 
REBUILD ;   

--2.2 Rebuild all indexes in the table

ALTER INDEX ALL ON Table_name
REBUILD ;

Writing reorganize and rebuild statements for each index, especially for the large databases can be a tiresome job. Therefore, we decided to use a dynamic script that automatically checks for the index fragmentation and index page size, and applies reorganize or rebuild operation depending on the results.

To use the script effectively, it is necessary to set a few threshold variables first:

1. @reorg_frag_thresh – Threshold for reorganizing an index in %. Suggested value for the variable is 10 (all indexes with fragmentation between 10% and 30% will be reorganized)
2. @rebuild_frag_thresh – Threshold for rebuilding an index in %. Suggested value for the variable is 30 (all indexes with fragmentation of 30% and above will be rebuilt)
3. @fill_factor – Speciffies the fill factor value for the rebuild operations. Default value is 0 (100% fill factor). It is strongly reccomended not to change this value, since it can result in growth of rebuilt indexes if value is set anywhere between 1 and 99
4. @page_count_thresh – Page count threshold. All indexes equal or larger than set number of pages will be processed

-- Script 4: Automatically analyze and defragment indexes

-- Set variables
-- *********************************************************************************************
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 = 0
DECLARE @report_only         bit  SET @report_only = 0
DECLARE @page_count_thresh   smallint SET @page_count_thresh = 1
-- *********************************************************************************************
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
                        )
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 partitions CURSOR FOR
    SELECT * FROM @table_var 
OPEN 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    
    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
    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 partitions
DEALLOCATE partitions
GO

Set defragmentation policies with SQL Server agent job

Before starting with the job configuration, make sure that SQL Server Agent is installed and running. To do this, open the SQL Server Management Studio, and find SQL Server Agent at the bottom of the Object Explorer. Right click on the agent, and click on the Start button in the context menu. In case that agent is running already, skip this step.

To create SQL Server agent that will defragment specified indexes automatically, perform the following steps:

1. Expand SQL Server Agent in Object explorer, right click on Jobs, and select New Job…:

   

2. In General tab, specify the name and description for the job. Make sure to thick the Enabled checkbox. Proceed to the Steps tab:

   

3. Clicking on New… button in Steps tab opens the form for the first job step configuration. Provide any name for the step, and leave all other values as default. In the command window, paste the script created in previous chapter, and click OK:

   

4. In Schedules tab, in New job window click New button to create the schedule for the job. Set the schedule type, frequency and daily frequency. Since REBUILD statements lock the tables during the defragmentation process, it is best to set the schedule to time when servers experience the least amount of traffic. In this example, schedule is set to run each day at 2:00 AM. Click OK to save the schedule:

   

5. If needed, set Alerts and Notifications in respective tabs. In Targets tab, specify the targets for the job. To be able to target multiple servers, it is necessary to either create Central Management Server and Server groups or Multiserver environment. Click OK to finish job configuration:

   

6. To run the selected job immediately, expand SQL Server Agent and Jobs in Object Explorer, right click on created job, and select Start Job at Step. Since our job has only one step, it will start executing automatically:

   

Set defragmentation policy with maintenance plans

To create and run defragmentation policies with maintenance plans, it is necessary to perform the following steps in SQL Server management studio:

1. Expand the Management node in Object Explorer, right click on Maintenance Plans, and select New Maintenance Plan… from the context menu. Specify the name for the maintenance plan and click OK:

   

2. Double click on Subplan_1:

   

3. In Subplan Properties, set the name and description for the subplan, and click on schedule icon:

   

4. Set the schedule for the subplan. When completed, click OK in both New Job Schedule and Subplan Properties windows:

   

5. Drag and drop Rebuild Index Task and Reorganize Index Task from the Toolbox to the clear area in Defragmentation plan design. If there is no Toolbox window visible on the screen, turn it on in the View tab of SSMS, or simply click Ctrl+Alt+X to activate it. Make sure to connect the green arrow from Reorganize Index Task with the Rebuild index task window:

   

6. Double click on Reorganize Index Task:

   

7. In opened window, select the database for the task from the Database(s) drop down menu:

   

8. From the Object drop down menu, select Table option. If multiple databases got selected in previous step, this option will be disabled, and the job will reorganize all indexes in the selected databases. All tables for the specified database are listed in the Selection drop down menu. See the results of index fragmentation analysis, and select the tables that contained indexes with fragmentation levels ranging from 10% to 30%. Click OK to save changes for Reorganize Index Task:

   

9. Double click on Rebuild Index Task. As in steps 7 and 8, select the database and the tables to add to the task. There is no need to change any settings in Free space options and Advanced options:

   

10. Right click on the heading of Defragmentation plan design, and choose to Save Selected Items:

    

11. The maintenance plan is now ready, and will run according to the set schedule. But should the need arise, it can be run immediately. To run the created plan right away, expand Management and Maintenance Plans nodes in Object Explorer. Right click on the created maintenance plan, and select Execute:

    

12. As soon as the plan gets executed, the success message is displayed:

    

Set defragmentation policies in SQL Server with ApexSQL Defrag

Setting defragmentation policies with ApexSQL Defrag is easy and straightforward. There is no need to run separate index fragmentation analysis, to use complex queries, or to memorize which indexes need to be defragmented. ApexSQL Defrag policies analize all selected objects first (databases, tables or single indexes). Depending on the results and set thresholds, it automatically runs reorganize or rebuild operations on selected objects. Furthermore, all created policies can be enabled or disabled at any time.

To create the policy in ApexSQL Defrag, perform the following steps:

1. To be able to manage a server with ApexSQL Defrag, add it to its server list first. Open ApexSQL Defrag, go to Home tab, and click on the Add button in the Servers group. In the Connect to SQL server window, select a server from the dropdown menu. Specify authentication type, username and password for the selected server. The server will appear in the server list if provided credentials are valid:

   

2. To create a new policy, navigate to the Policies tab, click the Create button and select the Custom command:

   

3. Set the name, description and targets for the new policy. Choose to Rebuild your policy Offline or Online. Offline rebuild is selected by default:

   

4. In the Targets box, click browse (…) button. Select the objects that need to be included in the policy. There is no need to worry about the current fragmentation of selected object since all selected objects get analyzed automatically on each new policy run. Click the OK button to confirm the selection:

   

5. Click on the Thresholds tab. Index fragmentation thresholds for rebuild and reorganize operations can be set manually on the two sliders. Default value for reorganize is set between 10% and 30%. All indexes that have fragmentation value above 30% will be rebuilt by default:

   

6. The final result of index fragmentation analysis depends on the selected scan mode. Choose between limited, sampled and detailed scan mode.

   • Limited mode – this is the fastest mode that scans the smallest number of pages. In the limited mode, only the pages above leaf level of an index get scanned
   • Sampled mode – only 1% of all pages in an index or a heap get scanned
   • Detailed mode – all pages are scanned and included in statistics. It is the slowest mode and takes the most resources when compared with other modes, but it returns the most accurate statistics:



7. In the Resource thresholds section, set the CPU load, available memory, transaction log usage and hard disk usage thresholds. Set thresholds for active job count and active transactions, or configure when to retry resource check, after how many attempts to exit job as failed and when to cancel the job run. The policy will stop automatically if any of the thresholds values are reached:

   

8. In the Schedule tab, schedule for the policy is set. Under the Frequency area, choose policy to be executed Once, Daily, Weekly or Monthly. Set Daily frequency and Duration (validity period). Under the Summary section, the info about chosen schedule parameters is presented:

   

9. Few more choices regarding the policy settings are presented in the Advanced tab. Choose to exclude indexes equal or smaller than set number of pages. Same applies for indexes equal or larger than set number of pages. There is also an option to include set percentage of all policy targets. Under the Execution area, set operation execution order by Fragmentation Index size or Pages, and sort indexes by descending or ascending order. Choose to limit the number of CPU core used and delay policy execution for specified time if chosen targets are currently locked:

   

10. Under the T-SQL tab choose to add some T-SQL script and set it to be executed before and/or after policy execution. Note that if indexes targeted by the policy reside on multiple SQL server instances, scripts will be executed against each instance.

11. If needed, set the email notification in the Notification tab. Choose to receive notifications on job failure, job success, cancelled job, warning and expired job. To add email recipients to the list, click on the Add button, and type in the email address. When done, click the OK button to save the changes:

    

The policy is now available in the policy list. It will run automatically based on the schedule set in step 8. There are several operations on the created policies that are available in ApexSQL Defrag:

1. If the policy needs to be run right away, right click on the policy in the list, and select the Run command from the context menu, or simply check desired policy and then click the Run button in ribbon bar. The policy will execute immediately. The result of the last run is available as soon as policy completes:

   

2. The selected policy can also be enabled or disabled from the context menu. An enabled policy runs depending on a set schedule. A disabled policy will not execute until it is enabled again
3. If any parameters need to be changed for a policy, it is best to use the Edit command from the context menu
4. Finally, if the policy is no longer needed, it can be deleted either by using the command from the context menu, or by selecting it, and click on the Delete button in the ribbon