WARNING! Although I’ve successfully implemented the below against a production DPM database to improve performance, this procedure may not be supported by Microsoft, and I cannot be held responsible for any consequences.
Note, however, that the below only involves adding indices to the database and, assuming a record is kept of these indices, they can easily be removed. The data itself is not altered.
When small, the DPM database performs well, but when larger (perhaps >5 GiB), even simple actions such as opening the DPM console can cause SQL Server to struggle to serve the data. The indicators of this bottleneck are excessive processor utilisation by SQL Server, DPM taking a long time to perform simple actions, and the console crashing due to time-outs. The fix for this is to add missing indices to the DPM database. In theory, adding indices can increase the write time to the database, but in practice I haven’t found this to be the case with DPM, and read times can be dramatically improved. The size of the DPM database will increase; from experience, by ~30%.
To determine the indices to add, execute the following in the context of the DPM database:
SELECT dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) AS AverageEstimatedImpact, dm_migs.last_user_seek AS LastUserSeek, OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS TableName, CreateStatement = 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.[statement] + ' (' + ISNULL(dm_mid.equality_columns, '') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') FROM sys.dm_db_missing_index_groups dm_mig INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle WHERE dm_mid.database_ID = DB_ID() AND dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) > 100 ORDER BY AverageEstimatedImpact DESC
CreateStatement column contains the statement(s) that are to be executed to create the missing indices. The query isn’t perfect – it sometimes recommends the creation of duplicate indices, so it’s worth manually reviewing the statements before executing them. I also recommend retaining the list of indices so that they can be removed at a later date, if required.