Slow execution of stored procedures related to resource assignment results in degraded NSE processing performance

Products

• Client Management Suite

Issue/Introduction

The exhibited behavior is that NSEs coming to the Event Queue seems to be processed slower than usual. This degradation remains until the NS services need to be restarted.  Also the SMP Console is extremely slow and takes 30 - 60 seconds to open a page.   

While looking at what is been processed and getting stuck, you may noticed that it is constantly getting most of the execution time under the "spAssignResourcesToTypedScopeCollection" stored procedure during "delete" and "merge" processing of the incoming NSEs. 

Environment

ITMS 8.7.2 and 8.7.3

Cause

The merge and delete statement query in "spAssign.." procedures is causing the NSEs not to be effectively processed.

Resolution

This issue has been fixed in ITMS 8.8 Release.

A pointfix is current available for ITMS 8.7.3 in the following KB article: CUMULATIVE POST ITMS 8.7.3 POINT FIXES  (starting with "SMA_SMP_8_7_3_PF_v5" and later).

We fixed an issue around "spAssignResourcesToTypedScopeCollection" stored procedure been slow during delete / merge processes. Updated "spAssign.." procedures and updated triggers for ScopeMembership insert/delete has been provided as part of the cumulative pointfix mentioned above.

Also, there is an additional fix available for a similar issue described in the following KB "Slow NSE processing. Unspecified Dataloader Exception encountered for event data class"