Defragmenting indexes for Project Server 2007 databases
This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.
Topic Last Modified: 2016-11-14
Database maintenance tasks can be performed by either executing Transact-SQL commands or running the Database Maintenance Wizard. This article provides details on both the approaches.
The recommended database maintenance tasks for Microsoft Office Project Server 2007 databases include:
Checking database integrity
Defragmenting indexes by either reorganizing them or rebuilding them
Setting the fill factor for a server
Monitoring the database size to pre-grow the database or to shrink databases
Cleaning up the history
Updating Statistics
Defragmenting indexes by reorganizing or rebuilding
Fragmentation occurs when the logical and physical storage allocation of a database contains many scattered areas of storage that are insufficient, not physically contiguous, or have become too fragmented to be used efficiently. Fragmentation can be the result of many inserts, updates, or deletes to a table. When a table becomes fragmented, the indexes defined on the table also become fragmented.
Office Project Server 2007 uses GUID types as clustering keys, which avoids concurrent inserts competing for the same data pages (insert hot spots), but which can be a cause of table and index fragmentation. Fragmentation can occur because new records can be inserted anywhere in the b-tree, rather than at the end, leading to a greater propensity to page split (index and data) and hence fragment. This is mitigated by clustering on composite keys that use the Project UID to ensure data pages contain related data, but regular defragmentation of the larger tables will improve performance, especially in large deployments of Office Project Server 2007.
Over time, database fragmentation can result in performance degradation (unnecessary disk activity) and inefficient space utilization. To mitigate fragmentation and minimize the rate at which fragmentation occurs, manually set the size of content databases to be as large as possible given your business requirements and database architecture. For example, if you have a requirement to limit content databases to 100 gigabytes (GB), after you have created your content databases, set their size to 100 GB in SQL Server Management Studio.
Although you can defragment tables, defragmenting indexes is more beneficial to database performance, and it is much faster. This article only describes how to defragment indexes.
Before implementing a database fragmentation maintenance plan, determine which tables and indexes are most fragmented and then create a maintenance plan to rebuild or reorganize those indexes.
Measuring fragmentation can be done in the following manner:
In SQL Server 2005, use the sys.dm_db_index_physical_stats dynamic management view
In SQL Server 2000, use DBCC SHOWCONTIG
Note that the algorithm for calculating fragmentation is more precise in sys.dm_db_index_physical_stats than in DBCC SHOWCONTIG. As a result, fragmentation values calculated by sys.dm_db_index_physical_stats appear higher.
Measuring fragmentation with sys.dm_db_index_physical_stats (SQL Server 2005)
In SQL Server 2005, use the sys.dm_db_index_physical_stats dynamic management view to determine fragmentation for the indexes on a specified table or view.
For measuring fragmentation, we recommend that you monitor the column avg_fragmentation_in_percent. The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable.
For information about how to use sys.dm_db_index_physical_stats, see sys.dm_db_index_physical_stats (https://go.microsoft.com/fwlink/?LinkId=128479\&clcid=0x409)
Measuring fragmentation using DBCC SHOWCONTIG (SQL Server 2000)
To check the fragmentation of database tables, an administrator can use the DBCC SHOWCONTIG function to report on logical and extent scan fragmentation. For a complete explanation of DBCC SHOWCONTIG results, see DBCC SHOWCONTIG (https://go.microsoft.com/fwlink/?LinkId=110841\&clcid=0x409).
For measuring fragmentation, we recommend that you monitor the scan density value returned by DBCC SHOWCONTIG. In tables in which everything is contiguous, scan density is 100.
Reducing fragmentation for a database
To reduce the level of index fragmentation, run the stored procedure in How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases (https://go.microsoft.com/fwlink/?LinkID=110843\&clcid=0x409) in the Microsoft Knowledge Base.
After determining the level of fragmentation of your databases, you can schedule the stored procedure to be run daily, weekly, or monthly, depending on your needs and the overall rate of change in your environment. Generally, we recommend that you establish a weekly defragmentation schedule, at a minimum. We also recommend that you schedule defragmentation operations after running DBCC CHECKDB REPAIR operations.
This stored procedure changes your content database indexes. Any modification to the stored procedure is not supported. For additional information on the changes that are supported for SharePoint Products and Technologies content databases, see Support for changes to the databases that are used by Office server products and by Windows SharePoint Services (https://go.microsoft.com/fwlink/?LinkID=110844\&clcid=0x409) in the Microsoft Knowledge Base.
Reducing fragmentation for a specific table and its indexes
If you want to defragment the index associated with a particular table, rather than an entire database, you can either reorganize or rebuild the index. For more information, see Clustered Index Structures (https://go.microsoft.com/fwlink/?LinkId=128480\&clcid=0x409).
Reorganizing an index means that the index leaf level will be reorganized. Index reorganization defragments and compacts clustered and non-clustered indexes on tables and views and can significantly improve index scanning performance. Reorganization is always performed online, so that the underlying table is available to users. Reorganization is equivalent to the SQL Server 2000 DBCC INDEXDEFRAG statement.
Rebuilding an index means that the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. Rebuilding improves the performance of index scans and seeks. You can rebuild the index with a table either on- or offline. Rebuilding is equivalent to the SQL Server 2000 DBCC DBREINDEX statement.
The fragmentation level of an index determines the method you should use to defragment it, and whether it can remain online, or should be taken offline.
| Fragmentation level | Defragmentation method |
|---|---|
Up to 10% |
Reorganize (online) |
10 – 75% |
Rebuild (online) |
75% or more |
Rebuild (offline) |
Note that using the DROP INDEX and CREATE INDEX commands is not supported on SharePoint Products and Technologies databases.
You can reorganize and rebuild indexes by using the SQL Server 2005 ALTER INDEX statement, the SQL Server 2005 Maintenance Wizard, the SQL Server 2000 DBCC INDEXDEFRAG and DBCC DBREINDEX statements, or the SQL Server 2000 Maintenance Wizard. This topic presents only the SQL Server 2005 options in detail. For more information about SQL Server 2000 options, see the following resources:
DBCC INDEXDEFRAG (https://go.microsoft.com/fwlink/?LinkId=128482\&clcid=0x409)
DBCC DBREINDEX (https://go.microsoft.com/fwlink/?LinkId=128483\&clcid=0x409)
Database Maintenance Plan Wizard (https://go.microsoft.com/fwlink/?LinkID=110849\&clcid=0x409)
Using ALTER INDEX
ALTER INDEX permits a database administrator to perform maintenance operations against an existing table or view index. It can be used to disable, rebuild, and reorganize indexes or optionally setting options on the index. ALTER INDEX replaces the DBCC DBREINDEX and DBCC INDEXDEFRAG statements.
In most cases you can rebuild indexes while the database is online, because there are no significant gains in an offline rebuild of the indexes. However, it is important to note that when an index is being rebuilt, a shared table lock is put on the table, preventing all operations with the exception of SELECT operations from being performed. SharePoint Products and Technologies databases use clustered indexes specifically. When a clustered index is being rebuilt, an exclusive table lock is put on the table, preventing any table access by end users.
You can customize the following sample script to rebuild all indexes on a table.
USE Contoso_Content_1
GO
ALTER INDEX ALL ON [database_name. [ schema_name ] . | schema_name. ]table_or_view_name
REBUILD WITH (FILLFACTOR = 70, SORT_IN_TEMPDB = ON, ONLINE = ON,
STATISTICS_NORECOMPUTE = ON)
GO
Special consideration for the Reporting Database
As we expect customers to implement custom reports based on custom fields and data available in reporting database, we recommend following the best practices for T-SQL writing and index creation to ensure scalable and performance reporting solution. Office Project Server 2007 does not index these (dynamically generated) tables outside of the primary key. The Infrastructure Update for Microsoft Office Servers provides additional functionality. For more information, see the "RDS Optimizations for Custom Fields" section in the downloadable article named Project 2007 Infrastructure Update Release for Server and Client (https://go.microsoft.com/fwlink/?LinkId=121912).
When working with Microsoft Customer Service and Support, a support engineer might ask you to remove any additional indexes created or remove any additional columns added to existing indexes. This is because additional indexes can change data access paths and in some situations lead to unexpected performance and locking/deadlocking issues.
Setting the fill factor for a server
The fill factor can be used to further improve index data storage and performance. When indexes are created or rebuilt, the fill factor value (1–100) determines the percentage of space on each leaf-level page that can be filled with data. The remaining space is reserved for future growth. For many situations the default server-wide fill factor level of 0 is optimal; however, for Microsoft Office SharePoint Server 2007, a server-wide setting of 70 is optimal to support growth and minimize fragmentation.
Although it is possible, we do not recommend that you set the fill factor for individual tables or indexes.
To view the fill factor value of one or more indexes, query the sys.indexes catalog view. For more information about the view, see sys.indexes (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=128510\&clcid=0x409).
To configure the server-wide fill factor value, use the sp_configure system stored procedure. For more information, see spconfigure (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=128512\&clcid=0x409).