Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Improving DPM Recovery Search for SharePoint Items

Updated: November 1, 2013

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 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
tbl_RM_RecoverySource

Exchange Mailbox

tbl_RM_DatasetROMap
tbl_RM_RecoverableObject
tbl_RM_RecoverySource

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.

See Also

Other Resources

ALTER INDEX (Transact-SQL)

-----
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.
-----
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.