Multiple instance of Update Query is causing very high Lck_M_U wait thus high CPU 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:
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 &lt;&gt; 0 OR Points &lt;&gt; ISNULL(computed.Points, 0)) to the final WHERE, or if DelayedPoints may be NULL in some cases AND (DelayedPoints &lt;&gt; 0 OR DelayedPoints IS NULL OR Points &lt;&gt; 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).