Why SQL Server’s ROWLOCK Hint Doesn’t Always Just Lock Rows
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I’m currently migrating over 700 million rows on an isolated server from one table to another. This case is entirely the opposite of your, I’m using with (tablockx) to reduce the number of row locks as this will run the system out of locks fairly quickly. Lock escalation doesn’t actually help in this case because the row locks appear to have remained after the escalation to a table lock. (I wasn’t about to repeat the experience to prove it on a 15TB DB). Part of the problem is that a few million rows in the source already exist in the target and would cause duplicates if I disable the indexes. (Don’t ask, I get the fun ones.)
So I just take the lock on the entire target table. This is a migration on a server which has no users or transactions otherwise.
Had I needed to do this on a live server it would have been considerably different code.
I’ve run into the start issue before with other applications that started before the database was up when both were on the same machine. What I did was set the dependencies on the services properly instead and avoided the “delayed start” setting entirely. That could help but I don’t have a SQL Server 2022 set up yet to verify.
That is an entirely reasonable choice. I’ve run into a variety of situations where skipping row and page locks entirely makes things faster.