Diferença nas duas primeiras abordagens

O primeiro plano gasta cerca de 7 dos 10 segundos no operador Window Spool, então esta é a principal razão pela qual é tão lento. Ele está executando muita E/S no tempdb para criar isso. Minhas estatísticas de E/S e tempo são assim:

Table 'Worktable'. Scan count 1000001, logical reads 8461526
Table 'Table_1'. Scan count 1, logical reads 2609
Table 'Worktable'. Scan count 0, logical reads 0

 SQL Server Execution Times:
   CPU time = 8641 ms,  elapsed time = 8537 ms.

O segundo plano é capaz de evitar o carretel e, portanto, a mesa de trabalho inteiramente. Ele simplesmente pega as 10 primeiras linhas do índice clusterizado e, em seguida, faz uma junção de loops aninhados à agregação (soma) que sai de uma varredura de índice clusterizada separada. O lado interno ainda acaba lendo a tabela inteira, mas a tabela é muito densa, então isso é razoavelmente eficiente com um milhão de linhas.

Table 'Table_1'. Scan count 11, logical reads 26093
 SQL Server Execution Times:
   CPU time = 1563 ms,  elapsed time = 1671 ms.

Melhorando a performance

Armazenamento de colunas

Se você realmente deseja a abordagem de "relatórios on-line", o columnstore provavelmente é sua melhor opção.

ALTER TABLE [dbo].[Table_1] DROP CONSTRAINT [PK_Table_1];

CREATE CLUSTERED COLUMNSTORE INDEX [PK_Table_1] ON dbo.Table_1;

Então esta consulta é ridiculamente rápida:

SELECT TOP 10
    seq, 
    value, 
    SUM(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING)
FROM dbo.Table_1
ORDER BY seq DESC;

Aqui estão as estatísticas da minha máquina:

Table 'Table_1'. Scan count 4, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 3319
Table 'Table_1'. Segment reads 1, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0

 SQL Server Execution Times:
   CPU time = 375 ms,  elapsed time = 205 ms.

Você provavelmente não vai superar isso (a menos que você seja realmente inteligente - bom, Joe). O Columnstore é muito bom em escanear e agregar grandes quantidades de dados.

Usando ROWa RANGEopção de função de janela

Você pode obter um desempenho muito semelhante à sua segunda consulta com essa abordagem, que foi mencionada em outra resposta e que usei no exemplo columnstore acima ( plano de execução ):

SELECT TOP 10
    seq, 
    value, 
    SUM(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING)
FROM dbo.Table_1
ORDER BY seq DESC;

Isso resulta em menos leituras do que sua segunda abordagem e nenhuma atividade tempdb versus sua primeira abordagem porque o spool da janela ocorre na memória :

...RANGE usa um spool no disco, enquanto ROWS usa um spool na memória

Infelizmente, o tempo de execução é quase o mesmo que sua segunda abordagem.

Table 'Worktable'. Scan count 0, logical reads 0
Table 'Table_1'. Scan count 1, logical reads 2609
Table 'Worktable'. Scan count 0, logical reads 0

 SQL Server Execution Times:
   CPU time = 1984 ms,  elapsed time = 1474 ms.

Solução baseada em esquema: totais em execução assíncronos

Como você está aberto a outras ideias, considere atualizar o "total em execução" de forma assíncrona. Você pode obter periodicamente os resultados de uma dessas consultas e carregá-los em uma tabela de "totais". Então você faria algo assim:

CREATE TABLE [dbo].[Table_1_Totals]
(
    [seq] [int] NOT NULL,
    [running_total] [bigint] NOT NULL,
    CONSTRAINT [PK_Table_1_Totals] PRIMARY KEY CLUSTERED ([seq])
);

Carregue-o todos os dias / horas / o que for (isso levou cerca de 2 segundos na minha máquina com linhas de 1 mm e pode ser otimizado):

