By David Wiseman (Administrator)Created 11 Feb 2011, Modified 01 Mar 2011
My Rating:
Not Rated

Defrag Indexes For Database

Language:  T-SQL


SQL Server 7 No SQL Server 2000 No
SQL Server 2005 Yes SQL Server 2008 Yes

This script will defrag all the indexes in a database that are above a specified fragmentation threshold. You can control the level of fragmentation that triggers a REORGANIZE and the level of fragmentation that triggers a REBUILD. You can also choose to rebuild your indexes online and sort in tempdb.


What is Index Fragmentation?

Index fragmentation is categorised in two types internal and external.  Fragmentation also occurs at the file system level, but this is separate from index fragmentation which is the main focus of this article.  The types of fragmentation are discussed in the following sections.

Internal Fragmentation

The level of internal fragmentation in an index is referring to the "fullness" of the index pages.  In some cases it might be desirable to have a certain amount of internal fragmentation as it can improve the performance of inserts/updates. This is because there is room available in the index pages so that new data can be inserted without incurring the performance penalty of a page split.  A page split is what occurs when there is no room on an index page to insert new data and SQL server moves approximately half the data to a new page to make room for the new data.
An index that has heavy internal fragmentation will take up additional disk space and incur additional I/O when data is read from the index. Indexes with low internal fragmentation are more compact and more efficient when reading data from the index, but might incur some additional overhead for inserts and updates. 
You can set a fill factor to introduce internal fragmentation into your indexes, which you might want to do for certain tables that receive large volumes of insert/update operations.  You wouldn't want to lower the fill factor for indexes on tables that receive relatively few inserts/updates or indexes where new data is logically inserted at the end of an index.  It's quite common to have tables with an identity column and these are often used as the table's primary key and clustered index.  In this case, new data is always inserted at the end of the index so introducing a lower fill factor is only going to have a negative impact on read performance and increase the storage requirements for the index.  You might also have a table that is using a UNIQUEIDENTIFIER as a primary key/clustered index with new values been generate by the NEWID() function.  In this case, new data is inserted randomly throughout the index causing a large number of page splits and poor performance with too high a fill factor.  Note: If you use the UNIQUEIDENTIFIER data type, a better option is to use the NEWSEQUENTIALID() function so that it behaves similar to an identity column where data is inserted at the end of the index. 
The fill factor value is specified as a percentage between 1 and 100, with a default server-wide setting of 0.  A fill factor of 0 is the same as a fill factor of 100, where leaf level pages are filled to capacity.  A fill factor value of 80 would leave approximately 20% space free at the leaf level of the index, potentially reducing the amount of page splits and improving insert/update performance.  At the same time this would also increase the size of the index by about 20% and reduce read performance. The fill factor setting is applied when the index is created or rebuilt, but is not maintained as data changes in the index.  If SQL Server was to maintain the fill factor, this would incur additional page splits which would defeat the purpose of the fill factor setting.  If you start out with a high fill factor, the value might also be lowered over time due to page splits.
In most situations there is no need to change the server level default setting for the fill factor.  As mentioned previously there might be certain indexes that would benefit from a lower fill factor and this can be set on an index by index basis by rebuilding the index with a lower fill factor.  It's worth noting that in most cases data will be read from the index much more than it is written to so a high fill factor is often a good choice. 

External Fragmentation

External fragmentation refers to the amount of out-of-order leaf pages in an index, normally expressed as a percentage.  Unlike internal fragmentation, there is no benefit in the index leaf pages been out-of-order and a percentage as close to zero as possible is desired.  External fragmentation also occurs as a result of page splits, which maintain the logical order of the index with previous and next pointers but not the physical order.  Out of order pages are not necessarily a problem for all query types but they do reduce the performance of ordered scans, where data in the index needs to be fetched back in logical order.  For example, if you have an index on a date column, a query requesting the range from 1st Jan to 31st of Jan might result in an ordered scan. 

File System Fragmentation

File system fragmentation occurs at the o/s level and it's completely separate from the internal and external fragmentation that is reported by SQL Server.  It's possible for SQL Server to report very low levels of internal and external fragmentation and for your database files to be severely fragmented at the file system level.  It's also possible for the reverse to be true where the file system reports very low levels of fragmentation, but the internal structure of your database is heavily fragmented.  If you perform an o/s level defragmentation, the internal contents of your database files will remain unchanged including the fragmentation levels of your index.  Defragmenting indexes in SQL Server won't improve the file system fragmentation levels of your database files. 
It's worth noting that file system fragmentation can largely be avoided by proper management of the growth of your database files.  You should avoid growing your database files in small increments and pre-allocate space in your database files rather than relying on auto growth.  You should keep your database files on dedicated volumes to avoid the drives been fragmented by other processes.  If you use database snapshots, I strongly advise that these are allocated their own drive separate from your other database files.

Index Maintenance

