Monitoring and maintaining Planning Server database indexes

The following example shows how to rebuild an index with online option on a Measure Group table in a Planning Application Database.

ALTER INDEX [ClusteredIndex_default_partition] 
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);

The database administrator should create a job on the computer that is running Microsoft SQL Server 2005 to defragment the index on each Measure Group table. Depending on the fragmentation situation, schedule the job to do index reorganize and then rebuild the index on different frequencies.

You might decide to reorganize the index once a day and rebuild the index once a week. Some indexes might need more frequent rebuilds, such as the clustered indexes on the Measure Group table. Schedule the rebuilding indexes jobs at an off-peak time, such as midnight. Both methods can be done online except the tables, which have line-of-business data types. If you choose to do offline index rebuild instead of online, you need to take your application offline first. For information about how to take an application offline, see the Planning Administration Console Help.

Here is an example of checking index fragmentation on these two tables:

SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), 
OBJECT_ID('AsyncWorkItems'),
     NULL, NULL, NULL) AS a
         JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id


SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), 
OBJECT_ID('AsyncWorkItemStatusHistory'),
     NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND 

Because the AsyncWorkItems table contains the LOB data type, the "rebuild index with online" option cannot be performed on it. You can either decide to reorganize the index which is always online, or take PerformancePoint Planning Process Service offline and then rebuild the index with the offline option and then defragment the AsyncWorkItems table. See the two examples that are shown here:

ALTER INDEX [XPKAsyncWorkItems] 
ON dbo.[AsyncWorkItems]
REORGANIZE ;

Take PerformancePoint Planning Process Service offline first, and then:

ALTER INDEX [XPKAsyncWorkItems] 
ON dbo.[AsyncWorkItems]
REBUILD WITH (ONLINE = OFF);

This example can be used on the AsyncWorkItemStatusHistory table:

ALTER INDEX [XPKAsyncWorkItemStatusHistory] 
ON dbo.[AsyncWorkItemStatusHistory]
REBUILD WITH (ONLINE = ON);

Your database administrator can create a job on the computer that is running SQL Server to defragment the index on these two AsyncWorkItems tables. Depending on the fragmentation situation, schedule the job to do an index reorganize and then rebuild the index on different frequencies.

You may decide to reorganize the index once a day and rebuild the index once a week. Schedule the rebuilding indexes jobs at an off-peak time, such as midnight.

The following example shows how to monitor index fragmentation.

When you run the following code statement:

SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('MG_Strategic_Plan_MeasureGroup_default_partition'),
     NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

a result set similar to the following will be returned.

For more information about fragmentation, see SQL Server Books Online . In this example, the recommended resolution is to reorganize MG_Strategic_Plan_MeasureGroup_default_partition_PK because the avg_fragmentation_in_percent is less than 30 percent.

If the fragmentation had been greater than 30 percent, the recommendation would have been to rebuild the index.

The following example shows how to reorganize the primary key index on a Measure Group table in Planning Server. To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause.

ALTER INDEX [ClusteredIndex_default_partition] 
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REORGANIZE ;

Reorganizing the index is not as exhaustive as rebuilding the index; it only cleans up fragmentation in the leaf level. It does not move the object for better extent scan density. The reorganize index command is always run online. No long-running locks are held during index reorganization. For information about index reorganizing and the pros and cons of reorganizing an index and rebuilding an index, see SQL Server Books Online.