Início / dba / Perguntas / 124785
Accepted
Der Kommissar
Asked: 2015-12-30 08:12:18 +0800 CST

Por que o SQL Server ignoraria um índice?

  • 772

Eu tenho uma tabela CustPassMastercom 16 colunas, uma das quais é CustNum varchar(8), e criei um índice IX_dbo_CustPassMaster_CustNum. Quando executo minha SELECTdeclaração:

SELECT * FROM dbo.CustPassMaster WHERE CustNum = '12345678'

Ele ignora o índice completamente. Isso me confunde, pois tenho outra tabela CustDataMastercom muito mais colunas (55), uma das quais é CustNum varchar(8). Criei um índice nesta coluna ( IX_dbo_CustDataMaster_CustNum) nesta tabela, e utilizo praticamente a mesma consulta:

SELECT * FROM dbo.CustDataMaster WHERE CustNum = '12345678'

E usa o índice que criei.

Existe algum raciocínio específico por trás disso? Por que ele usaria o índice de CustDataMaster, mas não o de CustPassMaster? É devido à baixa contagem de colunas?

A primeira consulta retorna 66 linhas. Para o segundo, 1 linha é retornada.

Além disso, nota adicional: CustPassMasterpossui 4.991 registros e CustDataMasterpossui 5.376 registros. Este poderia ser o raciocínio por trás de ignorar o índice? CustPassMastertambém possui registros duplicados com os mesmos CustNumvalores. Esse é outro fator?

Estou baseando essa afirmação nos resultados reais do plano de execução de ambas as consultas.

Aqui está o DDL para CustPassMaster(aquele com o índice não utilizado):

CREATE TABLE dbo.CustPassMaster(
    [CustNum] [varchar](8) NOT NULL,
    [Username] [char](15) NOT NULL,
    [Password] [char](15) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustPassMaster_CustNum] ON dbo.CustPassMaster
(
    [CustNum] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

E o DDL para CustDataMaster(omiti muitos campos irrelevantes):

CREATE TABLE dbo.CustDataMaster(
    [CustNum] [varchar](8) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustDataMaster_CustNum] ON dbo.CustDataMaster
(
    [CustNum] ASC
)WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Não tenho um índice clusterizado em nenhuma dessas tabelas, apenas um índice não clusterizado.

Ignore o fato de que os tipos de dados não correspondem inteiramente ao tipo de dados que está sendo armazenado. Esses campos são um backup de um banco de dados IBM AS/400 DB2 e esses são os tipos de dados compatíveis para ele. (Tenho que ser capaz de consultar esse banco de dados de backup com exatamente as mesmas consultas e obter exatamente os mesmos resultados.)

Esses dados são usados ​​apenas para SELECTdeclarações. Eu não faço nenhuma instrução INSERT// nele, exceto quando o aplicativo de backup está copiando dados do AS/400 UPDATE.DELETE

sql-server sql-server-2012

3 respostas

  1. Best Answer

    Normalmente, os índices serão usados ​​pelo SQL Server se considerar mais conveniente usar o índice do que usar diretamente a tabela subjacente.

    Parece provável que o otimizador baseado em custo pense que seria mais caro realmente usar o índice em questão. Você pode vê-lo usar o índice se em vez de fazer SELECT *, você simplesmente SELECT T1Col1.

    Quando você SELECT *está dizendo ao SQL Server para retornar todas as colunas da tabela. Para retornar essas colunas, o SQL Server deve ler as páginas das linhas que correspondem aos WHEREcritérios de instrução da própria tabela (índice clusterizado ou heap). O SQL Server provavelmente está pensando que a quantidade de leituras necessárias para obter o restante das colunas da tabela significa que ele também pode verificar a tabela diretamente. Seria útil ver a consulta real e o plano de execução real usado pela consulta.

    • 18

  2. Para usar o índice, porque você está fazendo select *, o SQL Server deve primeiro ler cada uma das linhas do índice que correspondem ao valor que você possui na cláusula where. Com base nisso, ele obterá os valores do índice clusterizado para cada uma das linhas e, em seguida, deverá buscar cada um deles separadamente do índice clusterizado (= pesquisa de chave). Como você disse que os valores não são exclusivos, o SQL Server usa estatísticas para estimar quantas vezes ele precisa fazer essa pesquisa de chave.

    Muito provavelmente, a estimativa de custo para verificar o índice não clusterizado + pesquisas de chave excede a estimativa de custo para a verificação de índice clusterizado e é por isso que o índice é ignorado.

    Você pode tentar usar set statistics io one depois usar uma dica de índice para ver se o custo de E/S é realmente menor ao usar o índice ou não. Se a diferença for grande, você pode consultar as estatísticas, se estiverem desatualizadas.

    Além disso, se o seu SQL estiver realmente usando variáveis ​​e não os valores exatos, isso também pode ser causado por detecção de parâmetro (=o valor anterior usado para criar o plano tinha muitas linhas na tabela).

    • 10

  3. Essa pode ser a razão. Os otimizadores são baseados no custo e decidem qual caminho escolher com base no 'custo' que cada caminho de execução possui. O 'maior' custo é levar os dados do disco para a memória. Se o otimizador calcular que leva mais tempo para ler o índice e os dados, ele pode decidir ignorar o índice. Quanto maiores as linhas, mais blocos de disco elas ocupam.

    • 1

relate perguntas