Início / dba / Perguntas / 219137
Accepted
Joe Obbish
Asked: 2018-10-03 19:26:33 +0800 CST

Por que uma varredura é mais rápida do que buscar esse predicado?

  • 772

Consegui reproduzir um problema de desempenho de consulta que descreveria como inesperado. Estou procurando uma resposta focada em internos.

Na minha máquina, a consulta a seguir faz uma verificação de índice clusterizado e leva cerca de 6,8 segundos de tempo de CPU:

SELECT ID1, ID2
FROM two_col_key_test WITH (FORCESCAN)
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

A consulta a seguir faz uma busca de índice clusterizado (a única diferença é remover a FORCESCANdica), mas leva cerca de 18,2 segundos de tempo de CPU:

SELECT ID1, ID2
FROM two_col_key_test
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

Os planos de consulta são bastante semelhantes. Para ambas as consultas, há 120000001 linhas lidas do índice clusterizado:

planos de consulta

Estou no SQL Server 2017 CU 10. Aqui está o código para criar e preencher a two_col_key_testtabela:

drop table if exists dbo.two_col_key_test;

CREATE TABLE dbo.two_col_key_test (
    ID1 NVARCHAR(50) NOT NULL,
    ID2 NVARCHAR(50) NOT NULL,
    FILLER NVARCHAR(50),
    PRIMARY KEY (ID1, ID2)
);

DROP TABLE IF EXISTS #t;

SELECT TOP (4000) 0 ID INTO #t
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);


INSERT INTO dbo.two_col_key_test WITH (TABLOCK)
SELECT N'FILLER TEXT' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) > 8000000 THEN N' 2' ELSE N'' END
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, NULL
FROM #t t1
CROSS JOIN #t t2;

Espero uma resposta que faça mais do que relatórios de pilha de chamadas. Por exemplo, posso ver que sqlmin!TCValSSInRowExprFilter<231,0,0>::GetDataXleva significativamente mais ciclos de CPU na consulta lenta em comparação com a rápida:

ver

Em vez de parar por aí, gostaria de entender o que é isso e por que há uma diferença tão grande entre as duas consultas.

Por que há uma grande diferença no tempo de CPU para essas duas consultas?

sql-server performance

1 respostas

  1. Best Answer

    Por que há uma grande diferença no tempo de CPU para essas duas consultas?

    O plano de varredura avalia o seguinte predicado não sargável (residual) enviado para cada linha:

    [two_col_key_test].[ID1]<>N'1' 
AND [two_col_key_test].[ID1]<>N'10' 
AND [two_col_key_test].[ID1]<>N'11' 
AND [two_col_key_test].[ID1]<>N'12' 
AND [two_col_key_test].[ID1]<>N'13' 
AND [two_col_key_test].[ID1]<>N'14' 
AND [two_col_key_test].[ID1]<>N'15' 
AND [two_col_key_test].[ID1]<>N'16' 
AND [two_col_key_test].[ID1]<>N'17' 
AND [two_col_key_test].[ID1]<>N'18' 
AND [two_col_key_test].[ID1]<>N'19' 
AND [two_col_key_test].[ID1]<>N'2' 
AND [two_col_key_test].[ID1]<>N'20' 
AND [two_col_key_test].[ID1]<>N'3' 
AND [two_col_key_test].[ID1]<>N'4' 
AND [two_col_key_test].[ID1]<>N'5' 
AND [two_col_key_test].[ID1]<>N'6' 
AND [two_col_key_test].[ID1]<>N'7' 
AND [two_col_key_test].[ID1]<>N'8' 
AND [two_col_key_test].[ID1]<>N'9' 
AND 
(
    [two_col_key_test].[ID1]=N'FILLER TEXT' 
    AND [two_col_key_test].[ID2]>=N'' 
    OR [two_col_key_test].[ID1]>N'FILLER TEXT'
)

    digitalizar residual

    O plano de busca faz duas operações de busca:

    Seek Keys[1]: 
    Prefix: 
    [two_col_key_test].ID1 = Scalar Operator(N'FILLER TEXT'), 
        Start: [two_col_key_test].ID2 >= Scalar Operator(N'')
