Uma consulta de procedimento armazenado às vezes obtém um plano ruim após a atualização das estatísticas em uma das tabelas, mas pode ser recompilada para o plano bom logo em seguida. Mesmos parâmetros compilados.
O problema parece vir de uma pequena tabela temporária criada no SP e depois unida. O plano inválido tem um aviso na tabela temporária de que a coluna de junção não possui estatísticas. O que da?
SQL Server 2016 SP1 CU4, com nível de compatibilidade de 2014
Plano ruim:
Bom plano:
Procedimento armazenado
USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [MySchema].[MySP]
@MyId VARCHAR(50),
@Months INT
AS
BEGIN
SET NOCOUNT ON
SELECT *
INTO #MyTemp
FROM AppDB.MySchema.View_Feeder vf WITH (NOLOCK)
WHERE vf.MyId = @MyId AND vf.Status IS NOT NULL
SELECT wd.Col1
, vp.Col2
, vp.Col3
FROM AppDB.MySchema.View_VP vp WITH (FORCESEEK)
INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())
END
Visão interna
USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW [MySchema].[View_VP]
AS
SELECT pp.Col1,
pd.Col2 AS Col2,
MAX(pp.Col4) AS Col3
FROM P_DB..LargeTable pp WITH (NOLOCK)
INNER JOIN P_DB..SmallTable pd WITH (NOLOCK) ON pp.P_Id = pd.P_Id
WHERE pp.[Status] IN (3, 4)
GROUP BY pp.Col1, pd.Col2
Planos
Redigido bom plano e plano ruim .
Informação adicional
A FORCESEEKdica foi adicionada na época para tentar lidar com esse mesmo problema e estabilizar o plano. E de qualquer forma, com ou sem isso, eu realmente gostaria de entender o que está acontecendo aqui.
Não consigo reproduzir o problema à vontade, por isso é difícil dizer se substituir SELECT INTOpor uma tabela explícita faria diferença. No entanto, acredito que deve se comportar da mesma maneira.
SELECT
database_id,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on
FROM sys.databases
WHERE
database_id IN (2, <relevant user databases>)
retorna:
database_id is_auto_create_stats_on is_auto_update_stats_on is_auto_update_stats_async_on
------------- ------------------------- ------------------------- -------------------------------
2 1 1 0
7 1 1 1
37 1 1 1
É claro que essa busca é terrível, mas a questão é por que ela não faz a boa busca em primeiro lugar.
A consulta não retorna 1 milhão de linhas, as estimativas estão erradas. Pode haver pequenas alterações na saída, mas o número de linhas é sempre muito baixo (talvez centenas no máximo).
Mesmo os que retornam relativamente muitas linhas geram planos buscando pelo Ide nunca pelo status(o que não é seletivo como você pode ver). Não consigo reproduzir o plano de busca de status, não importa quais valores sejam compilados. Eu até tentei adicionar um waitfor delayentre a criação da tabela temporária e a segunda consulta, e atualizar estatísticas/recompilar em uma segunda sessão, sem efeito também.
