Improve DPM recoverable object search
Applies To: System Center 2012 - Data Protection Manager, System Center 2012 R2 Data Protection Manager, System Center 2012 SP1 - Data Protection Manager
The time required for DPM Recoverable Object Search to return recovery points that meet the specified criteria increases as the number of recovery points grows and as the DPM database (DPMDB) becomes more fragmented. You can reduce the search time by performing regular maintenance on the DPM database.
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. The following table lists the database tables for which indexes need to be rebuilt for a specific data source.
| Data source | Tables in DPMDB |
|---|---|
SharePoint |
tbl_RM_SharePointRecoverableObject |
Exchange Mailbox |
tbl_RM_DatasetROMap |
Rebuilding Indexes
Rebuilding an index deletes the index and creates a new one. Rebuilding an index removes fragmentation and reclaims disk space by compacting the pages that are 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 an index
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 an index 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 takes longer than rebuilding the index.
Query to rebuild indexes
USE DPMDB
GO
ALTER INDEX ALL ON <tableName> REORGANIZE
GO
Rebuilding Compared To Reorganizing
| Rebuilding | Reorganizing |
|---|---|
Takes the table whose indexes are being currently rebuilt offline. Rebuilding should be done when it will least affect normal operations. |
Leaves the table whose indexes are being 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. |
Additional Resources
-----
For additional resources, see Information and Support for System Center 2012.
Tip: Use this query to find online documentation in the TechNet Library for System Center 2012. For instructions and examples, see Search the System Center 2012 Documentation Library.
-----