Estou tentando obter um pouco mais de desempenho de uma consulta que está acessando uma tabela com ~ 250 milhões de registros. Da minha leitura do plano de execução real (não estimado), o primeiro gargalo é uma consulta que se parece com isso:

select
    b.stuff,
    a.added,
    a.value
from
    dbo.hugetable a
    inner join
    #smalltable b on a.fk = b.pk
where
    a.added between @start and @end;

Veja mais abaixo as definições das tabelas e índices envolvidos.

O plano de execução indica que um loop aninhado está sendo usado em #smalltable, e que a varredura de índice sobre enorme tabela está sendo executada 480 vezes (para cada linha em #smalltable). Isso parece inverso para mim, então tentei forçar uma junção de mesclagem a ser usada:

select
    b.stuff,
    a.added,
    a.value
from
    dbo.hugetable a with(index = ix_hugetable)
    inner merge join
    #smalltable b with(index(1)) on a.fk = b.pk
where
    a.added between @start and @end;

O índice em questão (veja abaixo a definição completa) abrange as colunas fk (o predicado de junção), adicionado (usado na cláusula where) e id (inútil) em ordem crescente e inclui value .

Quando faço isso, no entanto, a consulta passa de 2 1/2 minutos para mais de 9. Eu esperava que as dicas forçassem uma junção mais eficiente que fizesse apenas uma única passagem por cada tabela, mas claramente não.

Qualquer orientação é bem-vinda. Informações adicionais fornecidas, se necessário.

Atualização (2011/06/02)

Tendo reorganizado a indexação na tabela, fiz incursões significativas de desempenho, mas encontrei um novo obstáculo quando se trata de resumir os dados na tabela enorme. O resultado é um resumo por mês, que atualmente se parece com o seguinte:

select
    b.stuff,
    datediff(month, 0, a.added),
    count(a.value),
    sum(case when a.value > 0 else 1 end) -- this triples the running time!
from
    dbo.hugetable a
    inner join
    #smalltable b on a.fk = b.pk
group by
    b.stuff,
    datediff(month, 0, a.added);

Atualmente, o enormetable tem um índice clusterizado pk_hugetable (added, fk)(a chave primária) e um índice não clusterizado indo para o outro lado ix_hugetable (fk, added).

Sem a 4ª coluna acima, o otimizador usa uma junção de loop aninhado como antes, usando #smalltable como a entrada externa e uma busca de índice não clusterizada como o loop interno (executando 480 vezes novamente). O que me preocupa é a disparidade entre as linhas estimadas (12.958,4) e as linhas reais (74.668.468). O custo relativo dessas buscas é de 45%. No entanto, o tempo de execução é inferior a um minuto.

Com a 4ª coluna, o tempo de execução aumenta para 4 minutos. Desta vez, ele busca no índice clusterizado (2 execuções) pelo mesmo custo relativo (45%), agrega por meio de uma correspondência de hash (30%), depois faz uma junção de hash em #smalltable (0%).

Estou inseguro quanto ao meu próximo curso de ação. Minha preocupação é que nem a pesquisa de intervalo de datas nem o predicado de junção sejam garantidos ou mesmo tudo o que provavelmente reduzirá drasticamente o conjunto de resultados. O intervalo de datas na maioria dos casos apenas cortará talvez 10-15% dos registros, e a junção interna em fk pode filtrar talvez 20-30%.

Conforme solicitado pelo Will A, os resultados de sp_spaceused:

name      | rows      | reserved    | data        | index_size  | unused
hugetable | 261774373 | 93552920 KB | 18373816 KB | 75167432 KB | 11672 KB

#smalltable é definido como:

create table #endpoints (
    pk uniqueidentifier primary key clustered,
    stuff varchar(6) null
);

Enquanto dbo.hugetable é definido como:

create table dbo.hugetable (
    id uniqueidentifier not null,
    fk uniqueidentifier not null,
    added datetime not null,
    value decimal(13, 3) not null,

    constraint pk_hugetable primary key clustered (
        fk asc,
        added asc,
        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];

Com o seguinte índice definido:

create nonclustered index ix_hugetable on dbo.hugetable (
    fk asc, added asc, id asc
) include(value) with (
    pad_index = off, statistics_norecompute = off,
    sort_in_tempdb = off, ignore_dup_key = off,
    drop_existing = off, online = off,
    allow_row_locks = on, allow_page_locks = on
)
on [primary];

O campo id é redundante, um artefato de um DBA anterior que insistia que todas as tabelas em todos os lugares deveriam ter um GUID, sem exceções.