INSERT INTO dbo.Table_1_Totals
SELECT
    seq, 
    SUM(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING) as total
FROM dbo.Table_1 t
WHERE NOT EXISTS (
            SELECT NULL 
            FROM dbo.Table_1_Totals t2
            WHERE t.seq = t2.seq)
ORDER BY seq DESC;

Então sua consulta de relatórios é muito eficiente:

SELECT TOP 10
    t.seq, 
    t.value, 
    t2.running_total
FROM dbo.Table_1 t
    INNER JOIN dbo.Table_1_Totals t2
        ON t.seq = t2.seq
ORDER BY seq DESC;

Aqui estão as estatísticas de leitura:

Table 'Table_1'. Scan count 0, logical reads 35
Table 'Table_1_Totals'. Scan count 1, logical reads 3

Solução baseada em esquema: totais em linha com restrições

Uma solução realmente interessante para isso é abordada em detalhes nesta resposta à pergunta: Escrevendo um esquema bancário simples: Como devo manter meus saldos sincronizados com o histórico de transações?

A abordagem básica seria rastrear o total de execução atual em linha junto com o total de execução anterior e o número de sequência. Em seguida, você pode usar restrições para validar que os totais em execução estejam sempre corretos e atualizados.

Crédito a Paul White por fornecer uma implementação de amostra para o esquema nesta sessão de perguntas e respostas:

CREATE TABLE dbo.Table_1
(
    seq integer IDENTITY(1,1) NOT NULL,
    val bigint NOT NULL,
    total bigint NOT NULL,

    prev_seq integer NULL,
    prev_total bigint NULL,

    CONSTRAINT [PK_Table_1] 
        PRIMARY KEY CLUSTERED (seq ASC),

    CONSTRAINT [UQ dbo.Table_1 seq, total]
        UNIQUE (seq, total),

    CONSTRAINT [UQ dbo.Table_1 prev_seq]
        UNIQUE (prev_seq),

    CONSTRAINT [FK dbo.Table_1 previous seq and total]
        FOREIGN KEY (prev_seq, prev_total) 
        REFERENCES dbo.Table_1 (seq, total),

    CONSTRAINT [CK dbo.Table_1 total = prev_total + val]
        CHECK (total = ISNULL(prev_total, 0) + val),

    CONSTRAINT [CK dbo.Table_1 denormalized columns all null or all not null]
        CHECK 
        (
            (prev_seq IS NOT NULL AND prev_total IS NOT NULL)
            OR
            (prev_seq IS NULL AND prev_total IS NULL)
        )
);

Eu recomendo testar com um pouco mais de dados para ter uma ideia melhor do que está acontecendo e ver como as diferentes abordagens funcionam. Carreguei 16 milhões de linhas em uma tabela com a mesma estrutura. Você pode encontrar o código para preencher a tabela na parte inferior desta resposta.

A abordagem a seguir leva 19 segundos na minha máquina:

SELECT TOP (10) seq
    ,value
    ,sum(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING) total
FROM dbo.[Table_1_BIG]
ORDER BY seq DESC;

Plano real aqui . A maior parte do tempo é gasto calculando a soma e fazendo a classificação. Preocupantemente, o plano de consulta faz quase todo o trabalho para todo o conjunto de resultados e filtra as 10 linhas que você solicitou no final. O tempo de execução dessa consulta é dimensionado com o tamanho da tabela em vez de com o tamanho do conjunto de resultados.

Esta opção leva 23 segundos na minha máquina:

SELECT *
    ,(
        SELECT SUM(value)
        FROM dbo.[Table_1_BIG]
        WHERE seq <= t.seq
        ) total
FROM (
    SELECT TOP (10) seq
        ,value
    FROM dbo.[Table_1_BIG]
    ORDER BY seq DESC
    ) t
ORDER BY seq DESC;

