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.

Web console alert links

The direct links to alert pages in the SCOM web console vary from version to version. The below describes the URL formats. Replace <Alert GUID> with the GUID to the alert.

SCOM 2012 (R2)

http://server/OperationsManager/#/web(url='/monitoringview/default.aspx?DisplayMode=Pivot&AlertID=<Alert GUID>')

SCOM 2016

http://server/MonitoringView/default.aspx/#/web(url='/OperationsManager/default.aspx?DisplayMode=Pivot&AlertID=<Alert GUID>')

SCOM 1801

http://server/OperationsManager/#/monitoring/drilldown/alert/<Alert GUID>

Management pack import results in a DispatcherService timeout

<Management pack name> could not be imported.

If any management packs in the Import list are dependent on this management pack, the installation of the dependent management packs will fail.

System.TimeoutException: The requested operation timed out. —> System.TimeoutException: This request operation sent to net.tcp://<hostname>:5724/DispatcherService did not receive a reply within the configured timeout (00:29:59.5368709). The time allotted to this operation may have been a portion of a longer timeout. This may be because the service is still processing the operation or because the service was unable to send a reply message. Please consider increasing the operation timeout (by casting the channel/proxy to IContextChannel and setting the OperationTimeout property) and ensure that the service is able to connect to the client.

I recently encountered the above error when importing a custom (sealed) management pack, and struggled with it for quite some time. Was the cause that the management pack was simply too large? Was there a SQL Server performance problem? Perhaps there was a timeout config error on the management server?

Eventually I traced the problem to an error in the <Resources> section of the management pack: there were references to files that didn’t exist.

<Image ID=”DistributedApplications.Image.Crepe16″ Accessibility=”Public” FileName=”Crêpe1680.png” />
<Image ID=”DistributedApplications.Image.Crepe80″ Accessibility=”Public” FileName=”Crêpe8080.png” />

The mistake was that files by the names specified weren’t present in the management pack; this was simply a typo. I would have thought this would cause an error to be thrown in the console, but instead SCOM attempts to import the management pack, appears to silently fail part-way through the process, then the SDK service waits for the above timeout. The solution was to simply provide the correct file names.