Esto es esencialmente un problema de brechas e islas, sin embargo, es atípico. Corté el ejemplo al mínimo. Necesito identificar brechas que excedan un cierto umbral y los duplicados no pueden ser un problema, aunque este ejemplo los elimina.
En cualquier caso, la solución común de usar ROW_NUMBER () no ayuda, ya que las brechas de incluso 1 no se pueden manejar y el valor de la brecha es un parámetro en la 'vida real'.

El siguiente código realmente funciona correctamente. ¡Y es súper rápido! Pero si lo miras, verás por qué las personas son bastante tímidas para confiar en él. El método se publicó por primera vez hace 9 años aquíhttp: //www.sqlservercentral.com/articles/T-SQL/68467 y he leído las 32 páginas de comentarios. Nadie ha hecho agujeros con éxito, excepto para decir "no es un comportamiento documentado". Lo he probado en todas las versiones de 2005 a 2019 y funciona.

La pregunta es, más allá de usar un cursor o un bucle while para mirar muchos millones de filas 1 por 1, lo que lleva, no sé cuánto tiempo porque cancelo después de 30 minutos. - ¿Hay alguna forma 'compatible' de obtener los mismos resultados en un tiempo razonable? ¡Incluso 100 veces más lento completaría 4M filas en 10 minutos y no puedo encontrar una manera de acercarme a eso!

CREATE TABLE #t (CreateDate   date not null
                ,TufpID       int not null
                ,Cnt          int not null
                ,FuzzyGroup   int null);
ALTER TABLE #t ADD CONSTRAINT PK_temp PRIMARY KEY CLUSTERED (CreateDate,TufpID);

-- Takes 40 seconds to write 4.4M rows from a source of 70M rows.
INSERT INTO #T
    SELECT X.CreateDate
          ,X.TufpID
          ,Cnt          = COUNT(*)
          ,FuzzyGroup   = null
      FROM SessionState SS
     CROSS APPLY(VALUES (CAST(SS.CreateDate as date),SS.TestUser_Form_Part_id)) X(CreateDate,TufpID)
     GROUP BY X.CreateDate
             ,X.TufpID
 ORDER BY x.CreateDate,x.TufpID;

-- Takes 6 seconds to update 4.4M rows.  They WILL update in clustered index order!
-- (Provided all the rules are followed - see the link above)
DECLARE @FuzzFactor int = 38 
DECLARE @Prior      int = -@FuzzFactor; -- Insure 1st row has it's own group
DECLARE @Group      int;
DECLARE @CDate      date;
UPDATE #T
   SET @Group = FuzzyGroup  = CASE WHEN t.TufpID - @PRIOR < @FuzzFactor AND t.CreateDate = @CDate
                                   THEN @Group ELSE t.TufpID END
      ,@CDate               = CASE WHEN @CDate = t.CreateDate THEN @CDate ELSE t.CreateDate END
      ,@Prior               = CASE WHEN @Prior = t.TufpID-1   THEN @Prior + 1 ELSE t.TufpID END
  FROM #t t WITH (TABLOCKX) OPTION(MAXDOP 1);

Después de lo anterior, la columna FuzzyGroup contiene el valor más bajo de TufpID en el grupo. IOW la primera fila (en orden de índice agrupado) contiene el valor de su propia columna TufpID. A partir de entonces, cada fila obtiene el mismo valor hasta que la fecha cambia o se supera un tamaño de espacio (en este caso 38). En esos casos, el TufpID actual se convierte en el valor puesto en FuzzyGroup hasta que se detecta otro cambio. Entonces, después de 6 segundos, puedo ejecutar consultas de ese grupo por FuzzyGroup y analizar las islas.

En la práctica, también hago algunos recuentos y totales en la misma pasada, por lo que me lleva 8 segundos, no 6, pero podría hacer esas cosas con funciones de ventana con bastante facilidad si lo necesito, así que las dejé.

Esta es la tabla más pequeña y eventualmente necesitaré manejar 100M de filas. Por lo tanto, 10 minutos para 4.4M probablemente no sea lo suficientemente bueno, pero es un lugar para comenzar.