SMPDiag Poor performance with spResourceSearchSelectProviderByResourceType

Products

• IT Management Suite

Issue/Introduction

There are few different locations and pickers in the Symantec Management Platform Console that end up using the stored procedure spResourceSearchSelectProviderByResourceType.  In some environments this can experience poor performance. 

A couple of the locations that have been noted are:

• Open the Security Role Manager
• Using the individual computer list in a Filter rule

There are no specific error messages however some of the symptoms that have been noticed are:

• Elevated CPU usage on the SQL server lasting up to 5 minutes
• The locations noted above timing out or taking much longer than expected to load

Cause

This can be made worse by having a large number of resources, roles, or localization strings on a system

Resolution

This has been addressed in Hot Fix 6.

For all versions of 7.5 and also 7.6 through Hot Fix 5, The attached version can be used.

Run the code in SQL Management studio against the Symantec_CMDB or custom named database. This will replace the old version of the stored procedure with the new one.