does rowlock hurt?

Question

does rowlock hurt?

Stephen Russell

SSC Enthusiast

Points: 190
More actions
April 25, 2007 at 9:29 am

#175942

I have an app that in the shipping process will have to set cols in a few tables to a virgin status so the app can process.
I have a great deal of code like this:
UPDATE
dbo.t_job_details WITH (ROWLOCK)
SET
checkedinon = null
WHERE
shipment_id = @shipment_id
-- CLEAR SHIPPED AND SORT LABEL STATUS OF THIS SHIPMENT
UPDATE
dbo.t_shipments WITH (ROWLOCK)
SET
shippedon = null,
sort_label = '',
record_status_id = @record_status_routed
WHERE
shipment_id = @shipment_id
The range of shipment_ids will only vary for the day by maybe 5000. These tables only have 1/4 mill rows and are reduced on a monthly basis.
So, is my with rowlock hindering performance if 50 people are doing this process at the same time in the day? Or does it give me the warm fuzzy that it's all good and we just get a mild extra nano second in time for better quality of data?
My latch waits show 7000+ in a 120 second refresh. lock waits = 0 as does timeouts and deadlocks.
TIA
__Stephen

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

As long as you're only actually locking a row, for updates, it should actually perform faster. If you're updating multiple rows, you'll either get an outrageous number of row locks out there and performance will stink, or the server will go ahead and lock a page (or table), bypassing the query hint.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Stephen Russell SSC Enthusiast Points: 190 More actions · Answer 2

OTTOMH # of updates is 1 to 5 to 10 depending on what the table is.

so from your response I'll stick with the rowlock?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Well, the short answer is, it depends. What, you expected yes or no?

Seriously though, I think it'll be OK, but I'd keep a close eye on it. As long as you're looking at 5 rows or less, I think you'll not only be fine, but probably see performance benefits. As you go past that... It depends on how far past, size & number of rows affected, etc. Experimentation & testing would nail that down better than I'll be able to through a posting.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Stephen Russell SSC Enthusiast Points: 190 More actions · Answer 4

What was I thinking!

While I have been in this I have found another table that acts a builder / dependency tracker who's index is killing me.

Thanks for your time.

I am focusing on more data bound issues as of now and will streamline the lock issue later on. It's probably a waste of time for small volume updating.

Thanks for the assistance!