Optimising a query using hint locks 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?
Before using hints, I would check the indexing on the table you are updating; it's probable you want a clustered index on MachineID; without it, an update may be forced to lock the entire table to update one row, which may be what you are seeing. Also examine any triggers on the table, make sure they are optimized. 

Post your table schema, or execution plan for the update if you can.