I'm having some delays when insert into a table. The funny thing is that it doesn't always happen.

Basically it's part of a load test that I created to compare some new features. I run the store procedure for a bunch of lists and there are some lists that always get delayed taking 3 hours to end and other that always are executed in a acceptable time (10 minutes).

At this point, look at performance monitor and the disk is reading at 1mb/s, so there isn't any of high load on the disk.

Also using extended events, look at "waits", saw the main wait is PAGEIOLATCH_EX, but as I said, the disk is reading at low rate, so what happen here?

CREATE TABLE SubscriberByList (
    SubscriberId INT NOT NULL
    ,ListId INT NOT NULL
    ,AddedAt DATETIME NOT NULL DEFAULT GETUTCDATE()
    ,Active BIT DEFAULT 1
    ,CONSTRAINT [PK_SubscriberByList] PRIMARY KEY CLUSTERED (ListId, SubscriberId)
    );

This table has 2.381.451.668 rows. A non clustered index on SubscriberId. SubscriberId and ListId are FK. Also a indexed view to count the amount of subscribers by each list.

On my test environment I created a procedure as following.

CREATE PROCEDURE [dbo].[LoadTest] @ListId INT 
AS
BEGIN
    CREATE TABLE #Subscribers (SubscriberId INT, Active BIT, HashKey as convert(binary(32),HASHBYTES('SHA2_256',convert(varchar,SubscriberId))) PERSISTED,PRIMARY KEY CLUSTERED(HashKey))


    INSERT INTO #Subscribers (SubscriberId, Active)
    SELECT SubscriberId, Active
    FROM dbo.SubscriberByList sxl
    where sxl.ListId = @ListId


    DELETE TOP (1000) dbo.SubscriberByList
    WHERE ListId = @ListId

    WHILE @@ROWCOUNT = 1000
    BEGIN
        DELETE TOP (1000) dbo.SubscriberByList
        WHERE ListId = @ListId
    END

    DECLARE @Offset INT = 0
        ,@Amount INT = 1000
    INSERT INTO dbo.SubscriberByList (SubscriberId, ListId, Active)
    SELECT SubscriberId, @ListId,Active
    FROM (SELECT SubscriberId, Active
        FROM #Subscribers
        ORDER BY HashKey
        OFFSET @Offset ROWS
        FETCH NEXT @Amount ROWS ONLY
        ) x

    WHILE @@ROWCOUNT = @Amount
    BEGIN
        SET @Offset = @Offset + @Amount

        INSERT INTO dbo.SubscriberByList (SubscriberId, ListId, Active)
        SELECT SubscriberId, @ListId,Active
        FROM (SELECT SubscriberId, Active
            FROM #Subscribers
            ORDER BY HashKey
            OFFSET @Offset ROWS
            FETCH NEXT @Amount ROWS ONLY
            ) x
    END

    DROP TABLE #Subscribers
END

The delay is on INSERT INTO dbo.SubscriberByList.

The HashKey is to get the subscribers distributed. As a new test I remove the HashKey and use as PK the SubscriberId. With this change the performance is improved but this isn't as real as distributed insertion and also isn't as expected because it takes 1 hour to complete.

What could happen since one list take 10 minutes to process 2.7 millions of rows and the other with similar amount takes 3 hours (1 hour on the best case)?

Thanks!