Снимок на, все еще мертвые блокировки, БЛОКИРОВКА СТРОКИ

SNAPSHOT isolation can only mitigate (some) of the deadlocks involving reads, but it does absolutely nothing to avoid write vs. write deadlocks. If you generate 100k+ rows per hour that is ~30 inserts per second, so the delete scan is pretty much guaranteed to conflict with other write operations. If all you do is Insert, never update, then the delete block, but not deadlock at row level lock, but because the table is big enough and the delete is doing a scan, the engine will likely choose a page lock for the delete, hence probably the deadlock you get.

w/o an index on the entrydate the delete has no choice but to scan the entire table. This sort of tables that get frequently inserted at the top and deleted at the bottom are in fact queues and your should organize them by the entrydate. That means entrydate should probably be the leftmost key in the clustered index. This organization allows for a clear separation of the inserts occuring at on end of the table vs. the deletes occuring at the other end. But this is a rather radical change, specially if you use the statvalueid to read these values. I guess right now you have a clustered index based on an auto-increment field (StatValueId). Also I assume that the entrydate and the statvalueid are correlated. If both assumptions are true, then you should delete base don the statvalueid: find the largest id that is safe to delete, then delete everything on the clustered index left of this id:

declare @statvalueidmax int;
select @statvalueidmax = max(statvalueid) 
 from statvalue with (readpast)
 where entrydate <  dateadd(minute,-60, getdate());

delete statvalue
 where statvalueid <= @statvalueidmax;

There are a number of assumptions I made, they may be wrong. But the gist of the idea is that you have to separate the inserts from the deletes so they don't overlap.

Please re-write your delete query like so:

DECLARE @cutoff_date DATETIME

SET @cutoff_date = dateadd(minute,-60, getdate())

delete statvalue
from statValue
where temporaryStat = 1
and entrydate < @cutoff_date

You will see a cost reduction in your execution plan

Чтобы уменьшить (или избежать) взаимоблокировок, удалите их партиями. с коротким ожиданием между каждым пакетом (с использованием WAITFOR DELAY)

Кроме того, взаимоблокировки могут быть уменьшены за счет наличия покрывающего индекса.

Для этого кода требуется несколько объявлений, и он приведен только в качестве примера (Выполняется на свой страх и риск!

SELECT  @intRowCount = 1,
    @intErrNo = 0

DECLARE @cutoff_date DATETIME

SET @cutoff_date = dateadd(minute,-60, getdate())

SELECT  @intRowsToDelete = COUNT(*) -- Number of rows to be deleted
FROM dbo.statValue 
WHERE temporaryStat = 1
AND  entrydate < @cutoff_date

WHILE @intRowCount > 0 AND @intErrNo = 0
BEGIN

    SET ROWCOUNT @DEL_ROWCOUNT

    delete statvalue
    FROM dbo.statValue 
    WHERE temporaryStat = 1
    AND  entrydate < @cutoff_date

    SELECT  @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

    SET ROWCOUNT 0  -- Reset batch size to "all"

    SELECT  @intRowsToDelete = @intRowsToDelete - @intRowCount

    WAITFOR DELAY '000:00:
 END

Я также включил Иоанна '