I have a stored procedure that is being called very frequently. For some database servers it is as often as 20 times per second. Unfortunately because different clients call this procedure, I can't (without significant rearchitecture of the application) chunk the queries into table updates.
Recently the database performance has fell through the floor, a very simple update takes 30-90 seconds instead of milliseconds.
By far the (looking at resource waits on the servers), Locks contribute the vast vast majority of the waits (its 150x the time of the second contendor).
The stored procedure does some validation then either inserts or updates an existing record (for now all of them are updates). If the validation fails it RAISERROR and the errors is passed all the way back to the client (the main obstacle to why it can't be chunked easily).
The stored procedure is
UPDATE dbo.TblHeartbeat SET LastCheckedIn = GETUTCDATE(), NumberOfCheckIns = NumberOfCheckIns + 1, LastCheckInVersion = @Version, IPAddress = @IPAddress WHERE MachineID = @MachineKey
I suspect that changing this to a WITH(ROWLOCK) will resolve the issue. There doesn't seem to be a lot of science around locks though. Opinions vary from, SQL knows what it's doing don't force it to do something, to it will ignore hints and you should actually remove the page lock from the table to force it to use row locks.
I am looking for,
- What is the correct approach here.
- What science can I place behind this change
- How do I calculate / appreciate the impact of this change.
- Will SQL ignore hints?