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)?
EDIT 1: Thanks for your comments, now I realize that delay also happen on the delete! here are some stats using the suggested Paul Randal script, taking 3 minutes on each test
So, on slow operations there are more number of reads/writes, but a small number of bytes. Right?
Also the delete is on the primary key, why take more time for the same amount of subscribers?
Why is that?