If you want to be a better and well-rounded DBA for Teamcenter, understanding DBCC (Database Console Commands) is an important step. In my last post, I covered the basics of the DBCC SHOWCONTIG command and how to find table fragmentation. In this post, I'll describe the DBCC DBREINDEX command, which helps you fix table fragmentation issues to improve performance.
The DBCC DBREINDEX command rebuilds an index on a table or all the indexes on a table. You use it to rearrange the extents and the pages within a table so that they are in index order. Pages scan faster when they have been written in heap memory in the same order as their next page's pointers (index order).
Extents can be paged from disk faster during a scan operation when they're written to storage in the same order as their next extent pointers (index order). Indexes that are not in the same logic order as they are stored in memory or on storage are fragmented. Read more about this in my last post.
A simple example of the DBCC DBREINDEX command is as follows:
DBCC DBREINDEX ('Production.Product')
Page splits are the performance-expensive process of inserting data into a full index page. To avoid page splits, you can leave some empty room in each page, which allows data to be inserted. Fill factor is a percentage between 1 percent and 100 percent that determines how full the page should be when it is rebuilt.
A fill factor of 100 percent is completely full—no extra room. The next insert in the page will require a page split. You can read more about fill factor on MSDN.
The example command above rebuilds all the indexes on a table and sets the fill factor (by default) to the original fill factor when the table was created.
The DBCC DBREINDEX command also lets you assign a new fill factor to the index. This can be very helpful if you have to gather statistics about how the table is used and if you want to allocate more space for the inserts. The following example sets an 80 percent fill factor for all indexes on the table:
DBCC DBREINDEX ('Production.Product', '', 80)
The number 80 in this command means 80 percent full and 20 percent empty space for new data.
When DBCC DBREINDEX rebuilds a clustered index (or rebuilds all the indexes), DBCC DBREINDEX puts an exclusive table lock on the table, which prevents any users from accessing the table. When DBCC DBREINDEX rebuilds a nonclustered index, DBCC DBREINDEX puts on a shared table lock, which prevents all but SELECT operations from being performed on it.
DBCC commands provide enormous benefits to the well-informed DBA. I will continue writing this series of posts with information about the additional DBCC commands that you should know. My next post will be about the DBCC INDEXDEFRAG command, which defragments your indexes without the exclusive lock that DBCC DBREINDEX puts on the table.
Wayne Berry (6230289B-5BEE-409e-932A-2F01FA407A92) is a computer programmer and serial entrepreneur with a passion for dynamic Web sites that run on SQL Server. When he is not blogging for MSDN, you can find him blogging for Project 31-A. http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com