Share via

SQL Server 2008 script to rebuild all indexes for all tables on all databases

  • Article

I got several times the same questions around index and fragmentation on SQL2008. So I decided to add one more item on Internet to discuss about the reconstruction and reorganization of the index and how detect index fragmentation.

Starter
As fragmentation can have a negative impact on the efficiency of data access; one of the main tasks of a DBA is to maintain database indexes.

In the life cycle of a database, fragmentation is expected behavior and natural. If the database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.
 

Main course
Since SQL Server 2005 the sys.dm_db_index_physical_stats Dynamic Management Function returns the size and fragmentation information for the data and indexes of the specified table or view.

NB: Although SQL Server 2008 still supports the SQL Server 2000 DBCC SHOWCONTING command, this feature will be removed on a future version of SQL Server. So, I invite you to remove it from your new development.

 

1. There are 3 mode in the syntax of sys.dm_db_index_physical_stats:

 
LIMITED (default): This mode is the fastest and scans the smallest number of pages. It scans all pages for a heap, but only scans the parent-level pages, which means, the pages above the leaf-level, for an index.
SAMPLED This mode returns statistics base on a one percent sample of the entire page in the index or heap. If the index or heap has fewer than 10 000 pages, DETAILD mode is used instead of SAMPLED.
DETAILED Detailed mode scans all pages and returns all statistics. Be careful, from LIMITED to SAMPLED to DETAILED, the mode are progressively slower, because more work is performed in each. In my script I am using this one

2. There are 2 kind of fragmentation on indexes.

 
Logical fragmentation(indexes)  is the percentage of an index that consists of out-of-order pages in the leaf pages. An out-of-order page is the one for which the next page indicated in an IAM is different from the page pointed to by the next page pointer in the leaf page.
Extent fragmentation (heap) is the percentage of a heap that consists of out-of-order extents in the leaf pages. An out-of-order extents is the one for which the extent that contains the current page for a heap is not, physically, the next extent after the extent that contains the previous page.
   

In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing and rebuilding depends on defragmentation values. The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. The value should be as close to zero as possible. A value between 5-30% indicates moderate fragmentation, while any value over 30% indicates high fragmentation.

 

The avg_page_space_used_in_percent is another value that it is worth to look closely. This value represents the amount of spaced used in the indexes. A value below 75% is usually associated to internal fragmentation (more blank pages on our book than recommended).

 

Dessert
The script will work for both SQL 2005 and higher versions. The aim is to reduce index fragmentation by recreating, reorganizing, or rebuilding the index:

  DOWNLOAD THE SCRIPT SAMPLE HERE

 

To run the stored procedure without execute the rebuild : EXECUTE handdleFragmentationIndexes @debugMode = 1

To run the stored procedure and defragement indexes :  EXECUTE handdleFragmentationIndexes

To run the stored procedure and defragement indexes on a specific database : EXECUTE handdleFragmentationIndexes @databaseName = 'myDatabaseName'

This script sample is extract in part from the following BOL : sys.dm_db_index_physical_stats (Transact-SQL)

 

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

Comments

  • Anonymous
    January 01, 2003
    Just for information about Arbi's comment. This issue is fixed. See the article KB below : support.microsoft.com/.../977076 FIX: Error message when you run an ALTER INDEX statement on a table that has CDC enabled in SQL Server 2008: "Msg 22983: The unique index <Index Name> on source table <Table Name> is used by Change Data Capture"

  • Anonymous
    January 01, 2003
    Michel, When you have Change Data Capture(CDC) enabled on some of your tables, when you want to do a maintenance on regular tables you need to disable CDC on table and then do the maintenance and enabled CDC back again. I like your script just want to let you know about it.

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    Michael,

    thank you for providing this script! It saved me much time"

    In Step 2 of your stored proc it seems the "database prefix" is missing when joining the ".sys.schemas". For most of my tables, I had no object names and schema names without that prefix, as these are lying in "user created" schemas.
    I've added the prefix and it seems to work.:

    ' SELECT @objectname = o.name, @schemaname = s.name '
    + ' FROM ['+@db_name&#43;'].sys.objects AS o '
    + ' JOIN ['+@db_name&#43;'].sys.schemas as s ON s.schema_id = o.schema_id '
    + ' WHERE o.object_id = ' + CAST(@objectid as varchar(50)

    Thank you again
    Cheers
    Ralf Jörg