Subconsulta de baixo desempenho com comparações de data

Com a definição de índice que você tem para IDX_my_nme, o SQL Server poderá buscar usando a ActionDatecoluna, mas não com a Addresscoluna. O índice contém todas as colunas necessárias para cobrir a subconsulta, mas provavelmente não é muito seletivo para essa subconsulta. Suponha que quase todos os dados na tabela tenham um ActionDatevalor anterior a '2017-05-30'. Uma busca de ActionDate < '2017-05-30'retornará quase todas as linhas do índice, que são filtradas ainda mais depois que a linha é buscada do índice. Se sua consulta retornar 200 linhas, você provavelmente faria quase 200 varreduras completas de índice em IDX_my_nme, o que significa que você lerá cerca de 50.000 * 200 = 10 milhões de linhas do índice.

É provável que a busca Addressseja muito mais seletiva para sua subconsulta, embora você não tenha nos fornecido informações estatísticas completas sobre a consulta, então isso é uma suposição de minha parte. No entanto, suponha que você crie um índice em just Addresse sua tabela tenha 10k valores exclusivos para Address. Com o novo índice, o SQL Server precisará buscar apenas 5 linhas do índice para cada execução da subconsulta, então você lerá cerca de 200 * 5 = 1000 linhas do índice.

Estou testando no SQL Server 2016, portanto, pode haver algumas pequenas diferenças de sintaxe. Abaixo estão alguns dados de exemplo nos quais fiz suposições semelhantes às acima para distribuição de dados:

CREATE TABLE #Activity (
    Id int NOT NULL,
    [Address] varchar(25) NULL,
    ActionDate datetime2 NULL,
    FILLER varchar(100),
    PRIMARY KEY (Id)
);

INSERT INTO #Activity WITH (TABLOCK)
SELECT TOP (50000) -- 50k total rows
x.RN
, x.RN % 10000 -- 10k unique addresses
, DATEADD(DAY, x.RN / 100, '20160201') -- 100 rows per day
, REPLICATE('Z', 100)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x;

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON #Activity
([ActionDate] ASC) INCLUDE ([Address]);

Eu criei seu índice conforme descrito na pergunta. Estou testando essa consulta que retorna os mesmos dados da pergunta:

select 
    a.*
    , ( select count(*) 
        from #Activity Activity
        where 
            Activity.[Address] = a.[Address]
            and Activity.ActionDate < a.ActionDate
    ) as PriorCount
from #Activity a
where a.ActionDate between '2017-05-29' and '2017-05-30'
order by a.ActionDate desc;

Eu recebo um carretel de índice. O que isso significa em um nível básico é que o otimizador de consulta cria um índice temporário dinamicamente porque nenhum dos índices existentes na tabela era adequado.

A consulta ainda termina rapidamente para mim. Talvez você não esteja obtendo a otimização do spool de índice em seu sistema ou há algo diferente na definição da tabela ou na consulta. Para fins educacionais, posso usar um recurso não documentado OPTION (QUERYRULEOFF BuildSpool)para desativar o carretel de índice. Veja como é o plano:

Não se deixe enganar pela aparência de uma simples busca de índice. O SQL Server lê quase 10 milhões de linhas do índice:

Se eu for executar a consulta mais de uma vez, provavelmente não fará sentido para o otimizador de consulta criar um índice toda vez que for executado. Eu poderia criar um índice antecipado que seria mais seletivo para esta consulta:

CREATE NONCLUSTERED INDEX [IDX_my_nme_2] ON #Activity
([Address] ASC) INCLUDE (ActionDate);

O plano é semelhante ao anterior:

No entanto, com o novo índice, o SQL Server lê apenas 1.000 linhas do índice. 800 das linhas são retornadas para serem contadas. O índice pode ser definido para ser mais seletivo, mas isso pode ser bom o suficiente dependendo da sua distribuição de dados.

Se você não conseguir definir nenhum índice adicional na tabela, eu consideraria usar funções de janela. O seguinte parece funcionar:

SELECT t.*
FROM
(
    select 
        a.*
        , -1 + ROW_NUMBER() OVER (PARTITION BY [Address] ORDER BY ActionDate) PriorCount
    from #Activity a
) t
where t.ActionDate between '2017-05-29' and '2017-05-30'
order by t.ActionDate desc;

Essa consulta faz uma única varredura dos dados, mas faz uma classificação cara e calcula a ROW_NUMBER()função para cada linha da tabela, então parece que há algum trabalho extra feito aqui:

No entanto, se você realmente gosta desse padrão de código, pode definir um índice para torná-lo mais eficiente:

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON #Activity
([Address], [ActionDate]) INCLUDE (FILLER);

Isso move o tipo para o final, que será muito menos caro:

Se nada disso ajudar, você precisará adicionar mais informações à pergunta, de preferência incluindo planos de execução reais.