Indexes can become fragmented over time, both in terms of internal an external fragmentation, resulting in a degradation in performance.  There are two different methods you can use to reduce fragmentation and improve the performance of your indexes; an index rebuild or an index reorganize.  An index reorganize will defragment the leaf levels of clustered an nonclustered indexes by physically moving data so that the physical order of the index matches the logical order of the index.  The process is considered an "online" operation as it doesn't hold any long-term locks that might cause blocking in your database. 
An index rebuild is the same as dropping and re-creating the index.  By default this is considered an offline operation as long term locks are held during the index rebuild process, which can cause severe blocking in your database.  If you are using the Enterprise, Developer or Evaluation editions of SQL Server, you have the option to rebuild your indexes online.  Locks are still used during the online index rebuild, but they are held for a much shorter period of time.  There are some limitations that apply to online index rebuilds that need to be considered.  For example, online index rebuilds are not supported for XML indexes or indexes that contain LOB data (image, text, NVARCHAR(MAX), VARCHAR(MAX) etc).  They are also not supported on partitioned indexes and disabled indexes.
If the level of fragmentation is high (>30%), you should consider rebuilding your indexes and for lower fragmentation levels, you should consider an index reorganize.

How Often?

You should always attempt to perform index maintenance out of hours or use an online method of index maintenance during periods of very low system activity if your databases are online 24x7.  Your maintenance windows will dictate to a certain extent when and how often you are able to perform index maintenance. 
It's worth noting that index fragmentation occurs as a result of insert, update and delete statements and you don't need to perform index maintenance more frequently than the data changes. You might have some databases that are read only or ones that are updated infrequently.  For example, if you have a data warehouse (reporting database) that is updated on a monthly basis, it's unnecessary to perform weekly index rebuilds as the database is essentially read only between data loads.  In the case of a data warehouse, it might be appropriate to perform index maintenance after loading new data into the database. 

Index Maintenance Strategy

If your maintenance window allows for it, the easiest option is simply to rebuild all the indexes in your database.  You could do this using a database maintenance plan or using a script similar to the one below:

FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id

sp_executesql @SQL
The script doesn't specify a fill factor so the fill factor that was set when the index was created or last rebuilt will be used.  This is normally the best option to use for index maintenance scripts that rebuild all of your indexes as you might want to tailor this value for individual indexes as required. 
If your database is large, it's likely to take a considerable amount of time to rebuild all your indexes so it's often better to take a more targeted approach.  One option is to  assess the levels of fragmentation by using the "sys.dm_db_index_physical_stats" dynamic management function and decide which indexes to rebuild based on the levels of fragmentation.  For example, the following query returns fragmentation statistics for all the indexes in the current database:
SELECT as schema_name,
   as table_name,
   as index_name,
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , 'LIMITED') ips
JOIN sys.indexes si on ips.object_id = si.object_id and ips.index_id = si.index_id
JOIN sys.objects so on ips.object_id = so.object_id
JOIN sys.schemas s on so.schema_id = s.schema_id
The "avg_fragmentation_in_percent" column gives you the level of external fragmentation and it's this value that it used for the index maintenance stored procedure in this article to decide which indexes to reorganize or rebuild.  You can find more information about the "sys.dm_db_index_physical_stats" dynamic management function here.

Index Maintenance Stored Procedure

The stored procedure accompanying this article makes use of the  sys.dm_db_index_physical_stats function mentioned previously to identify the levels of external fragmentation in your indexes.  Indexes are then rebuilt or reorganized based on the thresholds you specify to the stored procedure.  The parameters for the stored procedure are described in the table below:
Parameter Name Description
@ReorganizeThresold The value specified here indicates the minimum level of fragmentation that will trigger index maintenance.  The type of index maintenance would be an index reorganize unless the level of fragmentation also exceeds the @RebuildThreshold. 
@RebuildThreshold If the level of fragmentation is greater than the rebuild threshold an index rebuild is performed instead of a reorganize.  If you use a value greater than 100 for the rebuild threshold, the stored procedure will always use an index reorganize instead of a rebuild.
If you set the reorganize threshold to the same value as the rebuild threshold, a rebuild will always be used unless the online option is also specified.  If the online option is specified a reorganize would be used in those cases where it is not possible to perform an online rebuild.
@DatabaseName This is the name of the database where you want to defragment your indexes.
@Online If you are running the Enterprise, Developer or Evaluation editions of SQL Server, this option can be used to perform online index rebuilds.  If it's not possible to do an online index rebuild, a reorganize will be used instead.
@Debug This parameter can be set to 1 if you want to see what commands would be issued by the stored procedure without running them.  In most cases this parameter should be ignored or set to the default value of 0.
@SortInTempDB This parameter affects index rebuild operations by including the SORT_IN_TEMPDB=ON option.  This option can be used to improve the performance of index rebuilds if the storage of the tempdb database is appropriately designed.
@PageCountThreshold It can sometimes be useful to exclude small indexes from your index maintenance scripts.  By default the script will exclude indexes with a page count less than 512 (4MB). 
Although the stored procedure has a number of parameters, most of them have default values.  In it's simplest form you can execute the script just by passing in the name of the database you want to defragment:

EXEC dbo.DefragIndexesForDB @DatabaseName='AdventureWorks'

This is equivalent to the statement below:
EXEC dbo.DefragIndexesForDB 
      @Online =1
In addition to the stored procedure, two tables are also created to track your index maintenance; index_maintenance_batch and index_maintenance_log.  A single row is inserted into the index_maintenance_batch table every time you execute the DefragIndexesForDB stored procedure.  This tracks information such as the start time, end time and parameter options chosen for the index maintenance.  A row is inserted into the index_maintenance_log table for every index rebuild or reorganize operation and is linked to the index_maintenance_batch table by the batch_id column.

Line Numbers: On  Off      Plain Text
/* Prerequsites
	Requires dbo.fnSplitString function:
-- Remove objects from DB if they already exists (uncomment if required)
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[index_maintenance_log]') AND type in (N'U'))