A great way to improve performance is to reduce the amount of index reorganization you perform. People without much SQL Server experience frequently set up maintenance plan jobs to perform basic database backup, transaction log backup, and statistics collection jobs on a daily or weekly basis. They will sometimes also use the maintenance plan task to reorganize all indexes daily or weekly. If most of your tables have clustered indexes, this rewrites your entire database every day or week, creating a lot of unnecessary I/O and transaction log activity. Today's blog entry looks at how to know when a reorganization of your indexes is actually required.

Monitor Index Fragmentation, and Defragment When Necessary

SQL Server uses indexes to provide fast access to information when users or applications request it. These indexes are maintained by the Database Engine as the table data grows and/or changes. Over time, the indexes can become fragmented, especially in databases that handle heavy insert, update, and delete activity. An index is fragmented when the physical ordering on the disk does not match the logical order of the data (as defined by the index key) or when data pages that contain the index are dispersed across nonadjacent sections of the disk. Fragmentation of an index can reduce the speed of data access and result in slower application performance. It can also cause more disk space to be used than is actually necessary. You can correct index fragmentation by reorganizing or rebuilding the index.

You can tell which indexes, if any, have fragmentation problems by using the sys.dm_db_physical_stats system function. This function provides lots of details about the physical layout of the index. However, the most important result column for tracking fragmentation is avg_fragmentation_in_percent. This column indicates how fragmented the index is on the disk. A low number means low fragmentation (good); a high number means high fragmentation (bad).

For example, the following query returns index physical statistics for all the indexes in the current database:

SELECT OBJECT_NAME(object_id), 
index_id, 
page_count, 
index_type_desc, 
avg_fragmentation_in_percent, 
fragment_count 
FROM sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED')

To identify the indexes by name rather than by index ID, you can join against the sys.indexes system view using the object name column.

Similar information is also available in the standard reports in SQL Server Management Studio. To view these reports, right-click the Teamcenter database, click Reports, click Standard Reports, and then select the Index Physical Statistics report on the menu.

The following table indicates general guidelines for interpreting the avg_fragmentation_in_percent value.

Reorganizing an index does not block user access to the index while the reorganization is underway. However, rebuilding or re-creating the index does prevent user access to the index. The exception to this is if the ALTER INDEX REBUILD statement is used with the ONLINE = ON option. Note that online index rebuilding requires SQL Server 2008 Enterprise.

How Often Should You Check?

Periodically checking index fragmentation and taking any necessary corrective action is important to maintaining the performance of your Teamcenter deployment. The rate at which fragmentation may occur depends on user activity. But as a general rule, Siemens recommends checking index fragmentation at least monthly.

For more information about reorganizing and rebuilding indexes, see the following MSDN website:

http://msdn.microsoft.com/en-us/library/ms189858.aspx

For more information about best practices for running Teamcenter on SQL Server, see the following Microsoft white paper:

http://download.microsoft.com/download/7/3/6/7365D2BB-BB34-4D28-A128-F2C8FBA6E995/Siemens-Teamcenter-and-SQL-Server-Best-Practices.pdf

Richard Waymire is a mentor with Solid Quality Mentors and a former member of the Microsoft SQL Server development team. He has been working on SQL Server for more than 15 years, is a contributing editor of SQL Server Magazine, and is the author of several books about SQL Server. http://www.richardwaymire.com/