Início / dba / Perguntas / 33521
Accepted
njvds
Asked: 2013-01-25 16:31:54 +0800 CST

Problema de desempenho da consulta

  • 772

Eu tenho esta consulta abaixo que está se comportando de forma estranha. bem estranho no sentido de que não consegui encontrar uma explicação completa para isso.

Versão: Sql Server 2008 R2 Enterprise

Sem fragmentação. Estatísticas atualizadas com fullscan e para todos os índices.

DECLARE @t TABLE (
  subid INT )

INSERT INTO @t
VALUES     (7)--,(3)


SELECT TOP 1 t.QueueItemID
FROM   QueueTable t
WHERE  t.IsProcessed = 0
       AND t.QCode = 'USA'
       AND SubID IN (SELECT SubID
                     FROM   @t)
ORDER  BY t.QueueItemID

Um pouco sobre o esquema:

Variável de tabela (@t) é apenas um subid de coluna.

O esquema da QueueTable é:

CREATE TABLE [dbo].[QueueTable](
    [QueueItemID] [int] IDENTITY(1,1) NOT NULL,
    [SubID] [int] NOT NULL,
    [IsProcessed] [bit] NOT NULL,
    [Qload] [varchar](max) NOT NULL,
    [QCode] [varchar](5) NOT NULL,
    [QDesc] [varchar](max) NULL,
 CONSTRAINT [PK_QueueTable] PRIMARY KEY CLUSTERED 
(
    [QueueItemID] ASC
)

Esta tabela é tão grande quanto as colunas do tipo varchar(max) no esquema acima.

Existem 2 índices NC:

  1. NC index NCx_1 on (isprocessed,qcode) include(queueitemid,subid)

  2. NC index NCx_2 on (subid,isprocessed,qcode) include(queueitemid)

A contagem total de linhas é de cerca de 9 milhões de linhas. O grupo por subid está abaixo:

SubID   RowCount
------  --------
1   68
2   8255571
3   378584
7   5350
11  5318

As linhas que satisfazem a condição (t.IsProcessed = 0 e t.QCode = 'USA') estão em torno de 350k.

Quando executo a consulta acima, leva 1,5 segundo para ser concluída com a busca em NC NCx_1 e, em seguida, digitalizo a variável de tabela. Aqui está o plano.

Plano de consulta

O plano acima é para subid = 11 ou 7 na variável de tabela @t Não sei por que não está usando o índice NCx_2 (subid, isprocessed, qcode) include (queueitemid) que corresponde aos critérios. Em vez disso, ele está usando o índice NCX_1.

Parece que está procurando cerca de 350 mil linhas para satisfazer (t.IsProcessed = 0 e t.QCode = 'USA' ) e, em seguida, filtrando os dados com base na coluna subid.

Eu esperaria que primeiro filtrasse os dados com base na coluna subid (o que seria muito menor) e, em seguida, aplicasse outros filtros, exatamente para o que serve o NCX_2.

Eu tentei algumas otimizações aqui que melhoraram o desempenho, mas quero entender esse comportamento estranho pelo menos para mim.

  1. Quando adiciono dica de junção de mesclagem na consulta, a consulta é executada muito rapidamente (100 ms)
  2. Quando adiciono dica de índice (NCX_2) na consulta, a consulta também é executada muito rapidamente (60 ms)
  3. Quando modifico a consulta para fazer MIN(t.QueueItemID) e removo a ordem por consulta novamente, é executado muito rápido (60 ms)

Não sei por que o otimizador não o escolheu por padrão.

t-sql sql-server-2008-r2

1 respostas

  1. Best Answer

    Você diz

    O plano acima é para subid = 11 ou 7 na variável de tabela @t

    Eu acho que você pode estar sob um equívoco aqui. O SQL Server não examina o conteúdo da variável de tabela e escolhe um plano com base nos valores que ela contém.

    A instrução é compilada antes que a variável da tabela contenha quaisquer linhas e você obterá o mesmo plano (que assume uma única linha), independentemente de conter eventualmente 2(e corresponderia a 95,5% das linhas) ou 1(e corresponderia apenas a 0.0008%) .

    A variável da tabela também pode conter várias linhas, mas o SQL Server não levará em conta isso, exceto se você usar a OPTION (RECOMPILE)dica e, mesmo assim, não houver estatísticas nas variáveis ​​da tabela, portanto, não poderá levar em consideração os valores reais.

    Alguns planos alternativos estão abaixo

    plano 1

    plano 2

    Isso requer encontrar todas as linhas correspondentes e classificá-las.

    Como NCx_1não é declarado como um índice exclusivo, ele include(QueueItemID)é ignorado (conforme explicado em Mais sobre chaves de índice não clusterizado ) e , em vez disso, é adicionado como uma coluna de chaveQueueItemID de índice . Isso significa que o SQL Server pode buscar on e as linhas correspondentes serão ordenadas por .IsProcessed, QCodeQueueItemID

    O plano em sua pergunta, portanto, evita uma operação de classificação, mas o desempenho depende inteiramente de quantas linhas na prática precisam ser avaliadas antes que a primeira correspondente ao SubID IN (SELECT SubID FROM @t)predicado seja encontrada e a busca de intervalo possa parar.

    Claro que isto pode variar muito dependendo de quão comuns são os SubIDvalores contidos em @te se existe alguma distorção na distribuição destes valores em relação a QueueItemID(Você diz que cerca de 350k linhas correspondem ao predicado de busca e que cerca de 350k acabam sendo procurados então SubID = 7, parece que todas estão no final ou talvez nenhuma linha corresponda - o que seria o pior caso para este plano).

    Seria interessante saber qual é o número estimado de linhas que saem da busca. Presumivelmente, isso é muito menos do que 350.000 e, portanto, o SQL Server escolhe o plano que você vê com base nesse custo estimado.

    Se a variável da tabela sempre tiver apenas algumas linhas, você pode achar que essa reescrita funciona melhor para você.

    SELECT TOP 1 QueueItemID
FROM   @t
       CROSS APPLY (SELECT TOP 1 t.QueueItemID
                    FROM   QueueTable t
                    WHERE  t.IsProcessed = 0
                           AND t.QCode = 'USA'
                           AND SubID = [@t].SubID
                    ORDER  BY t.QueueItemID) CA 
ORDER BY QueueItemID

    Para mim, dá o plano abaixo, onde ele busca o índice subid,isprocessed,qcode,queueitemidquantas vezes você tiver linhas na variável da tabela. É semelhante ao primeiro plano mostrado, mas pode ser um pouco mais eficiente, pois cada busca é interrompida após o retorno da primeira linha.

    plano

    • 4

relate perguntas