DPM performance

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

The 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.