1

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:

enter image description here

Indexes defined on UserActivities Table

enter image description here

Indexes defined on LBrs Table

enter image description here

Multiple update queries running on instance (only once instance on whole server) enter image description here

Waits on Server:

enter image description here

  • What would help the most is adding the plan to PasteThePlan and adding it to the question :). This way we can give better recommendations that apply to your problem. – Randi Vertongen Dec 3 at 18:32
  • @RandiVertongen Thank you, I have added the link for the Execution Plan at the bottom of my question. – Ali Dec 3 at 18:53
  • There is a third take involved, LBrds, that you have not included information on. – David Spillett Dec 5 at 9:53
  • @DavidSpillett Added the snippet for indexes defined on LBrs table. Thanks – Ali Dec 5 at 17:56
4

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).

  • I did not find any scanning on the Table. I have updated the question with Execution Plan. – Ali Dec 3 at 19:19
  • thanks for the input, I have updated the question with indexes defined on the table. As for high number of rows I believe it is correct, I just checked one of the multiple (duplicate queries) it shows the there is a scan going on on UserActivities table where it is reading more than 3 million rows. I'm trying to get the information on what update achieves. – Ali Dec 3 at 20:01
  • It seems this only query running on the instance currently (multiple instances of same query) and the CPU is at 80%. Please see the snippet in my question. I found out it is the rest API queries. – Ali Dec 3 at 20:14
  • David, since there is a huge variation (50k to 3m records) between number of rows updated would adding your suggested solution to the where clause will still be relevant ? – Ali Dec 4 at 15:01
  • If many of those records might already have the correct values, probably. The cost of the check is likely to be very small compared to the cost of the writes it could avoid. 3m row updates is going to take some effort. You could try it and test? – David Spillett Dec 5 at 9:55
1

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

  • thank you very helpful comment. Please pardon my knowledge in performance tuning, I wonder, why the CPU usage is high if it is not a CPU/IO problem? – Ali Dec 6 at 16:12
  • just to add to this the monitoring tool is showing this query as being the culprit for high cpu. – Ali Dec 6 at 18:44
  • 1
    Execution plan tells that this query tries to update 50k rows and first sql server need to find that 50k rows with multiple joins. This operation can be CPU intensive, but based on your wait stats you should first fix your blocking issies and only then focus on CPU time of this query. – Artashes Khachatryan 2 days ago
  • Please also mention what external tool are you using? – Artashes Khachatryan 2 days ago
  • It's called DPA (Database Performance Analyzer) from Solarwinds. – Ali yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.