Execução lenta em procedimento armazenado; rápido quando executado ad hoc

Eh

Eu tentei reescrever isso para usar variáveis ​​locais, otimizar para desconhecido\variável\etc, com recompilação.

Os dois primeiros métodos listados aqui são bastante inúteis na maioria das vezes para melhorar o desempenho . Você pode se deparar com uma situação a cada 5 a 10 anos em que eles melhoram uma consulta específica, mas eu manteria essas opções bem baixas na minha lista de coisas para tentar.

No plano lento, a parte que consome mais tempo fica assim:

Aparentemente, você tem uma coluna computada na Catalog_Itemstabela que gera o MD5 e ela não é persistente. Você não precisa persistir, mas a razão pela qual você acaba com um operador Filter em uma expressão e não pode aplicar o predicado enquanto verifica a tabela é porque ele precisa ser calculado por linha em tempo de execução .

Na consulta rápida, você tem CategoryCodeHasho index [IX_Catalog_Items_IdRetailer_CategoryCodeHash], que persiste o valor e o torna pesquisável.

Quanto ao motivo pelo qual você obteve uma estimativa tão ruim no plano lento, não está claro se o @CategoryHashargumento é um parâmetro ou uma variável local neste contexto, mas...

        <ParameterList>
          <ColumnReference 
Column="@TransactionDescriptionId" 
ParameterDataType="varchar(32)" 
ParameterCompiledValue="'Instacart'" 
ParameterRuntimeValue="'Instacart'" 
/>
          <ColumnReference 
Column="@CategoryHash" 
ParameterDataType="binary(16)" 
ParameterRuntimeValue="0xD1DC3A8270A6F9394F88847D7F0050CF" 
/>
        </ParameterList>

O fato de @TransactionDescriptionIdpossuir o ParameterCompiledValueatributo e @CategoryHashnão parece indicar que não é um parâmetro detectado. No plano rápido nenhum dos dois tem o ParameterCompiledValueatributo, mas sem a reprodução completa não sei exatamente o que você fez de diferente.

de qualquer forma

O otimizador baseado em custos do SQL Server achou a busca + pesquisa mais cara do que a verificação + filtro de índice clusterizado ou nunca chegou ao plano de busca + pesquisa. Muitas pessoas dirão para você "fazer a cobertura do índice" para que todas as colunas necessárias estejam disponíveis no [IX_Catalog_Items_IdRetailer_CategoryCodeHash]índice. Essa é uma opção, é claro, mas você também pode fazer coisas para enganar o otimizador e fazê-lo usar índices estreitos, como usar um self-join .

SELECT TOP (1)
    ci2.IdItem,
    ci2.IdCatalog,
    ci2.IdRetailer,
    ci2.LastUpdatedFromIdFile,
    ci2.Upc11,
    ci2.Upc12,
    ci2.Gtin,
    ci2.[Description],
    ci2.ShortDescription,
    ci2.CategoryName,
    ci2.SubCategoryName,
    ci2.FineLineCategoryName,
    ci2.SubCategoryCode,
    ci2.Manufacturer
FROM
(
    SELECT
        DelegateMerchantDescription = 
            ISNULL(delegateRetailers.TransactionDescriptionId, retailers.TransactionDescriptionId),
        retailers.TransactionDescriptionId,
        retailers.MerchantId
    FROM Retailers AS retailers (NOLOCK)
    LEFT JOIN Retailers AS delegateRetailers (NOLOCK)
      ON delegateRetailers.TransactionDescriptionId = retailers.DelegateCatalogMerchantDescription
    WHERE retailers.TransactionDescriptionId IS NOT NULL
) AS delegate
JOIN Retailers AS r
  ON r.TransactionDescriptionId = delegate.DelegateMerchantDescription
JOIN Catalog_Items AS ci WITH (NOLOCK)
  ON ci.IdRetailer = r.IdRetailer
/*This is new*/
JOIN Catalog_Items AS ci2 WITH (NOLOCK)
/*I'm not sure what the PK of Catalog_Items is*/  
  ON ci2.primary_key_column = ci.primary_key_column
WHERE ci.CategoryCodeHash = @CategoryHash
AND
(
    delegate.TransactionDescriptionId = @TransactionDescriptionId
OR  delegate.MerchantId = @TransactionDescriptionId
);

A ideia é unir uma tabela a si mesma em valores de chave primária, com uma referência à tabela usada para selecionar todas as colunas que deseja exibir, e a outra referência responsável pela união e filtragem e quaisquer outras atividades relacionais.

contextualmente

Uma coisa que pode valer a pena considerar por que o plano é diferente seriam as várias opções de SET . Alguns são necessários para que visualizações indexadas, colunas computadas e índices filtrados sejam avaliados durante a otimização:

A opção NUMERIC_ROUNDABORT deve ser definida como OFF e as seguintes opções devem ser definidas como ON:

ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER

O DatabaseContextSettingsIdé diferente para os dois planos:

vs.

Você pode ver algumas das configurações aqui:

SELECT
    procedure_name = p.name,
    sm.uses_ansi_nulls,
    sm.uses_quoted_identifier
FROM sys.procedures AS p
JOIN sys.sql_modules AS sm
  ON p.object_id = sm.object_id
WHERE p.name = 'YourProcedureName';

Você também pode ver se o banco de dados possui algum padrão que vá contra os requisitos de todos os módulos criados sem nenhuma especificação diferente:

SELECT
    database_name = name,
    is_ansi_null_default_on,
    is_ansi_nulls_on,
    is_ansi_padding_on,
    is_ansi_warnings_on,
    is_arithabort_on,
    is_concat_null_yields_null_on,
    is_numeric_roundabort_on,
    is_quoted_identifier_on,
    is_recursive_triggers_on,
    is_cursor_close_on_commit_on,
    is_local_cursor_default
FROM sys.databases
WHERE name = 'YourDatabaseName';

Dependendo do que você vê, você pode tentar recriar o procedimento armazenado com o seguinte:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;

algumas outras coisas

Para sua consideração geral, embora não seja necessariamente um grande problema para esta consulta específica:

1. NOLOCKfede e pode retornar dados incorretos/inconsistentes
2. TOPsem um ORDER BYé uma proposição duvidosa
3. @Table Variablesforçam modificações para serem executadas em thread único e geralmente não são tão interessantes quando você começa a juntá-las a outras tabelas, pois elas não obtêm estatísticas em nível de coluna, mesmo quando indexadas

Observe o CouldNotGenerateValidParallelPlanatributo do INSERToperador. Neste caso, porém, ambos os custos do plano são tão baixos que é improvável que se consiga um plano de execução paralelo de qualquer maneira.