Existe alguma documentação ou pesquisa sobre alterações no SQL Server 2016 para como a cardinalidade é estimada para predicados contendo SUBSTRING () ou outras funções de string?
A razão pela qual estou perguntando é que eu estava olhando para uma consulta cujo desempenho degradou no modo de compatibilidade 130 e o motivo foi relacionado a uma alteração na estimativa do número de linhas que correspondem a uma cláusula WHERE que continha uma chamada para SUBSTRING (). Corrigi o problema com uma reescrita de consulta, mas gostaria de saber se alguém conhece alguma documentação sobre alterações nessa área no SQL Server 2016.
O código de demonstração está abaixo. As estimativas são muito próximas neste caso de teste, mas a precisão varia dependendo dos dados.
No caso de teste, no nível de compatibilidade 120, o SQL Server parece estar usando o histograma para a estimativa, enquanto no nível de compatibilidade 130, o SQL Server parece assumir 10% fixos das correspondências da tabela.
CREATE DATABASE MyStringTestDB;
GO
USE MyStringTestDB;
GO
DROP TABLE IF EXISTS dbo.StringTest;
CREATE TABLE dbo.StringTest ( [TheString] varchar(15) );
GO
INSERT INTO dbo.StringTest
VALUES
( 'Y5_CLV' );
INSERT INTO dbo.StringTest
VALUES
( 'Y5_EG3' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_NE' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_PQT' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_T2V' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_TT4' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_ZKK' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_LW6' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_QO3' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_TZ7' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_UZZ' );
CREATE CLUSTERED INDEX IX_Clustered ON dbo.StringTest (TheString);
/*
Uses fixed % for estimate; 1.1 rows estimated in this case.
Plan for computation:
CSelCalcFixedFilter (0.1) <----
Selectivity: 0.1
*/
ALTER DATABASE MyStringTestDB SET compatibility_level = 130;
GO
SELECT *
FROM dbo.StringTest
WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);
/*
Uses histogram to get estimate of 1
CSelCalcPointPredsFreqBased <----
Distinct value calculation:
CDVCPlanLeaf
0 Multi-Column Stats, 1 Single-Column Stats, 0 Guesses
Individual selectivity calculations:
(none)
Loaded histogram for column QCOL: [DBA].[dbo].[StringTest].TheString from stats with id 1
*/
ALTER DATABASE MyStringTestDB SET compatibility_level = 120;
GO
SELECT *
FROM dbo.StringTest
WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);
/*
-- Simpler rewrite; works fine in both compat levels and gets better estimate.
SELECT *
FROM dbo.StringTest
WHERE TheString LIKE 'ZZ[_]%'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);
*/
Não tenho conhecimento de nenhuma documentação. Eu examinei isso e fiz algumas observações, no entanto, que são muito longas para um comentário.
A estimativa de 10% nem sempre é uma degradação. Veja o exemplo a seguir.
e a
WHEREcláusula em sua pergunta.A tabela contém um milhão de linhas. Todos eles correspondem ao predicado. No nível de compatibilidade 130, o palpite de 10% produz uma estimativa de 100.000. Abaixo de 120, as linhas estimadas são 1,03913.
O comportamento 120 usa o histograma, mas apenas para obter o número de linhas distintas. O vetor de densidade no meu caso mostra 1,039131E-06 e é multiplicado pela cardinalidade da tabela para obter a contagem de linhas estimada. Todos os valores são de fato diferentes, mas todos correspondem ao predicado.
Rastrear o
query_optimizer_estimate_cardinalityevento estendido mostra que abaixo de 130 há dois<StatsCollection Name="CStCollFilter"eventos diferentes. O primeiro estima 100.000. O segundo carrega o histograma e usa o CSelCalcPointPredsFreqBased/DistinctCountCalculator para obter a estimativa de 1,04. Este segundo resultado parece não utilizado.O comportamento que você observou não é aplicado consistentemente em 130. Acrescentei que
ORDER BY TheStringesperava que isso fosse uma vitória clara para o estimador 130, pois o 120 luta com uma concessão de memória para uma linha, mas essa pequena alteração foi suficiente para reduzir as linhas estimadas para 1,03913 no caso 130 também.A adição
OPTION (QUERYRULEOFF SelectToFilter)reverte a estimativa que entra na classificação para 100.000, mas a concessão de memória não aumenta e as estimativas que saem da classificação ainda são baseadas nos valores distintos da tabela.Da mesma forma, ajustar o limite de custo para paralelismo para que a consulta obtenha um plano paralelo foi suficiente no caso 130 para reverter para a estimativa mais baixa. A adição
QUERYTRACEON 8757também causa a estimativa mais baixa. Parece que a estimativa de 10% é mantida apenas para planos triviais.Sua reescrita proposta com
Mostra estimativas muito superiores a ambos. A saída para isso é
Mostrando que usou tentativas . Mais informações sobre isso estão na seção de estatísticas de resumo de string logo acima aqui .
No entanto, não é o mesmo que sua consulta original. Como a primeira instância de
_agora é considerada sempre o terceiro caractere, em vez de ser encontrada dinamicamente.Se essa suposição estiver codificada em sua consulta original
O método de estimativa muda para
CSelCalcHistogramComparison(INTERVAL)e as linhas estimadas tornam-se precisas.É capaz de converter isso em um intervalo
e use o histograma para estimar o número de linhas com valores nesse intervalo.
No entanto, isso se aplica apenas à estimativa de cardinalidade.
LIKEé preferível, pois pode usar uma busca de intervalo em tempo de execução.SUBSTRING(TheString, 1, 3)ouLEFT(TheString, 3)não pode.