Improving DPM Recovery Search for SharePoint Items
Applies To: System Center Data Protection Manager 2010
The time required for DPM Recoverable Object Search to return recovery points that meet the specified criteria will increase over time as the number of recovery points grow and the DPMDB gets more and more fragmented. You can improve the time taken by the search by carrying out regular maintenance on the DPMDB.
The following table lists the set of tables for which indexes need to be rebuilt for a specific data source. To improve the performance of the recovery point search for a data source, you need to rebuild or reorganize the indexes related to that data source.
| Data source | Tables in DPMDB |
|---|---|
SharePoint |
tbl_RM_SharePointRecoverableObject |
Exchange Mailbox |
tbl_RM_DatasetROMap |
Rebuilding Indexes
Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve SQL query performance by reducing the number of page reads required to obtain the requested data.
Query to rebuild indexes
USE DPMDB
GO
ALTER INDEX ALL ON <tableName> REBUILD
GO
Reorganizing Indexes
Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.
Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space.
In some cases, the gain might not be significant. It is also a longer running operation compared to rebuilding the index.
Query to rebuild indexes
USE DPMDB
GO
ALTER INDEX ALL ON <tableName> REORGANIZE
GO
Rebuilding v/s Reorganizing – A Comparison
| Rebuilding | Reorganizing |
|---|---|
Takes the table whose indexes are being currently rebuilt offline. It should be done when it will least affect normal operations. |
Keeps the table whose indexes are being currently reorganized online and working normally. Does not affect normal operations. |
Substantial performance gains in search and browse operations. |
Moderate performance gains in search and browse operations. |
Not a time intensive operation. |
Usually a time intensive operation. |
Most effective when index is heavily fragmented. |
Most effective when index is not heavily fragmented. |