I ran into a problem when SQL Server causes a significant number of locks (from 95 to 150) in our main table. These are usually short locks lasting less than 3 seconds, but I would like to remove them if possible. We also noticed that usually there are no blocks, but sometimes we have a situation where the blocks seem to be “cascading”, and then the whole system slows down significantly.
Background
We have up to 600 virtual machines that process data, and we loaded the table into SQL so that we can track any stalled records and records that were marked as completed. Usually in our table we have from 200,000 to 1,000,000 records.
What are we trying to accomplish?
We are trying to get the next available record (Status = 0). However, since there may be several hits on the stored process at the same time, we try to make sure that each virtual machine receives a unique record. This is important because processing takes from 1.5 to 2.5 minutes to record, and we want to make it as clean as possible.
Our thought process up 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 caused us several problems with locks, so we reworked the process a bit and changed the place to a subquery to return the top 1 RowID (primary key) from the table. It seemed to help that everything works a little smoother, but then we sometimes load into 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
We found that the presence of a significant number of state records 1 and 2 within the table slows down. We found out that this was from a table scan in the Status column. We added the following index, but this did not help solve the locks.
CREATE NONCLUSTERED INDEX IX_Test_Status_RowID ON [dbo].[Test] ([Status]) INCLUDE ([RowID])
The last step after UPDATE, we use the returned RowID to select the details:
SELECT 'Test' as FileName, *, @Nick as [Nickname] FROM Test WITH (NOLOCK) WHERE RowID IN (SELECT id from @retValue)
Types of locks
Most of the blocks are LCK_M_U and LCK_M_S, which I expect with this UPDATE and SELECT query. Sometimes we had 1 or 2 locks LCK_M_X. This made me think that we might still encounter our “unique” recording code.
Questions
- Are these locks and the number of locks only normal SQL operations for loading this type?
- Does a subquery cause more problems than TOP (1) in UPDATE, where did we start? I'm trying to get confirmation, I can delete the ORDER BY statement and delete this extra processing step.
- Would a different index help? I wondered if updating indexes was a possible cause of locks initially, but now I'm not sure.
- Is there a better or more efficient way to get a unique RowID?
- Could WITH (ROWLOCK) cause more locks than leave it? The idea is that ROWLOCK only locks one particular record and allows another process to update another record and select without locking the table or page.
- Does anyone have any tools they recommend for stress testing and running 100 queries at the same time to test any potential solutions?
Sorry for all the questions, just trying to make sure that I am as clear as possible in our process and the issues that we have.
Thank you in advance for your understanding, as this is a very unpleasant problem for us.
Equipment
We are running SQL Server 2008 R2 on a Dual Xeon processor with 24 GB of RAM. Therefore, we must have a lot of horsepower for this process.