Defragmenting indexes for Project Server 2010 databases
Applies to: Project Server 2010
Topic Last Modified: 2011-06-20
Database maintenance tasks can be performed by either executing Transact-SQL commands or running the Database Maintenance Wizard. This article contains details on both the approaches.
The recommended database maintenance tasks for Microsoft Project Server 2010 databases include the following:
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.
Project Server 2010 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, instead of at the end, leading to a greater propensity to page split (index and data) and therefore fragment. This is lessened 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 Project Server 2010.
Over time, database fragmentation can result in performance degradation (unnecessary disk activity) and inefficient space usage. To reduce 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 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 useful 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 with sys.dm_db_index_physical_stats
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 (Transact-SQL) (https://go.microsoft.com/fwlink/p/?LinkID=110839)
Reducing fragmentation for a database
For information about reducing fragmentation for a database, see the Measure and reduce fragmentation section of the Database Maintenance for Microsoft SharePoint 2010 Products white paper. You can download the white paper from Database maintenance for SharePoint 2010 Products.
Reducing fragmentation for a specific table and its indexes
If you want to defragment the index associated with a particular table, instead of a whole database, you can either reorganize or rebuild the index. For more information, see Clustered Index Structures (https://go.microsoft.com/fwlink/p/?LinkID=110847).
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.
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.
The fragmentation level of an index determines the method that 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 Microsoft SharePoint Server 2010 databases.
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.
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, be aware that when an index is being rebuilt, a shared table lock is put on the table, preventing all operations except for SELECT operations from being performed. SharePoint Server 2010 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 that are based on custom fields and data available in the Reporting database, we recommend following the best practices for T-SQL writing and index creation to ensure scalable and performance reporting solution. Project Server 2010 does not index these (dynamically generated) tables outside the primary key.
When you are 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 cases 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 SharePoint Server 2010, 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/p/?LinkId=221728).
To configure the server-wide fill factor value, use the sp_configure system stored procedure. For more information, see sp_configure (Transact-SQL) (https://go.microsoft.com/fwlink/p/?LinkId=221729).