I need to write a store procedure to update link reference in my DB. Link can be contained in few nvarchar field that contains JSONs (that could contains some urls).

To do so i update the tables in batches of 8129 items per iteration, so that the machine will not hang (in theory).

But now the code seems to hang anyway, it will not print any message and the procedure keep running (without affecting any data) for many minutes, untill i have to kill the procedure (that in meanwhile seems to haven't affect any data).

If i try to use the same logic on a toy example, i get no issue, so i think my problem is due the fact the table is big (few hundred thousand of rows).

Here the minimal example that is working, the exact same code on bigger table hang apparently doing nothing (tested with SQL Server 2019).

Example :

    CREATE TABLE [dbo].[tbl_ana_Articles](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[ID_BRAND] [int] NOT NULL,
    	[CODE] [nvarchar](40) NOT NULL,
    	[CODFOR] [nvarchar](40) NOT NULL,
    	[COD_ALT01] [nvarchar](50) NOT NULL,
    	[COD_ALT02] [nvarchar](50) NOT NULL,
    	[COD_ALT03] [nvarchar](50) NOT NULL,
    	[ID_UOM] [int] NOT NULL,
    	[IS_ACTIVE] [bit] NOT NULL,
    	[_ATTRIBUTES] [nvarchar](max) NOT NULL,
    	[_DOCUMENTS] [nvarchar](max) NOT NULL,
    	[_SEO] [nvarchar](max) NOT NULL,
    	[_TRANSLATIONS] [nvarchar](max) NOT NULL,
    	[_TAGS] [nvarchar](max) NOT NULL,
    	[_NOTES] [nvarchar](max) NOT NULL,
    	[_METADATA] [nvarchar](max) NOT NULL,
    	[IS_B2B] [bit] NOT NULL,
    	[IS_B2C] [bit] NOT NULL,
    	[IS_PROMO] [bit] NOT NULL,
    	[IS_NEWS] [bit] NOT NULL,
    	[CAN_BE_RETURNED] [bit] NOT NULL,
    	[IS_SHIPPABLE] [bit] NOT NULL,
    	[HAS_SHIPPING_COSTS] [bit] NOT NULL,
    	[IS_PURCHEASABLE] [bit] NOT NULL,
     CONSTRAINT [PK_tbl_ana_articles] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO



    INSERT INTO [dbo].[tbl_ana_Articles]
               ([ID_BRAND]
               ,[CODE]
               ,[CODFOR]
               ,[COD_ALT01]
               ,[COD_ALT02]
               ,[COD_ALT03]
               ,[ID_UOM]
               ,[IS_ACTIVE]
               ,[_ATTRIBUTES]
               ,[_DOCUMENTS]
               ,[_SEO]
               ,[_TRANSLATIONS]
               ,[_TAGS]
               ,[_NOTES]
               ,[_METADATA]
               ,[IS_B2B]
               ,[IS_B2C]
               ,[IS_PROMO]
               ,[IS_NEWS]
               ,[CAN_BE_RETURNED]
               ,[IS_SHIPPABLE]
               ,[HAS_SHIPPING_COSTS]
               ,[IS_PURCHEASABLE])
         VALUES
               (1
               ,'COD1'
               ,'SUPPLIER1'
               ,'CATEGORY1'
               ,'CATEGORY1-BIS'
               ,'CATEGORY2'
               ,1
               ,1
               ,'{ "url" : "https://old.com" }'
               ,''
               ,''
               ,''
               ,''
               ,''
               ,''
               ,1
               ,0
               ,0
               ,0
               ,1
               ,1
               ,0
               ,1);
               
        DECLARE @FullOldUrl AS NVARCHAR(50) = 'https://old.com';
        DECLARE @FullNewUrl AS NVARCHAR(50) = 'https://new.com';
    
        --ARTICLE
        PRINT('updating articles');
        WHILE 1=1
        BEGIN
            UPDATE TOP (8196) [dbo].[tbl_ana_Articles]
               SET [_ATTRIBUTES]    = REPLACE([_ATTRIBUTES]   , @FullOldUrl, @FullNewUrl)
                  ,[_DOCUMENTS]     = REPLACE([_DOCUMENTS]    , @FullOldUrl, @FullNewUrl)
                  ,[_SEO]           = REPLACE([_SEO]          , @FullOldUrl, @FullNewUrl)
                  ,[_TRANSLATIONS]  = REPLACE([_TRANSLATIONS] , @FullOldUrl, @FullNewUrl)
                  ,[_TAGS]          = REPLACE([_TAGS]         , @FullOldUrl, @FullNewUrl)
                  ,[_NOTES]         = REPLACE([_NOTES]        , @FullOldUrl, @FullNewUrl)
            WHERE 
                [_ATTRIBUTES]   like '%' + @FullOldUrl + '%' OR
                [_DOCUMENTS]    like '%' + @FullOldUrl + '%' OR
                [_SEO]          like '%' + @FullOldUrl + '%' OR
                [_TRANSLATIONS] like '%' + @FullOldUrl + '%' OR
                [_TAGS]         like '%' + @FullOldUrl + '%' OR
                [_NOTES]        like '%' + @FullOldUrl + '%'
    
            IF (@@ROWCOUNT <= 0)
            BEGIN
                BREAK;
            END
        END
        
        SELECT * FROM [dbo].[tbl_ana_Articles]
        PRINT('Finished');

Here the execution plan produced by the toy example (i can't get the execution plan of real scenario).

https://www.brentozar.com/pastetheplan/?id=SJhVTcMTo

I am really puzzled about what caused that issue 

--EDIT : 
i have run the procedure again, and discovered that if i let run long enough  (~30 min) i get the right behaviour. So apparently i have a performance issue here. I am not really intereseted in performance here, because the procedure is used only rarely (manually) during mantainance (only if the site change domain).

But i am curious if I made some blunder here, to get such low performance