Início / dba / Perguntas / 155881
Accepted
BanksySan
Asked: 2016-11-22 05:41:06 +0800 CST

Desempenho de indexação inesperado (tudo é igual)

  • 772

Eu queria ver os efeitos de ter índices em colunas calculadas, então criei uma tabela como esta:

CREATE TABLE [Domain\UserName].[CompColIndexing](
    [a] [int] NOT NULL,
    [nonIndexedNonPersisted]  AS ([a]+(1)),
    [nonIndexedPersisted]  AS ([a]+(1)) PERSISTED,
    [IndexedNonPersisted]  AS ([a]+(1)),
    [IndexedPersisted]  AS ([a]+(1)) PERSISTED
) ON [DATA]

Adicionei 800,000linhas a isso, com o valor para apercorrer 0até 9.

Os seguintes índices foram adicionados:

CREATE NONCLUSTERED INDEX [IX_DJB_CompNonPersisted] ON [Domain\UserName].[CompColIndexing] 
(
    [IndexedNonPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

CREATE NONCLUSTERED INDEX [IX_DJB_CompPersisted] ON [Domain\UserName].[CompColIndexing] 
(
    [IndexedPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

Então eu executei algumas ORDER BYcláusulas para ver quais diferenças de desempenho eu obteria, planejando depois para ver como a mudança de valores aafetaria as coisas.

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY a

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedPersisted

Porém, inesperadamente, descobri que obtenho exatamente o mesmo resultado para cada uma das consultas:

Saída do Profiler

Eu esperava pelo menos que a SORToperação na primeira consulta fosse mais lenta, pois essa não é indexada.

O que está acontecendo aqui?

A cardinalidade é baixa propositalmente, na verdade, preciso classificar em três valores diferentes.

estou a usarMicrosoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

Planos de execução reais disponíveis em: https://www.brentozar.com/pastetheplan/?id=S10MTuxGg

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    A definição da tabela que você tem está levando a um comportamento realmente estranho do otimizador. Suspeito que você esteja enfrentando o problema documentado nesta postagem do SE . Para evitar esse problema, vou criar a tabela apenas com as colunas [a] e [IndexedPersisted].

    As dicas de consulta podem ser úteis para descobrir por que o otimizador não escolheu o plano que você esperava. Aqui você esperava que o índice fosse usado, mas o SQL Server não o usou. Vamos ver ambos os planos de consulta lado a lado:

    SELECT *
FROM [dbo].[CompColIndexing]
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT *
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

    planos de consulta

    O otimizador de consulta considera que a classificação após a varredura da tabela é mais barata do que 800.000 pesquisas RID. Talvez esteja errado, então vamos executar as consultas e comparar as métricas de desempenho para elas.

    cpu_time    total_elapsed_time  logical_reads   reads
1204        1395                3087            1485
1516        1570                851798          0

    Eu obtive esses números olhando para sys.dm_exec_sessions depois de executar as consultas em sessões separadas com "descartar resultados após a execução" marcada para que eu não precisasse esperar que as linhas fossem retornadas ao cliente.

    Esses números me parecem razoáveis. Só porque um índice pode ser usado não significa que ele deva ser usado, especialmente se o SQL Server precisar ler a tabela inteira usando o índice. Esse é o pior caso de uso para um índice que posso imaginar. Os índices podem ser muito úteis ao selecionar uma pequena porcentagem de linhas da tabela ou quando estão cobrindo índices.

    O índice é abrangente se eu selecionar apenas a coluna [IndexedPersisted]. Nesse caso, o servidor SQL acha que usar o índice é mais barato do que fazer uma varredura na tabela. Código para comparar os dois métodos:

    -- force table scan
SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX(0))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

    plano de consulta 2

    Aqui estão os números de desempenho:

    cpu_time    total_elapsed_time  logical_reads   reads
1171        1212                2727            1088
343         406                 1798            0

    Agora que o índice é um índice de cobertura, é um caminho de acesso melhor para a tabela do que uma varredura de tabela.

    • 4

relate perguntas