Início / dba / Perguntas / 23465
Accepted
Nate
Asked: 2012-08-31 11:24:52 +0800 CST

É possível aumentar o desempenho da consulta em uma tabela estreita com milhões de linhas?

  • 772

Eu tenho uma consulta que está levando em média 2500ms para ser concluída. Minha tabela é muito estreita, mas tem 44 milhões de linhas. Quais opções eu tenho para melhorar o desempenho, ou isso é o melhor possível?

A pergunta

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

A mesa

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsPUp] [bit] NOT NULL,
    [IsWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

O índice

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] 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 [PRIMARY]

Adicionar índices adicionais ajudaria? Se sim, como eles seriam? O desempenho atual é aceitável, porque a consulta é executada apenas ocasionalmente, mas estou pensando como um exercício de aprendizado, há algo que eu possa fazer para tornar isso mais rápido?

ATUALIZAR

Quando altero a consulta para usar uma dica de índice de força, a consulta é executada em 50 ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats] WITH(INDEX(CommonQueryIndex))
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'

Adicionar uma cláusula DeviceID seletiva corretamente também atinge o intervalo de 50 ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' AND DeviceID = 4;

Se eu adicionar ORDER BY [DateEntered], [DeviceID]à consulta original, estou na faixa de 50ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

Todos eles usam o índice que eu esperava (CommonQueryIndex), portanto, suponho que minha pergunta agora seja: existe uma maneira de forçar esse índice a ser usado em consultas como essa? Ou o tamanho da minha tabela está atrapalhando demais o otimizador e devo apenas usar um ORDER BYou uma dica?

sql-server optimization

4 respostas

  1. Best Answer

    Por que o otimizador não vai para o seu primeiro índice:

    CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] 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 [PRIMARY]

    É uma questão de seletividade da coluna [DateEntered].

    Você nos disse que sua tabela tem 44 milhões de linhas. o tamanho da linha é:

    4 bytes para o ID, 4 bytes para o Device ID, 8 bytes para a data e 1 byte para as colunas de 4 bits. isso é 17 bytes + 7 bytes de sobrecarga para (tags, bitmap nulo, deslocamento de coluna var, contagem de col) totaliza 24 bytes por linha.

    Isso se traduziria aproximadamente em 140 mil páginas. Para armazenar essas 44 milhões de linhas.

    Agora o otimizador pode fazer duas coisas:

    1. Ele pode verificar a tabela (varredura de índice clusterizado)
    2. Ou pode usar seu index. Para cada linha em seu índice, seria necessário fazer uma pesquisa de marcador no índice clusterizado.

    Agora, em um determinado ponto, torna-se mais caro fazer todas essas pesquisas únicas no índice clusterizado para cada entrada de índice encontrada em seu índice não clusterizado. O limite para isso geralmente é que a contagem total de pesquisas deve exceder 25% a 33% da contagem total de páginas da tabela.

    Portanto, neste caso: 140k/25%=35.000 linhas 140k/33%=46666 linhas.

    (@RBarryYoung, 35k é 0,08% do total de linhas e 46666 é 0,10%, então acho que é aí que estava a confusão)

    Portanto, se sua cláusula where resultar em algo entre 35.000 e 46.666 linhas. (isso está abaixo da cláusula superior!) É muito provável que seu não clusterizado não seja usado e que a varredura de índice clusterizado seja usada.

    As duas únicas maneiras de mudar isso são:

    1. Torne sua cláusula where mais seletiva. (se possível)
    2. Solte o * e selecione apenas algumas colunas para que você possa usar um índice de cobertura.

    agora, certifique-se de que você pode criar um índice de cobertura mesmo quando usar um select *. No entanto, isso apenas cria uma sobrecarga enorme para suas inserções/atualizações/exclusões. Teríamos que saber mais sobre sua carga de trabalho (leitura versus gravação) para garantir que essa seja a melhor solução.

    Mudar de datetime para smalldatetime é uma redução de 16% no tamanho do índice clusterizado e uma redução de 24% no tamanho do índice não clusterizado.

    • 15

  2. Existe um motivo específico para o seu PK estar em cluster? Muitas pessoas fazem isso porque o padrão é assim ou acham que os PKs devem ser agrupados. Não. Os índices clusterizados geralmente são melhores para consultas de intervalo (como esta) ou na chave estrangeira de uma tabela filho.

    Um efeito de um índice de agrupamento é que ele agrupa todos os dados porque os dados são armazenados nos nós folha da árvore do cluster b. Portanto, supondo que você não esteja solicitando um intervalo 'muito amplo', o otimizador saberá exatamente qual parte da árvore b contém os dados e não precisará encontrar um identificador de linha e, em seguida, pular para onde os dados é (como acontece ao lidar com um índice NC). O que é 'muito amplo' de um intervalo? Um exemplo ridículo seria pedir 11 meses de dados de uma tabela que tem apenas um ano de registros. Puxar um dia de dados não deve ser um problema, supondo que suas estatísticas estejam atualizadas. (No entanto, o otimizador pode ter problemas se você estiver procurando os dados de ontem e não tiver atualizado as estatísticas por três dias.)

    Como você está executando uma consulta "SELECT *", o mecanismo precisará retornar todas as colunas da tabela (mesmo que alguém adicione uma nova que seu aplicativo não precise naquele momento), portanto, um índice de cobertura ou um índice com colunas incluídas não ajudará muito, se for o caso. (Se você incluir todas as colunas da tabela em um índice, está fazendo algo errado.) O otimizador provavelmente ignorará esses índices NC.

    Então o que fazer?

    Minha sugestão seria descartar o índice NC, alterar o PK clusterizado para não clusterizado e criar um índice clusterizado em [DateEntered]. Mais simples é melhor, até que se prove o contrário.

    • 8

  3. Contanto que você tenha esse "*" lá, a única coisa que eu poderia imaginar que faria muita diferença seria alterar sua definição de índice para isto:

    CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)INCLUDE (ID, IsWebUp, IsPingUp, IsPUp)
 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 [PRIMARY]

    Como observei nos comentários, ele deve usar esse índice, mas se não, você pode convencê-lo com um ORDER BY ou uma dica de índice.

    • 4

  4. Eu olharia para isso um pouco diferente.

    • Sim, eu sei que é um tópico antigo, mas estou intrigado.

    Eu despejaria a coluna datetime - mudaria para um int. Tenha uma tabela de pesquisa ou faça uma conversão para sua data.

    Despeje o índice clusterizado - deixe-o como um heap e crie um índice não clusterizado na nova coluna INT que representa a data. ou seja, hoje seria 20121015. Essa ordem é importante. Dependendo da frequência com que você carrega a tabela, veja como criar esse índice na ordem DESC. O custo de manutenção será maior e você desejará introduzir um fator de preenchimento ou particionamento. O particionamento também ajudaria a diminuir o tempo de execução.

    Por fim, se você puder usar o SQL 2012, tente usar SEQUENCE - ele superará o identity() para inserções.

    • 3

relate perguntas