Início / dba / Perguntas / 201660
Accepted
Steffen Mangold
Asked: 2018-03-20 04:55:55 +0800 CST

Otimize o índice em uma tabela de 2.135.044.521 linhas

  • 772

Eu tenho um problema de E/S com uma tabela grande.

Estatísticas gerais

A mesa tem as seguintes características principais:

  • ambiente: Banco de Dados SQL do Azure (camada é P4 Premium (500 DTUs))
  • linhas: 2.135.044.521
  • 1.275 partições usadas
  • índice clusterizado e particionado

Modelo

Esta é a implementação da tabela:

CREATE TABLE [data].[DemoUnitData](
    [UnitID] [bigint] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [Value1] [decimal](18, 2) NULL,
    [Value2] [decimal](18, 2) NULL,
    [Value3] [decimal](18, 2) NULL,
    CONSTRAINT [PK_DemoUnitData] PRIMARY KEY CLUSTERED 
    (
        [UnitID] ASC,
        [Timestamp] ASC
    )
)
GO

ALTER TABLE [data].[DemoUnitData] WITH NOCHECK ADD CONSTRAINT [FK_DemoUnitData_Unit] FOREIGN KEY([UnitID])
REFERENCES [model].[Unit] ([ID])
GO

ALTER TABLE [data].[DemoUnitData] CHECK CONSTRAINT [FK_DemoUnitData_Unit]
GO

O particionamento está relacionado a isso:

CREATE PARTITION SCHEME [DailyPartitionSchema] AS PARTITION [DailyPartitionFunction] ALL TO ([PRIMARY])

CREATE PARTITION FUNCTION [DailyPartitionFunction] (datetime) AS RANGE RIGHT
FOR VALUES (N'2017-07-25T00:00:00.000', N'2017-07-26T00:00:00.000', N'2017-07-27T00:00:00.000', ... )

Qualidade de serviço

Eu acho que os índices e estatísticas são bem mantidos todas as noites por reconstrução/reorganização/atualização incremental.

Estas são as estatísticas de índice atuais das partições de índice mais usadas:

Estatísticas da partição

Estas são as propriedades estatísticas atuais das partições mais usadas:

Estatisticas

Problema

Eu executo uma consulta simples em alta frequência na tabela.

SELECT [UnitID]
    ,[Timestamp]
    ,[Value1]
    ,[Value2]
    ,[Value3]
FROM [data].[DemoUnitData]
WHERE [UnitID] = 8877 AND [Timestamp] >= '2018-03-01' AND [Timestamp] < '2018-03-13'
OPTION (MAXDOP 1)

contagem excecional

O plano de execução fica assim: https://www.brentozar.com/pastetheplan/?id=rJvI_4TtG

Meu problema é que essas consultas produzem uma quantidade extremamente alta de operações de E/S, resultando em um gargalo de PAGEIOLATCH_SHesperas.

melhores esperas

Pergunta

Eu li que PAGEIOLATCH_SHas esperas geralmente estão relacionadas a índices não bem otimizados. Você tem alguma recomendação para mim sobre como reduzir as operações de E/S? Talvez adicionando um índice melhor?

Resposta 1 - relacionada ao comentário de @S4V1N

O plano de consulta postado foi de uma consulta que executei no SSMS. Após seu comentário, faço uma pesquisa sobre o histórico do servidor. A consulta real executada do serviço parece um pouco diferente (relacionada ao EntityFramework).

(@p__linq__0 bigint,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)) 

SELECT 1 AS [C1], [Extent1] 
   .[Timestamp] AS [Timestamp], [Extent1] 
   .[Value1] AS [Value1], [Extent1] 
   .[Value2] AS [Value2], [Extent1] 
   .[Value3] AS [Value3]  
FROM [data].[DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2) OPTION (MAXDOP 1)

Além disso, o plano parece diferente:

https://www.brentozar.com/pastetheplan/?id=H1fhALpKG

ou

https://www.brentozar.com/pastetheplan/?id=S1DFQvpKz

E como você pode ver aqui, nosso desempenho de banco de dados dificilmente é influenciado por essa consulta.

SQL principal

Resposta 2 - relacionada à resposta de @Joe Obbish

Para testar a solução, substituí o Entity Framework por um SqlCommand simples. O resultado foi um incrível aumento de desempenho!

O plano de consulta agora é o mesmo do SSMS e as leituras e gravações lógicas caem para ~8 por execução.

A carga geral de E/S cai para quase 0! Queda de E/S

Também explica por que recebo uma grande queda de desempenho depois de alterar o intervalo de partição de mensal para diário. A falta de eliminação de partição resultou em mais partições para verificar.

sql-server performance

1 respostas

  1. Best Answer

    Você poderá reduzir as PAGEIOLATCH_SHesperas por essa consulta se conseguir alterar os tipos de dados gerados pelo ORM. A Timestampcoluna em sua tabela tem um tipo de dados de DATETIMEmas os parâmetros @p__linq__1e @p__linq__2tem tipos de dados de DATETIME2(7). Essa diferença é o motivo pelo qual o plano de consulta para as consultas ORM é muito mais complicado do que o primeiro plano de consulta que você postou que tinha filtros de pesquisa codificados. Você pode obter uma dica disso no XML também:

    <ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@p__linq__1],NULL,(22))">

    Como está, com a consulta ORM você não pode obter nenhuma eliminação de partição. Você obterá pelo menos algumas leituras lógicas para cada partição definida na função de partição, mesmo se estiver apenas procurando por um dia de dados. Dentro de cada partição, você obtém uma busca de índice para que não demore muito para o SQL Server passar para a próxima partição, mas talvez todo esse IO esteja somando.

    Fiz uma reprodução simples para ter certeza. Existem 11 partições definidas na função de partição. Para esta consulta:

    DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime2(7) = '20180103';
DECLARE @p__linq__2 datetime2(7) = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

    Veja como é o IO:

    Tabela 'DemoUnitData'. Contagem de varredura 11, leituras lógicas 40

    Quando eu corrijo os tipos de dados:

    DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime = '20180103';
DECLARE @p__linq__2 datetime = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

    O IO é reduzido como resultado da eliminação da partição:

    Tabela 'DemoUnitData'. Contagem de varredura 2, leituras lógicas 8

    • 7

relate perguntas