Multiple instance of Update Query is causing very high Lck_M_U wait thus high CPU

Question

My investigation found the following query is causing high Lck_M_U wait. I noticed there are multiple instance of this query running obviously competing for the same resource. I wonder if there is anything I can do with this query (listed below) to minimize the wait?

UPDATE LBMebrs
                SET DelayedPoints = 0, Points = ISNULL(computed.Points, 0)
                FROM LBMebrs Le1
                LEFT JOIN
                (
                    SELECT lm.UserId, SUM(ua.Points) Points
                    FROM LBMebrs lm
                    INNER JOIN LBrds l ON l.Id = lm.LBrdsId
                    INNER JOIN UserActivities ua ON ua.UserId = lm.UserId
                    WHERE l.Id = @LBrdsId AND ua.Pub BETWEEN l.StartDate AND l.EndDate
                    GROUP BY lm.UserId
                ) computed ON computed.UserId = Le1.UserId
                WHERE Le1.LBrdsId = @LBrdsId

Executions Plan:

https://www.brentozar.com/pastetheplan/?id=SJ0byVETr

Indexes defined on LBMebrs table:

Indexes defined on UserActivities Table

Indexes defined on LBrs Table

Multiple update queries running on instance (only once instance on whole server)

Waits on Server:

Answer 1

causing very high Lck_M_U wait thus high CPU

The locks should not really be causing high CPU use - a thread that is waiting for locks to clear is doing just that: waiting and not consuming processing resource. So waiting for locks a lot is a symptom of the statements taking a long time to run, not a cause of it.

I suspect that the tables are not well indexed for the query that is working out what to update, particularly the derived table sub-query. If each run of this is scanning a large amount of data instead of being able to seek it more efficiently, then that would explain the high CPU cost (and the waiting - each call is waiting for the last to finish that scanning). As suggested in the comments already, posting a sample query plan would help here, as would a list of indexes defined on the relevant tables.

---

Looking at the plan posted after writing the above, I still suspect that better index options will help, add to the question the indexes that are currently defined.

It would also help to describe what the update is trying to achieve.

Is it right that it is updating thousands of rows each time? An estimated 55,979 rows in this instance. If most of the time the values will not change you can at least remove a lot of the page write activity and the need to attain update locks by adding AND (DelayedPoints <> 0 OR Points <> ISNULL(computed.Points, 0)) to the final WHERE, or if DelayedPoints may be NULL in some cases AND (DelayedPoints <> 0 OR DelayedPoints IS NULL OR Points <> ISNULL(computed.Points, 0)). The reason this will help is that SQL Server will perform the updates without checking that they are not necessary, so adding your own check will save it a pile of log and data page updates most times the statement is run (obviously don't do this if you have extra logic such in triggers that is relying on being triggered by these no-op updates).

Answer 2

Your mentioned waits are caused because you are trying concurrently update the same rows which cause blocks. When a transaction performs an update sql server tries to put an "U" lock on the corresponding row(s). If there are no conflicting locks the "U" lock will be set and then converted to "X" - exclusive lock. Remember that there can be only 1 "U" lock on a single recourse at any moment, that means if you have 2 transactions which tries to run this query with same @LBrdsId they will block each other and you will see LCK_M_U waits. This is not CPU or IO or Memory problem, this is typical blocking issue.

The solution is to run this update with different @LBrdsId params. In that case each statement will lock "it own rows" and will not cause blocking.

You can read more about sql server U and X lock here