Seek Keys[1]: 
    Start: [two_col_key_test].ID1 > Scalar Operator(N'FILLER TEXT')

    ...para corresponder a esta parte do predicado:

    (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))

    Um predicado residual é aplicado a linhas que passam pelas condições de busca acima (todas as linhas em seu exemplo).

    No entanto, cada desigualdade é substituída por dois testes separados para menor que OR maior que :

    ([two_col_key_test].[ID1]<N'1' OR [two_col_key_test].[ID1]>N'1') 
AND ([two_col_key_test].[ID1]<N'10' OR [two_col_key_test].[ID1]>N'10') 
AND ([two_col_key_test].[ID1]<N'11' OR [two_col_key_test].[ID1]>N'11') 
AND ([two_col_key_test].[ID1]<N'12' OR [two_col_key_test].[ID1]>N'12') 
AND ([two_col_key_test].[ID1]<N'13' OR [two_col_key_test].[ID1]>N'13') 
AND ([two_col_key_test].[ID1]<N'14' OR [two_col_key_test].[ID1]>N'14') 
AND ([two_col_key_test].[ID1]<N'15' OR [two_col_key_test].[ID1]>N'15') 
AND ([two_col_key_test].[ID1]<N'16' OR [two_col_key_test].[ID1]>N'16') 
AND ([two_col_key_test].[ID1]<N'17' OR [two_col_key_test].[ID1]>N'17') 
AND ([two_col_key_test].[ID1]<N'18' OR [two_col_key_test].[ID1]>N'18') 
AND ([two_col_key_test].[ID1]<N'19' OR [two_col_key_test].[ID1]>N'19') 
AND ([two_col_key_test].[ID1]<N'2' OR [two_col_key_test].[ID1]>N'2') 
AND ([two_col_key_test].[ID1]<N'20' OR [two_col_key_test].[ID1]>N'20') 
AND ([two_col_key_test].[ID1]<N'3' OR [two_col_key_test].[ID1]>N'3') 
AND ([two_col_key_test].[ID1]<N'4' OR [two_col_key_test].[ID1]>N'4') 
AND ([two_col_key_test].[ID1]<N'5' OR [two_col_key_test].[ID1]>N'5') 
AND ([two_col_key_test].[ID1]<N'6' OR [two_col_key_test].[ID1]>N'6') 
AND ([two_col_key_test].[ID1]<N'7' OR [two_col_key_test].[ID1]>N'7') 
AND ([two_col_key_test].[ID1]<N'8' OR [two_col_key_test].[ID1]>N'8') 
AND ([two_col_key_test].[ID1]<N'9' OR [two_col_key_test].[ID1]>N'9')

    procurar residual

    Reescrevendo cada desigualdade, por exemplo:

    [ID1] <> N'1'  ->  [ID1]<N'1' OR [ID1]>N'1'

    ...é contraproducente aqui. As comparações de string com reconhecimento de agrupamento são caras. Dobrar o número de comparações explica a maior parte da diferença no tempo de CPU que você vê.

    Você pode ver isso mais claramente desabilitando o envio de predicados não sargáveis ​​com o sinalizador de rastreamento não documentado 9130. Isso mostrará o resíduo como um filtro separado, com informações de desempenho que você pode inspecionar separadamente:

    Varredura

    procurar

    Isso também destacará a pequena estimativa incorreta de cardinalidade na busca, o que explica por que o otimizador escolheu a busca sobre a varredura em primeiro lugar (esperava que a parte de busca eliminasse algumas linhas).

    Embora a reescrita da desigualdade possa tornar possível a correspondência de índices (possivelmente filtrada) (para fazer o melhor uso da capacidade de busca dos índices b-tree), seria melhor reverter essa expansão posteriormente se ambas as metades terminarem no resíduo. Você pode sugerir isso como uma melhoria no site de comentários do SQL Server .

    Observe também que o modelo de estimativa de cardinalidade original ("herdado") seleciona uma varredura por padrão para essa consulta.

    • 32

relate perguntas