Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

  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?
share|improve this question
    
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
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
very embaressed there is no index on MachineID –  M Afifi Jul 2 '12 at 11:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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