Início / dba / Perguntas / 244062
Accepted
Chessbrain
Asked: 2019-07-30 09:13:46 +0800 CST

Obtendo Index Scan em vez de uma possível Busca de Índice?

  • 772

Atualmente aprendendo algumas coisas sobre otimização de consultas, e tenho tentado consultas diferentes e me deparei com esse "problema".

Estou usando o banco de dados AdventureWorks2014, executei esta consulta simples:

estrutura da tabela (retirada de https://www.sqldatadictionary.com/AdventureWorks2014.pdf ):

insira a descrição da imagem aqui

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

retorna 19.720 linhas

número total de linhas em Sales.Customer = 19.820

E depois de verificar se CustomerID não é apenas o PK da tabela, mas também tem um índice clusterizado (ainda que usa um índice não clusterizado), esse é o caso:

EXEC SP_HELPINDEX 'Sales.Customer'

insira a descrição da imagem aqui

Aqui está o plano de execução ↓

https://www.brentozar.com/pastetheplan/?id=B1g1SihGr

Eu li que quando confrontado com grandes quantidades de dados e/ou quando ele retorna mais de 50% do conjunto de dados, o otimizador de consulta favorecerá uma verificação de índice. Mas essa tabela como um todo mal tem 20.000 linhas (19.820 para ser exato), não é uma tabela grande de forma alguma.

Quando executo esta consulta:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 30000

retorna 118 linhas

https://www.brentozar.com/pastetheplan/?id=Byyux32MS

Em vez disso, recebo uma busca de índice, então pensei que era devido a esse "caso de mais de 50%", no entanto, também executei esta consulta:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 20000

retorna 10.118 linhas

https://www.brentozar.com/pastetheplan/?id=HJ9oV33zr

E também usou uma busca de índice, embora estivesse retornando mais de 50% do conjunto de dados.

Então o que está acontecendo aqui?

EDITAR:

Com as Estatísticas de E/S ativadas, a consulta >100 retorna:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Enquanto o > 20.000 retornou:

Table 'Customer'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Então, adicionei WITH(FORCESCAN)a opção > 20.000 para ver o que aconteceria:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Portanto, ele acaba funcionando melhor com um Index Scan (leituras menos lógicas), mesmo que o otimizador de consulta tenha escolhido executar um Index Seek para essa consulta específica.

sql-server index

3 respostas

  1. Best Answer

    Você usa um predicado de não-qualidade para que suas operações de "busca" sejam, na verdade, varreduras que apenas começam de algum valor (não de "primeiro") e depois vão para o final do nível de folha do índice clusterizado.

    Por outro lado, você retorna apenas uma coluna que é a chave do índice clusterizado, portanto, usar qualquer um dos índices não obterá nenhuma operação de pesquisa de chave. O otimizador tem que estimar o que seria mais barato: escanear um índice não clusterizado (duas colunas int no nível folha) ou escanear parte do seu índice clusterizado (todas as colunas no nível folha).

    Ele estima dependendo das estatísticas atuais (quantas linhas) e metadados (qual é o tamanho de uma linha). Vimos que o otimizador cometeu um erro no >20,000predicado.

    Ao se deparar com grandes quantidades de dados e/ou quando retornar mais de 50% do conjunto de dados, o otimizador de consultas favorecerá uma verificação de índice.

    Isso é um fato quando o otimizador tem que escolher executar índice clusterizado ou varredura de tabela versus busca de índice não clusterizado + pesquisas de chave.

    No seu caso, se o seu índice CustomerIDnão fosse clusterizado, você sempre veria uma operação de busca nesse índice, mas se você adicionasse outra coluna à sua saída, veria as pesquisas de índice + RID em conjuntos de resultados curtos e varredura de tabela nos grandes.

    • 5

  2. Na Otimização de Base de Custos, o Otimizador encontra a melhor execução possível em um determinado momento, com boa relação custo-benefício.

    Quando verificamos o tamanho do índice de cada índice nesta tabela,

    SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('Sales.Customer'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;



IX_Customer_TerritoryID || 288
PK_Customer_CustomerID || 976

    Então, claramente, o tamanho do índice de IX_Customer_TerritoryIDé muito menor que PK_Customer_CustomerID.

    Compare o custo de ambas as consultas,

    SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

SELECT C.CustomerID
FROM Sales.Customer AS C WITH(INDEX(PK_Customer_CustomerID))
WHERE C.CustomerID > 100

    I/O costde consulta com índice IX_Customer_TerritoryIDé menor que o de PK_Customer_CustomerID.

    • 0

  3. O otimizador usa o que achar mais rápido; muitas vezes o que eu esperava que fizesse, não acontece. Não é apenas baseado na linha %; é baseado em muitos fatores como as estatísticas que possui, índices, as colunas da tabela e a própria consulta. Usa isso para criar estimativas de custo e limite, embora o número de linhas entre em jogo.

    Eu acho que escaneou a primeira consulta por causa dos fatores mencionados acima, ou seja, estatísticas. Ele fez a busca do índice no segundo pelo mesmo motivo. A terceira busca pode ter sido apenas porque o plano já estava compilado na memória. Estou curioso para saber se ele teria verificado isso se você tentasse com recompilar, como o @scsimon sugeriu.

    • -1

relate perguntas