How to automate SQL Server defragmentation using policies


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 3rd 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 speciffic index, use the following statements:

--Script 2: Reorganize script

--2.1 Reorganize single index

ALTER INDEX Index_name ON Table_name 

--2.2 Reorganize all indexes in the table


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 

--2.2 Rebuild all indexes in the table


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
-- *********************************************************************************************

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
    [object_id]                    AS objectid,
    [index_id]                     AS indexid,
    [partition_number]             AS partitionnum,
    [avg_fragmentation_in_percent] AS frag,
    [page_count]		  AS page_count
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    [avg_fragmentation_in_percent] > @reorg_frag_thresh 
	page_count > @page_count_thresh
    index_id > 0	
    SELECT * FROM @table_var 
OPEN partitions 
        FROM partitions
        INTO @objectid, @indexid, @partitionnum, @frag, @page_count 
        @objectname = QUOTENAME(o.[name]),
        @schemaname = QUOTENAME(s.[name])
        sys.objects AS o WITH (NOLOCK)
        JOIN sys.schemas AS s WITH (NOLOCK)
        ON s.[schema_id] = o.[schema_id]
        o.[object_id] = @objectid 
        @indexname = QUOTENAME([name])
        sys.indexes WITH (NOLOCK)
        [object_id] = @objectid AND
        [index_id] = @indexid 
        @partitioncount = count (*)
        sys.partitions WITH (NOLOCK)
        [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) +
    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 + ';'
    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'
    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))
    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) + ')'
    IF @report_only = 0 BEGIN
        SET @intentions = @intentions + N' EXECUTING: ' + @command
        PRINT @intentions	    
        EXEC (@command)
        PRINT @intentions
        PRINT @command
CLOSE partitions
DEALLOCATE partitions

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 automaticaly.

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 CTR+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 Add button in Servers group. In Connection window, select a server from the drop down 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, click on Policies button above the taskbar, and select Create

  3. Set the name, description and schedule for the new policy

  4. In 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 OK to confirm the selection.

  5. Click on 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.

    • Sample 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 Resources threshold, set the CPU load or memory usage thresholds. The policy will stop automatically if any of the thresholds values are reached.

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

The policy is now available in the policy list. It will run automatically based on the schedule set in step 3. 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 Run from the context menu. 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 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 clicking on delete button in the ribbon.

Related links:

sys.dm_db_index_physical_stats (Transact-SQL)
Reorganize and Rebuild Indexes
What is causing database slowdowns?


March 9, 2017