Trouble with SQL Server locks

Question

I am running into an issue where SQL Server is causing a significant number of locks (95 to 150) on our main table. They are typically short duration locks, lasting under 3 seconds, but I would like to eliminate those if I possibly can. We have also noticed that typically there are no blocks, but occasionally we have a situation where the blocks seem to "cascade" and then the entire system slows down considerably.

Background

We have up to 600 virtual machines processing data and we loaded a table in SQL so we could monitor any records that got stalled and records that were marked complete. We typically have between 200,000 and 1,000,000 records in this table during our processing.

What we are trying to accomplish

We are attempting to get the next available record (Status = 0). However, since there can be multiple hits on the stored proc simultaneously, we are trying to make sure each VM gets a unique record. This is important because processing takes between 1.5 and 2.5 minutes per record and we want to make this as clean as possible.

Our thought process to this point

UPDATE TOP (1) dbo.Test WITH (ROWLOCK)
SET Status = 1, 
    VMID = @VMID, 
    ReadCount = ReadCount + 1,
    ProcessDT = GETUTCDATE()
OUTPUT INSERTED.RowID INTO @retValue
WHERE Status = 0

This update was causing us a few issues with locks, so we re-worked the process a little bit and changed the where to a sub-query to return the top 1 RowID (primary key) from the table. This seemed to help things run a little bit smoother, but then we occasionally get over-loaded in the database again.

UPDATE TOP (1) dbo.Test WITH (ROWLOCK)
SET Status = 1, 
    VMID = @VMID, 
    ReadCount = ReadCount + 1,
    ProcessDT = GETUTCDATE()
OUTPUT INSERTED.RowID INTO @retValue
-- WHERE Status = 0
WHERE RowID IN (SELECT TOP 1 RowID FROM do.Test WHERE Status = 0 ORDER BY RowID)

We discovered that having a significant number of Status 1 and 2 records int he table causes slowdowns. We figured it was from a table scan on the Status column. We added the following index but it did not help solve the locks.

CREATE NONCLUSTERED INDEX IX_Test_Status_RowID
ON [dbo].[Test] ([Status])
INCLUDE ([RowID])

The final step after the UPDATE, we use the RowID returned to select out the details:

SELECT 'Test' as FileName, *, @Nick as [Nickname] 
FROM Test WITH (NOLOCK)
WHERE RowID IN (SELECT id from @retValue)

Types of locks

The majority of the blocks are LCK_M_U and LCK_M_S, which I would expect with that UPDATE and SELECT query. We did have 1 or 2 LCK_M_X locks as well occasionally. That made me think we may still be getting collisions on our "unique" record code.

Questions

1. Are these locks and the number of locks just normal SQL operations for this type load?
2. Is the sub-query causing more issues than a TOP(1) in the UPDATE we started with? I am trying to get confirmation I can remove the ORDER BY statement and remove that extra step of processing.
3. Would a different index help? I wondered if the index updating was a possible cause of the locks initially, but now I am not sure.
4. Is there a better or more efficient way to get a unique RowID?
5. Is the WITH (ROWLOCK) causing more locks than leaving it off would cause? The idea is ROWLOCK would only lock the 1 specific record and allow another proc to update another record and select without locking the table or page.
6. Does anyone have any tools they recommend to stress test and run 100 queries simultaneously in order to test any potential solutions?

Sorry for all the questions, just trying to make sure I am as clear as possible on our process and the questions we have.

Thanks in advance for any insight as this is a really frustrating issue for us.

Hardware

We are running SQL Server 2008 R2 on a Dual Xeon CPU with 24 GB of RAM. So we should have plenty of horsepower for this process.

Answer 1

It looks like the best solution to the issue was to create a separate table with an identity and use the @@IDENTITY from the insert to determine the next row to process. That has solved all my lock issues so far in my stress testing. Thanks to all who pointed my in the right direction!