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
    LastCheckedIn = GETUTCDATE(),
    NumberOfCheckIns = NumberOfCheckIns + 1,
    LastCheckInVersion = @Version,
    IPAddress = @IPAddress
    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,

  1. What is the correct approach here.
  2. What science can I place behind this change
  3. How do I calculate / appreciate the impact of this change.
  4. Will SQL ignore hints?
  • From your comment, it would appear adding an index to MachineId will resolve your problem. For future reference, the ROWLOCK hint is just that, a hint. SQL Server may choose to ignore it and escalate to page/table regardless. – Mark Storey-Smith Jul 2 '12 at 12:14

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.

  • very embaressed there is no index on MachineID – M Afifi Jul 2 '12 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.