Início / dba / Perguntas / 286452
Accepted
Jez
Asked: 2021-03-05 11:48:05 +0800 CST

Por que o SQL Server não usa meu índice neste SELECT ... WHERE?

  • 772

Criei uma tabela com um PK não clusterizado (isso ocorre por design) e um índice não clusterizado adicional na coluna que estou filtrando com uma WHEREcláusula ( [target_user_id]):

CREATE TABLE [dbo].[MP_Notification_Audit] (
    [id]                    BIGINT             IDENTITY (1, 1) NOT NULL,
    [type]                  INT                NOT NULL,
    [source_user_id]        BIGINT             NOT NULL,
    [target_user_id]        BIGINT             NOT NULL,
    [discussion_id]         BIGINT             NULL,
    [discussion_comment_id] BIGINT             NULL,
    [discussion_media_id]   BIGINT             NULL,
    [patient_id]            BIGINT             NULL,
    [task_id]               BIGINT             NULL,
    [date_created]          DATETIMEOFFSET (7) CONSTRAINT [DF_MP_Notification_Audit_date_created] DEFAULT (sysdatetimeoffset()) NOT NULL,
    [clicked]               BIT                NULL,
    [date_clicked]          DATETIMEOFFSET (7) NULL,
    [title]                 NVARCHAR (MAX)     NULL,
    [body]                  NVARCHAR (MAX)     NULL,
    CONSTRAINT [PK_MP_Notification_Audit1] PRIMARY KEY NONCLUSTERED ([id] ASC)
);

[...]

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUser] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] 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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Esta tabela possui cerca de 11.700 linhas de dados, portanto deve ser suficiente para acionar o uso de índices com WHEREcláusulas. Se eu SELECTapenas a coluna que estou filtrando, apenas o índice é usado e 133 linhas correspondentes são lidas - uma verificação somente de índice:

SELECT [target_user_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

Plano de execução 1

No entanto, assim que eu adiciono uma coluna extra ao SELECT, o índice é ignorado e uma varredura de tabela com um predicado é feita para obter o resultado, lendo mais de 11.700 linhas:

SELECT [target_user_id], [patient_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

Plano de execução 2

Por que está ignorando meu índice nesta segunda consulta? Eu teria pensado que ainda seria mais eficiente usar o índice para chegar a 133 RIDs e, em seguida, consultar os dados de linha extras necessários, do que percorrer todas as linhas da tabela com um predicado? Eu sei que posso adicionar colunas ao índice com INCLUDEos campos extras necessários na SELECTcláusula para fazê-lo usar o índice novamente, mas estou interessado em saber por que ele ainda não usa o índice nesse caso.

sql-server query-performance

2 respostas

  1. Best Answer

    Dado o tamanho da sua tabela (~ 11k linhas), acho que seria seguro supor que o SQL Server estimou que o custo de realizar uma busca no índice não clusterizado e, em seguida, várias pesquisas RID eram mais caras do que executar uma tabela Varredura.

    Há alguma evidência para apoiar essa teoria no segundo plano de consulta que você colou. Eu normalmente esperaria que o Otimizador de Consulta sugerisse a adição de um índice de cobertura para sua consulta, conforme você mencionou em sua postagem. No entanto, isso não aconteceu. Isso para mim sugere que o SQL pensa que isso forneceria pouca ou nenhuma melhoria em relação a uma verificação completa da tabela.

    Com tudo isso dito, tenho certeza de que, se você adicionar mais linhas à tabela, o SQL Server pode mudar de ideia e solicitar que você adicione um índice de cobertura ou comece a executar uma pesquisa de busca + RID conforme o esperado. Se você tiver o Repositório de Consultas ativado, sempre poderá ficar de olho nas consultas nesta tabela que estão causando problemas - se não estiver causando problemas, eu não me preocuparia com isso agora.

    • 3

  2. Um pouco de informação extra: esta é a estatística para quando você usa varredura de tabela vs busca de índice + Pesquisa RID

    Varredura de Tabela

    Table 'MP_Notification_Audit'. Scan count 1, logical reads 154, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

    Busca de índice + Pesquisa RID

    (118 rows affected)
Table 'MP_Notification_Audit'. Scan count 1, logical reads 120, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 220 ms.

    Como pode ser visto, a diferença nas leituras lógicas é de ~23%, mas o número absoluto é baixo, o sistema de E/S nem deve notar, apenas 272kB.

    Mas a diferença na CPU é óbvia, 220ms gastos na pesquisa RID e loops aninhados é alto. Como foi dito em resposta, é simples. O custo extra de E/S foi corretamente estimado como sendo menor que o custo extra na CPU.

    • 2

relate perguntas