How To Detect locking due to multi row updates

Question

Hi we are thinking of moving our updating records operations over from c# to sql, I am afraid of locking problems, how can I detect this problem if it occurs, and if it causes locking, is there any solution other then making small batches

Answer 1

we are thinking of moving our updating records operations over from c# to sql

Typically the performance benefits of multi-row DML outweigh any increased blocking. Plus you should be using transactions when performing a multi-row update from C#, in which case you'll have similar locking footprint to a server-side multi-row update: Every row updated will be locked until the transaction commits.

You don't want to monitor locking, you want to monitor blocking. And it's tracked in the system wait stats, session wait stats, and in query store wait stats, and with the good old Blocked Process Report.

See generally from the docs:

Transaction Locking and Row Versioning Guide

And from Brent Ozar:

Locking and Blocking in SQL Server

and

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

Answer 2

You can use a series of different monitoring tools and queries that actively watch, log, and report blocking issues. The following would be my recommendation of where to start:

1. Adam Machanic's sp_WhoIsActive - This will tell you when a query is locked, how long it's been locked for, what is the query blocking it, and other information about why a query is running slowly. (Note I don't know if it's just me, but I get intermittent issues with trying to access that site. This is an alternative query I built on top of the aforementioned that provides the same information.)

2. Brent Ozar's sp_Blitz Toolset - There's multiple queries in this toolset for similarly related, but different performance issue monitoring things. Among them, you should be able to get a grasp of any blocking issues.

Any of the aforementioned monitoring queries can be routinely scheduled with a SQL Agent job (among other ways) to continually capture and log performance problems.

David Browne's answer has a list of useful ways to monitor performance as well. Specifically I would look into leveraging the Query Store and Wait Stats. Additionally Brent Ozar and Erik Darling have a lot of good blog posts, in general, on database performance analysis and tuning.

My final recommendation, while it isn't a way to directly monitor blocking performance issues, it is a way to be more proactive to prevent them, would be to become intimate with the Execution Plan that is viewable for the queries you write. This intro and this RedGate article should help you get started. The reason I mention it, is the execution plan of a query will tell you upfront of a lot of common potential performance problems with that query. It can be your guiding map to nipping potential performance issues of your queries in the bud before they actually get a chance to become an issue.