>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.

When deleting records, the disk I/O is the bottleneck: The amount of RAM is irrelevant as writes need to be written to disk promptly irrigardless of the amount of RAM. Also note that disk I/O is always challenging on VM ware solutions where the disk is shared amongst several virtual servers. 3rd party products exists to optimize that.

In regards of concurrency problems (deadlock) you need to check what your minimum required ISOLATION_LEVEL is. Read_uncommited does not place locks on records, but therefore might let other queries read inconsistent data because during the transaction. SERIALIZABLE is the most concurrency secure settings, but is much more lickely to cause deadlocks on the database. check the bol documentation for more details,

Walter