Monitoring and maintaining Planning Server database indexes
Updated: 2009-04-30
In this article:
Examine Measure Group table indexes
Examine indexes in the AsyncWorkItems tables
Checking index fragmentation
Reorganizing indexes
Rebuilding indexes
Like most database systems that use indexes to improve system performance, the Planning Server system uses indexes to achieve optimal performance. For any clustered or nonclustered indexes, INSERT/UPDATE will cause the pages to split causing fragmentation to occur. Over time, the index fragmentation will become more and more severe and it will slow down system performance. It is important for the database administrator to monitor the index fragmentation status and know what to do when the indexes become very fragmented.
All tables in the Planning Application Database should be monitored for index fragmentation status, but extra attention should be given to the Measure Group tables, whose name starts with the prefix "MG_". In application databases, Measure Group tables undergo extensive insert, update, and delete operations. Types of operations that perform these data changes include assignment submissions and calculation rule executions. Some Measure Group tables will have more activity than others. The indexes on "MG_*" tables become severely fragmented over time and will decrease system performance considerably if the indexes are not rebuilt or organized regularly.
Examine Measure Group table indexes
When a model is created, Planning Server creates one clustered index on the new “MG_*” table. This clustered index includes all dimension keys in that Measure Group table. The order of the dimension keys in this clustered index is arbitrarily arranged by the Planning Server. That order might not best reflect the individual customer’s Measure Group table usage (for example, write back usage, data loading usage, and rule-based calculation usage). Your database administrator might need to look at the clustered index’s column order and rearrange the order.
For example, the following clustered index is created on MG_Strategic_Plan_MeasureGroup_default_partition table with the following prearranged column order. You may rearrange the column order in this index to fit that Measure Group table’s usage. You might also want to move the Entity_MemberID column to the first column in this index because PerformancePoint Add-in for Excel users often perform write-back operations based on Entity.
CREATE CLUSTERED INDEX [ClusteredIndex_default_partition] ON [dbo].[MG_Strategic_Plan_MeasureGroup_default_partition]
(
[Scenario_MemberId] ASC,
[Time_Month] ASC,
[Account_MemberId] ASC,
[BusinessProcess_MemberId] ASC,
[Entity_MemberId] ASC,
[TimeDataView_MemberId] ASC,
[Currency_MemberId] ASC,
[BusinessDriver_MemberId] ASC,
[Product_MemberId] ASC,
[Flow_MemberId] ASC,
[Intercompany_MemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Example
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.
Examine indexes in the AsyncWorkItems tables
It is important to be careful with the AsyncWorkItems and AsyncWorkItemStatusHistory measure group tables. These tables are used to queue the jobs that are processed by the Planning workflow engine and therefore will have frequent insertions and deletions being done. Because of this, these two tables experience rapid and severe index fragmentation. Your database administrator must monitor the fragmentation percentage closely and rebuild the indexes more frequently than the other tables.
Example
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);
Note
You will have better results by using the REBUILD option rather than the REORGANIZE option. See Rebuilding Indexes in this topic for more information.
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.
Note
It is important that you take your application offline before you perform an offline index rebuild for the AsyncWorkItems table. Please see the Planning Administration Console Online Help topic, "How to Take an Application Offline".
Checking index fragmentation
Over time, the database modifications can cause the information in the index to become scattered or fragmented in the database. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
In SQL Server 2005, you can remedy index fragmentation either by reorganizing an index or by rebuilding an index. You need to analyze the index to determine the degree of fragmentation before you decide which defragmentation method to use.
Example
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.
| index_id | name | avg_fragmentation_in_percent |
|---|---|---|
1 |
MG_Strategic_Plan_MeasureGroup_default_partition_PK |
23.076923076923077 |
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.
Note
The preceding 30 percent is from the recommendation in SQL Server Books Online. You can change this number to best fit your organization’s need.
Reorganizing indexes
It is a best practice to reorganize an index when the index is not heavily fragmented. However, if the index is heavily fragmented, you will achieve better results by rebuilding the index. For fragmentation guidelines, see Checking Index Fragmentation.
Example
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.
Rebuilding indexes
An index can be rebuilt by dropping the index and creating a new one. When a new index is created the fragmentation is removed.
The following example shows how to rebuild an index with online option on a Measure Group table in Planning Server.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);
Schedule a SQL Server job to defragment the index on each Measure Group table. After determining the degree of fragmentation, schedule the job to do index reorganize and an index rebuild on different frequencies. You might decide to do an index reorganize once a day, scheduling it at off-peak times, such as midnight, and then do an index rebuild only once a week. Both methods can be done online except the tables, which have line-of-business data types.
Rebuilding an index generally has better results than reorganizing an index. It removes all levels of fragmentation from both the leaf level and the B-tree, rebalancing the tree. It updates statistics, acting the same as doing a “full scan” with accurate statistics.
For more information about rebuilding indexes, see SQL Server Books Online.
Download this book
This topic is included in the following downloadable book for easier reading and printing:
See the full list of available books at Downloadable content for PerformancePoint Planning Server.