Recently, I have created a thread about a dead lock issue in regards to a delete command removing several thousands of records. This had been running for years and started to be on a permanent failure from December 28th. The delete command removes about 4000 to 5000 records every 15 minutes. While the robot was executing this task, from December 28th, SQL Server decided that it had to apply a dead lock which resulted in hits on the Web site having errors until that command was completed.

We tried various items to resolve that issue. The only one that I found which resolved it was to delete in a For/Next loop.

On that thread, someone provided more information later on about the fact that this might have been related to insufficient disk I/O resources being allocated to SQL Server.

Here is more information I have collected today.

The monitoring team reports that SQL server appears to be under-utilized. They say that the disk I/O is higher than the previous environment and it has been confirmed that CPU power is similar. They report however that the database is not fully utilizing the available RAM. Only half of it is presently being utilized. So, they are wondering if this could be the bottleneck.

I have this one on hold for a while as I am trying to collect more information to it. While I have an effective method of removing those records, as fast as before, and not causing any issues like that, it would still be interesting to know if that could be an issue.