Plano real aqui . Essa abordagem é dimensionada com o número de linhas solicitadas e o tamanho da tabela. Quase 160 milhões de linhas são lidas da tabela:

Para obter resultados corretos, você deve somar as linhas de toda a tabela. Idealmente, você realizaria essa soma apenas uma vez. É possível fazer isso se você mudar a maneira como aborda o problema. Você pode calcular a soma de toda a tabela e subtrair um total das linhas no conjunto de resultados. Isso permite que você encontre a soma da enésima linha. Uma maneira de fazer isso:

SELECT TOP (10) seq
,value
, [value]
    - SUM([value]) OVER (ORDER BY seq DESC ROWS UNBOUNDED PRECEDING)
    + (SELECT SUM([value]) FROM dbo.[Table_1_BIG]) AS total
FROM dbo.[Table_1_BIG]
ORDER BY seq DESC;

Plano real aqui . A nova consulta é executada em 644 ms na minha máquina. A tabela é varrida uma vez para obter o total completo e, em seguida, uma linha adicional é lida para cada linha no conjunto de resultados. Não há classificação e quase todo o tempo é gasto calculando a soma na parte paralela do plano:

Se você quiser que essa consulta seja ainda mais rápida, basta otimizar a parte que calcula a soma completa. A consulta acima faz uma verificação de índice clusterizado. O índice clusterizado inclui todas as colunas, mas você só precisa da [value]coluna. Uma opção é criar um índice não clusterizado nessa coluna. Outra opção é criar um índice columnstore não clusterizado nessa coluna. Ambos irão melhorar o desempenho. Se você estiver no Enterprise, uma ótima opção é criar uma visualização indexada como a seguinte:

CREATE OR ALTER VIEW dbo.Table_1_BIG__SUM
WITH SCHEMABINDING
AS
SELECT SUM([value]) SUM_VALUE
, COUNT_BIG(*) FOR_U
FROM dbo.[Table_1_BIG];

GO

CREATE UNIQUE CLUSTERED INDEX CI ON dbo.Table_1_BIG__SUM (SUM_VALUE);

Essa exibição retorna uma única linha, portanto, quase não ocupa espaço. Haverá uma penalidade ao fazer DML, mas não deve ser muito diferente da manutenção do índice. Com a visualização indexada em jogo, a consulta agora leva 0 ms:

Plano real aqui . A melhor parte dessa abordagem é que o tempo de execução não é alterado pelo tamanho da tabela. A única coisa que importa é quantas linhas são retornadas. Por exemplo, se você obtiver as primeiras 10.000 linhas, a consulta agora leva 18 ms para ser executada.

Código para preencher a tabela:

DROP TABLE IF EXISTS dbo.[Table_1_BIG];

CREATE TABLE dbo.[Table_1_BIG] (
    [seq] [int] NOT NULL,
    [value] [bigint] NOT NULL
);

DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);

INSERT INTO #t WITH (TABLOCK)
SELECT TOP (4000) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

INSERT INTO dbo.[Table_1_BIG] WITH (TABLOCK)
SELECT t1.ID * 4000 + t2.ID, 8 * t2.ID + t1.ID
FROM (SELECT TOP (4000) ID FROM #t) t1
CROSS JOIN #t t2;

ALTER TABLE dbo.[Table_1_BIG]
ADD CONSTRAINT [PK_Table_1] PRIMARY KEY ([seq]);

Ao lidar com um subconjunto tão pequeno de linhas retornadas, a junção triangular é uma boa opção. No entanto, ao usar funções de janela, você tem mais opções que podem aumentar seu desempenho. A opção padrão para a opção de janela é RANGE, mas a opção ideal é ROWS. Esteja ciente de que a diferença não está apenas no desempenho, mas nos resultados também quando há empates.

O código a seguir é um pouco mais rápido do que os que você apresentou.

SELECT TOP 10 seq
    ,value
    ,sum(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING) total
FROM Table_1
ORDER BY seq DESC