Tips for Rebuilding Indexes

Tips for Rebuilding Indexes

http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx

By : Brad McGehee Feb 21, 2007

 

Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance. It will also update column statistics.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).

The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:

--Script to automatically reindex all tables in a database

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.

When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.

For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized. [7.0, 2000, 2005] Updated 7-24-2006

*****

When you create or rebuild an index , you can specify a fill factor, which is the amount the data pages in the index that are filled. A fill factor of 100 means that each index page is 100% full, a fill factor of 50% means each index page is 50% full. If you create a clustered index that has a fill factor of 100, and it is not based on a monotonically increasing key, that means that each time a record is inserted (or perhaps updated), page splits may occur because there is no room for the data in the existing pages. Numerous page splits can slow down SQL Server's performance.

Here's an example: Assume that you have just created a new index on a table with the default fill factor. When SQL Server creates the index, it places the index on contiguous physical pages, which allows optimal I/O access because the data can be read sequentially. But as the table grows and changes with INSERTS, UPDATES, and DELETES, page splitting occurs. When pages split, SQL Server must allocate new pages elsewhere on the disk, and these new pages are not contiguous with the original physical pages. Because of this, random I/O, not sequential I/O access must be used to gather the data, which is much slower, to access the index pages.

So what is the ideal fill factor? It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines:

• Low Update Tables (100-1 read to write ratio): 100% fill factor
• High Update Tables (where writes exceed reads): 50%-70% fill factor
• Everything In-Between: 80%-90% fill factor.

You may have to experiment to find the optimum fill factor for your particular application. Don't assume that a low fill factor is always better than a high fill factor. While page splits will be reduced with a low fill factor, it also increases the number of pages that have to be read by SQL Server during queries, which reduces performance. And not only is I/O overhead increased with a too low of fill factor, it also affects your buffer cache. As data pages are moved in from disk to the buffer, the entire page (including empty space) is moved to the buffer. So the lower the fill factor, the more pages that have to be moved into SQL Serve's buffer, which means there is less room for other important data pages to reside at the same time, which can reduce performance.

If you don't specify a fill factor, the default fill factor is 0, which means the same as a 100% fill factor, (the leaf pages of the index are filled 100%, but there is some room left on intermediate index pages). In most cases, this default value is not a good choice, especially for clustered indexes. [6.5, 7.0, 2000, 2005] Updated 7-24-2006

*****

If you find that your transaction log grows to an unacceptable size when you run DBCC REINDEX , you can minimize this growth by switching from the Full Recovery mode to the Bulk-Logged mode before you reindex, and when done, switch back. This will significantly reduce the size of the transaction log growth. [2000, 2005] Updated 7-24-2006

*****

If you have a table that has a clustered index on a monotonically increasing or decreasing primary key, and if the table is not subject in UPDATEs or if it has no VARCHAR columns, then the ideal fill factor for the table is 100. This is because such a table will normally not experience any page splits. Because of this, there is no point in leaving any room in the index for page splits. And because the fill factor is 100, SQL Server will require fewer I/Os to read the data in the table, and performance will be boosted. [7.0, 2000, 2005] Updated 7-24-2006

*****

If you are not sure what to make the fill factor for your indexes , your first step is to determine the ratio of disk writes to reads. The way to do this is to use these two counters: Physical Disk Object: % Disk Read Time and Physical Disk Object: % Write Time. When you run both counters on an array, you should get a good feel for what percentage of your I/O are reads and writes. You will want to run this over a period of time representative of your typical server load. If your percentage writes greatly exceeds the percentage of reads, then a lower fill factor is called for. If your percentage of reads greatly exceeds the percentage of writes, then a higher fill factor is called for.

Another Performance Monitor counter you can use to help you select the ideal fill factor for your environment is the SQL Server Access Methods: Pages Splits/Sec. This counter measures the number of page splits that are occurring in SQL Server every second. For best performance, you will want this counter to be as low as possible, as page splits incur extra server overhead, hurting performance. If this number is relatively high, then you may need to lower the fill factor in order to prevent new page splits. If this counter is very low, then the fill factor you have is fine, or it could be a little too low. You won't know unless you increase the fill factor and watch the results.

Ideally, you want a fill factor that prevents excessive page splits, but not so low as to increase the size of the database, which in turn can reduce read performance because of all the extra data pages that need to be read.

Once you know the ratio of disk write to reads, you now have the information you need to help you determine an optimum fill factor for your indexes. [6.5, 7.0, 2000, 2005] Updated 7-24-2006

*****

If you want to determine the level of fragmentation of your indexes due to page splitting, you can run the DBCC SHOWCONTIG command. Since this command requires you to know the ID of both the table and index being analyzed, you may want to run the following script:

--Script to identify table fragmentation

--Declare variables
DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)

--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = OBJECT_ID('table_name') --enter name of table

--Get the Index Values
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName

--Display the fragmentation
DBCC SHOWCONTIG (@id, @IndexID)

While the DBCC SHOWCONTIG command provides several measurements, the key one is Scan Density. This figure should be as close to 100% as possible. If the scan density is less than 75%, then you may want to reindex the tables in your database, and also increase the fill factor if you are finding that the current fill factor you are using is not appropriate. [6.5, 7.0, 2000] Updated 7-24-2006

*****

Here's a script that is used to create DBCC SHOWCONFIG commands for all of the indexes in one or more tables . Once you run this script, it will produce for you a DBCC SHOWCONFIG statement for each index, which you can then run to find out about the level of fragmentation of your indexes. This script is especially handy if you don't know the names of the indexes in your tables (which is most of the time).

SELECT 'dbcc showcontig (' +
CONVERT(varchar(20),i.id) + ',' + -- table id
CONVERT(varchar(20),i.indid) + ') -- ' + -- index id
object_name(i.id) + '.' + -- table name
i.name -- index name
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.type = 'U'
and i.indid < 2
and
i.id = object_id(o.name)
ORDER BY
object_name(i.id), i.indid

Once you run this script, the output will be DBCC SHOWCONFIG statements for each of the tables(s) and index(es). This output can then be cut and pasted into Query Analyzer or Management Studio and run, which produces a DBCC SHOWCONFIG result for every index for every table you specified. [7.0, 2000, 2005] Updated 11-1-2005 Tip contributed by Jeff M. Belina and Steven R. Morrow