0

I am in a situation to perform a costly update operation (0.2M records) on a 40M record table. I am using a filter condition on a nvarchar column that does not have a NCI. As per the execution plan, I can see clustered index scan and high IO operator cost which is expected.

I remember somewhere while reading the internals, I read that the storage engine shares the scan results among the sessions scanning the same index so that the engine dont have to do a full scan of the indexes for each session all over again and again. Sorry I forgot that terminology.

enter image description here

update top (@BatchSize) [dbo].[Table]
        set DeleteAfter = GETDATE() 
        where coltypeid in ('nvarchar1','nvarchar2',.....,'nvarchar52') 
        AND DeleteAfter is NULL

I am planning to run the update in batches of 100 records for 3 mins in a 10 min interval during nightly hours which would take me a month to complete this update.

My thought process is, if my above theory about storage engine is valid, then instead of 1 single update batch, if I split the single update as 2 update batch jobs and schedule to run parallelly using the same approach (batches of 100 records for 3 mins in a 10 min interval) will I be able to complete the whole update in 15 days.

I cannot avoid a scan here, but I am basically trying to make the maximum use of the Index scan that is happening. I need your inputs on this before I can go and try a POC.

update top (@BatchSize) [dbo].[Table] set DeleteAfter = GETDATE() 
 where coltypeid in ('nvarchar1','nvarchar2',.....,'nvarchar25') AND DeleteAfter is NULL



 update top (@BatchSize) [dbo].[Table] set DeleteAfter = GETDATE() 
     where coltypeid in ('nvarchar26','nvarchar2',.....,'nvarchar52') AND DeleteAfter is NULL
3
  • 1
    "I am planning to run the update in batches of 100 records for 3 mins in a 10 min interval during nightly hours which would take me a month to complete this update." - Why?...if you're doing it off-hours, just update the 200,000 rows in a single batch. The biggest bottleneck of that process is the scanning of the 40 million row table. You're saying it's happening either way, so why do it multiple times and suffer from that bottleneck more than once? Just get the entire update done in one go. Updating 200,000 rows is not that much, and the entire process may only take about 10-20 minutes.
    – J.D.
    Jul 7, 2023 at 12:26
  • @J.D. - This DB is sitting on a EBS volume which is shared by other DBs are well. I am worried because, executing it in one go will spike the volume Queue length and will have a wider impact Jul 7, 2023 at 12:49
  • But if you're scanning the entire table anyway, even with your small update batch query, it's the same amount of server wide bottleneck. You're probably making it worse by running it multiple times in smaller batches. So you're better off getting the entire update done in one batch, and only scan the table once.
    – J.D.
    Jul 7, 2023 at 13:54

1 Answer 1

2

I read that the storage engine shares the scan results among the sessions scanning the same index so that the engine dont have to do a full scan of the indexes for each session all over again and again. Sorry I forgot that terminology

The term is Advanced Scanning, an Enterprise Editon feature.

I suggest an alternate technique for batching the mass update. Create a table with the clustered index key (plus additional column(s) if your CI key is not unique) of the rows to be updated along with a pre-calculated batch number ordered by clustered index key. Join to this table in the UPDATE query for each batch to leverage the target table clustered index. This way, only rows to be updated will be touched by the UPDATE query, improving performance and concurrency.

Below is an example of this method where ExampleID is the clustered index key. This uses a larger batch size since concurrency shouldn't be concern without the scan.

SET NOCOUNT ON;
DECLARE @BatchSize int = 10000;
DROP TABLE IF EXISTS dbo.RowsToUpdate;
SELECT
      ((ROW_NUMBER() OVER(ORDER BY ExampleID) - 1) / @BatchSize) + 1 AS BatchNumber
     , ExampleID
INTO dbo.RowsToUpdate
FROM dbo.Example
WHERE 
    AND target.coltypeid IN ('nvarchar1','nvarchar2','nvarchar52') 
    AND DeleteAfter is NULL;

CREATE UNIQUE CLUSTERED INDEX cdx ON dbo.RowsToUpdate(BatchNumber, ExampleID);

DECLARE @BatchNumber int = 1;
WHILE @BatchNumber <= (SELECT MAX(BatchNumber) FROM dbo.RowsToUpdate)
BEGIN

    UPDATE dbo.Example
    SET DeleteAfter = GETDATE() 
    FROM dbo.Example AS target
    JOIN dbo.RowsToUpdate AS batch ON batch.ExampleID = target.ExampleID
    WHERE
        batch.BatchNumber = @BatchNumber
        AND target.coltypeid IN ('nvarchar1','nvarchar2','nvarchar52') 
        AND target.DeleteAfter is NULL;

    SET @BatchNumber += 1;

END;
GO
2
  • Hi @Dan Guzman - Thanks for your insight and the script for your approach. I will definitely check that. Wanted to know if my understanding about advanced scanning and my proposed approach in the post is valid- Just to feel happy that I am putting theories into practical use cases :-) Jul 7, 2023 at 13:04
  • @udhayandharmalingam, just speculating, but each UPDATE query will acquire an update lock on each row touched whether it qualifies or not. Concurrent UPDATE statement using the same scan point will likely experience blocking and mitigate potential performance improvement of advanced scanning.
    – Dan Guzman
    Jul 7, 2023 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.