1676

Issue:
Large client environments can see a lot of activity on SQL server side that is caused by spWebPolicy-GetCompUsersRequiringUpdate.
This leads to slow response of SDConnex services and denial of service for clients waiting for PBconnex or PBConnex autoboot can occur.

Root Cause:

The policy engine has a thread that wakes up every 100ms and calls a stored procedure (spWebPolicy_GetCompUsersRequiringUpdate) to get a list of computers and users that have changed and require policies to be updated.  We get batches of 20 at a time, and the changes are likely coming from AD.  For each computer/user, we call a function to recalculate the effective policy for this computer/user.  Each recalculation is an expensive operation and makes several database calls.  This is an unnecessary operation which can choke the database for customers with large AD structures.  For customers with more than one SDConnex instance, each instance has this thread running, which places even more load on the database server.

Every time the spWebPolicy_GetCompUsersRequiringUpdate stored procedure returns an empty record set, the thread will back off by 100ms, until it reaches 5 seconds.  I have attached a script that changes the spWebPolicy_GetCompUsersRequiringUpdate stored procedure so that it always returns an empty record set.  This has two effects:

1.    We don’t ever perform the expensive and unnecessary policy recalculation operation.
2.    The thread will back off and only wake up every five seconds to call the stored procedure.

Environment:
7.1  to 7.1sr4 HF6

Fix
This stored procedure is not running in 7.1sr5

For clients that are unable to upgrade to 7.1sr5 ,please run the attached SQL script

Searchwords: Stored procedure, SQL lockup,Expensive query