Atualizando uma tabela com mais de 850 milhões de linhas de dados

Para acelerar as coisas, você pode tentar

• Adicionar uma chave primária a #CSpec.RowID para que você não a verifique a cada iteração
• Altere o CTE para uma tabela temporária com PK adequado. Veja o próximo ponto também
• Adicione um índice em SourceTable1 para corresponder à cláusula CTE WHERE: atualmente, o PK será verificado, o que significa que todas as linhas de SourceTable1 serão verificadas a cada iteração. Todas as 52 milhões de linhas
• SourceTable2.MarketID também não possui um índice, mas não me preocuparia com isso porque é verificado apenas uma vez (pelo que entendi)

Os planos de consulta aqui devem mostrar muitas varreduras porque você tem índices ruins para as operações que está fazendo.

A indexação da tabela de destino parece OK

Outra observação: uniqueidentifier e varchar são escolhas ruins para índices clusterizados (seus PKs aqui): muito amplo, não aumentando, sobrecarga de comparações de coleção, pelo menos

Editar, outra observação (graças a @Marian)

Seu índice clusterizado geralmente é amplo. Cada índice não clusterizado aponta para o índice clusterizado, o que significa um enorme índice NC também

Provavelmente , você poderia obter o mesmo resultado reordenando o PK agrupado.

Postando o SQL final desse processo, para benefício da comunidade

/********************************************************************************************************************
*  Notes: Since this approach executes in a loop inside an explicit transaction, locks will be obtained and         *
*  released for each iteration, thus minimizing impact on other users accessing the same table at the same time.    *
*                                                                                                                   *
*  This process would update 10,000 to 12,000 rows per second, and thus is estimated to run for approximately       *
*  23 hours on production with 850 million rows in Spec_Variant table. However, we can harness the power of         *
*  mutli-threading, by statically defining the @min and @max variable values and then running multiple sessions     *
*  of this update. This will reduce the time required to 23 hours divided by the number of sessions. In other words,* 
*  if we run 8 sessions of this update query parallelly, it should complete in 23/8 ~ 3 hours. If multiple sessions *
*  are possible, then the temp table needs to be created as a global temp table and populated in its own session.   *
*  Additionally, each sessions @max and @min values need to be hard coded,for example, 1-50000, 50001-100000, etc.  *
*********************************************************************************************************************/

-- However, to make this possible, we will have to use...

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- ... this would be the ideal setting to minimize locking. Before using this, we will need to execute
-- ALTER DATABASE MyDatabase
-- SET ALLOW_SNAPSHOT_ISOLATION ON

-- Alternately, if access rights permit, executing 
-- DBCC TRACEON(1211,-1) will disable lock escalation. Else, the TRANSACTION ISOLATION LEVEL can be left at 
-- default (READ COMMITTED), but will not allow us to run multiple sessions.

SET NOCOUNT ON;

-- Check if Temp table already exists and drop if it does
IF EXISTS(
        SELECT NULL 
        FROM tempdb.sys.tables
        WHERE name LIKE '#CSpec%'
      )
BEGIN
    DROP TABLE #CSpec;
END;

-- Create Temp table to assign sequence numbers
CREATE Table #CSpec
    (
    RowID           int PRIMARY KEY,
    ID              uniqueidentifier,
    PriceListDate   VarChar(8),
    ProdClassID     VarChar(10),
    ModelID         VarChar(20),
    MarketID        Varchar(10)
    );

-- Populate temp table 
INSERT INTO #CSpec
SELECT ROW_NUMBER() OVER (ORDER BY MarketID) RowID,
       CS.id, 
       CS.pricelistdate, 
       CS.prodclassid, 
       CS.modelid, 
       CS.marketid 
FROM   dbo.SourceTable2 CS 
WHERE CS.MarketID IS NOT NULL
-- This AND clause will allow this process to be run multiple times in timed sessions and will prevent
-- an attempt to update rows that were already updated in an earlier session. If the process will be run 
-- only once from start to finish, this block can be commented out
AND CS.Id NOT IN 
            (
                SELECT DISTINCT ChassisSpecificationId
                FROM TargetTable
                WHERE VariantType IS NOT NULL AND Visibility IS NOT NULL
            );

-- Declare variables to hold values used for updates
DECLARE @min            int, 
        @max            int,
        @ID             uniqueidentifier,
        @PriceListDate  varchar(8),
        @ProdClassID    varchar(10),
        @ModelID        varchar(20),
        @MarketID       varchar(10);

-- Set minimum and maximum values for looping. See comments in the notes section on top.
SELECT @min = 1,@max = MAX(RowID) From #CSpec;

-- Populate other variables in a loop
WHILE @min <= @max
BEGIN
    BEGIN TRY
    BEGIN TRANSACTION;
    SELECT 
        @ID = ID,
        @PriceListDate = PriceListDate,
        @ProdClassID = ProdClassID,
        @ModelID = ModelID,
        @MarketID = MarketID
    FROM #CSpec
    WHERE RowID = @min;  

-- Use CTE to get relevant values from SourceTable1
    ;WITH CTE AS
    (
    SELECT  V.variantid, 
            V.varianttype, 
            V.visibility,
            MAX(V.PriceListVersion) LatestPriceVersion
    FROM    SourceTable1 V 
    WHERE       V.ModelID = @ModelID
            AND V.ProdClassID = @ProdClassID
            AND V.PriceListDate = @PriceListDate
            AND V.MarketID = @MarketID
    GROUP BY
            V.variantid, 
            V.varianttype, 
            V.visibility
    )

-- Update the TargetTable with the values obtained in the CTE
    UPDATE      SV 
    SET         SV.VariantType = VC.VariantType, 
                SV.Visibility = VC.Visibility
    FROM        spec_variant SV 
    INNER JOIN  CTE VC
    ON          SV.VariantId = VC.VariantId
    WHERE       SV.ChassisSpecificationId = @ID
                AND SV.VariantType IS NULL
                AND SV.Visibility IS NULL;

   -- Check for errors and commit transaction
        IF @@ERROR = 0
            BEGIN
                COMMIT TRANSACTION;
                 -- Increment the value of loop variable
                SET @min = @min+1;
            END
    END TRY
    BEGIN CATCH
        IF @@ERROR <> 0
            BEGIN
                ROLLBACK;
            END
    END CATCH
END
-- Clean up
SET NOCOUNT OFF; 
DROP TABLE #CSpec;