DPM/SQL Performance

DPM is largely depending on its SQL database. All settings, jobs and recovery information is hold in the DPM database.
When the DPM database has performance issues then this will also impact the performance of the DPM console.
One of the possible performance issue are fragmented Indexes in your database.

The Report Index Physical Statistics show the status of the fragmentation

 

Rebuild indexes
When the indexes are heavily fragmented then you need to rebuild them. You can do that by hand, but that’s a ‘hell of a job’.

Therefor I use the script below which will rebuild every index existed in database with fillfactor of 80.

DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO

(Source: \http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/)