Andrew Williamson's questions

Eu tenho uma tabela grande (300 milhões de linhas), com cerca de 1 milhão de linhas adicionadas por dia. Estes são dados de série temporal, então eu tenho a seguinte configuração:

CREATE TABLE Readings (
    Id INT PRIMARY KEY,
    TrackerId INT,
    DateProcessed DATETIME,
    -- About 10 other columns of data
)

CREATE INDEX IX_TrackerId_DateProcessed
ON Readings (
    TrackerId,
    DateProcessed
) INCLUDE (
    -- all the other columns
) WHERE (
    TrackerId IS NOT NULL
)

Quando visualizamos os dados, geralmente executo algo como a seguinte consulta:

SELECT Latest.*
FROM Trackers
CROSS APPLY (
    SELECT TOP 1 *
    FROM Readings
    WHERE TrackerId = Trackers.Id
    ORDER BY DateProcessed DESC
) Latest

Isso costumava funcionar bem. Agora, leva uma eternidade para gerar o plano de consulta, mesmo quando eu digo explicitamente para usar o índice. A única solução que encontrei até agora é atualizar as estatísticas da tabela com o seguinte comando:

UPDATE STATISTICS Readings

Isso leva cerca de dez minutos para ser executado e, em seguida, a consulta de seleção é executada bem, mas apenas por um dia . Então eu tenho que atualizar as estatísticas novamente. Por que as estatísticas estão desatualizadas tão rapidamente e o que posso fazer para evitar isso?

Pontos que merecem destaque:

• Eu só acrescento à tabela, nunca atualizo ou excluo linhas
• Existem algumas linhas antigas com um valor nulo para o ID do rastreador, mas não serão adicionadas mais
• O banco de dados está sendo executado no nível de compatibilidade 14
• Não consigo habilitar o sinalizador de rastreamento 2371 no Azure SQL

Respostas adicionais para perguntas nos comentários

O plano de consulta estimado antes de atualizar as estatísticas

O plano de consulta estimado após a atualização das estatísticas

Os planos antes e depois de atualizar as estatísticas parecem idênticos para mim. O problema não é que estou recebendo um plano de consulta ruim, mas quanto tempo leva para obter o plano. Antes de atualizar as estatísticas, tive que esperar dois minutos pelo plano. Após atualizar as estatísticas, obteve o plano instantaneamente.

Enquanto escrevia isso, percebi que seria mais rápido esperar pela consulta e deixar o cache fazer o trabalho, em vez de atualizar as estatísticas, mas isso ainda não está resolvendo o problema. Essa consulta é executada de dentro do Entity Framework, com um tempo limite de 30 segundos, portanto, dois minutos ainda é muito tempo.

Parece uma pergunta tão comum, eu entenderei se estiver fechado, mas se estiver, sugira um lugar melhor para eu perguntar. Tenho as seguintes duas tabelas de interesse:

CREATE TABLE [dbo].[Sessions]
(
    [Id] [int] PRIMARY KEY,
    [DateConnected] [datetime] NOT NULL,
    [Origin] [nvarchar](max) NULL,
    [TrackerId] [int] NULL,
    [Imei] [nvarchar](max) NULL,
    [Sim] [nvarchar](max) NULL,
    [ProtocolVersion] [tinyint] NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
    [Id] [int] PRIMARY KEY,
    [RequestId] [int] NULL,
    [SessionId] [int] NOT NULL,
    [DateProcessed] [datetime] NOT NULL,
    [Direction] [int] NOT NULL,
    [Sequence] [int] NOT NULL,
    [Acknowledgement] [int] NOT NULL,
    [DateRecorded] [datetime] NOT NULL,
    [Version] [tinyint] NOT NULL,
    [Command] [tinyint] NOT NULL,
    [Flags] [tinyint] NOT NULL,
    [Checksum] [tinyint] NOT NULL,
    [Data] [varbinary](max) NULL
)

CREATE NONCLUSTERED INDEX [IX_TrackerId_DateConnected] ON [dbo].[Sessions]
(
    [TrackerId] ASC,
    [DateConnected] ASC
)

CREATE NONCLUSTERED INDEX [IX_SessionId_DateProcessed] ON [dbo].[PacketTransmissions]
(
    [SessionId] ASC,
    [DateProcessed] ASC
)
INCLUDE ([Direction], [Sequence], [Acknowledgement], [Command])

Minha consulta mais comum e mais cara (muitas vezes expira agora) envolve listar todas as transmissões de pacotes para um rastreador específico.

DECLARE @TrackerId INT = 10
DECLARE @StartDate DATETIME2 = '2018-03-10'
DECLARE @EndDate   DATETIME2 = '2018-03-12'

SELECT [PacketTransmissions].*
FROM [Sessions]
JOIN [PacketTransmissions] ON [PacketTransmissions].[SessionId] = [Sessions].[Id]
WHERE [Sessions].[TrackerId] = @TrackerId
AND [PacketTransmissions].[DateProcessed] > @StartDate
AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC

Isso foi bom no começo, mas agora há muitos dados, diminuiu a velocidade. Minha tentativa de obter o plano de consulta hoje levou 2 minutos e mostra que ele estará usando um table scan , em vez do índice que criei. Mesmo quando eu forço o index , ainda é muito lento.

Em comparação, se eu escolher uma sessão primeiro e pesquisar apenas as transmissões de pacotes gravadas nessa sessão, a consulta usará o índice e será incrivelmente rápida.

Minha tentativa mais bem-sucedida de acelerar a consulta foi ordenar os resultados primeiro por id de sessão, depois por data de processamento, para corresponder à ordem do índice. Embora isso nem sempre seja tecnicamente correto, é aceitável. No entanto, mesmo isso começou a expirar, e sinto que há algo errado com minha compreensão de como fazer o JOINmais rápido.

O que posso fazer para melhorar o desempenho desta consulta?

Consultar com DATETIMEvariáveis ​​em vez de DATETIME2simplificou o plano de consulta, porém ainda é muito lento.

• As sessões têm 265.929 linhas

• PacketTransmissions tem 32.916.233 linhas

  Isso resulta em 123,7 pacotes por sessão, em média.

• Algumas das sessões são para dispositivos não registrados, então eles criam uma sessão, enviam entre um e três pacotes e, em seguida, a sessão é rejeitada pelo servidor.

• Normalmente estarei depurando um dispositivo registrado, então o número real de pacotes por sessão é consideravelmente maior, entre 300 e 5000 pacotes por sessão

• Alguns rastreadores podem manter a mesma sessão por um mês de cada vez se tiverem conectividade

No passado, tive uma experiência ruim ao alterar o índice clusterizado para usar uma chave não sequencial. Isso resulta em muitas gravações fora de ordem e divisões de página, e o desempenho da inserção cai significativamente.

O problema com os planos de execução reais é que não quero executar o banco de dados no máximo DTU por até uma hora e, potencialmente, ter inserções falhando nesse meio tempo.

Acabei de ter uma situação em que tive que incluir uma coluna em um índice. Isso exigia descartar o índice e recriá-lo. Isso me fez pensar, certamente isso é muito trabalho desnecessário.

Digamos que eu tenha criado o novo índice primeiro e depois descartado o antigo. Vamos supor também que eu tenha alguma forma de indicar que o índice antigo não será descartado até que o novo tenha sido criado.

O servidor obteria algum benefício de desempenho usando o índice antigo ao criar o novo?

Digamos que eu tenha uma tabela particionada, configurada assim:

CREATE PARTITION FUNCTION PF_Month (DATE) AS RANGE RIGHT FOR VALUES (
  '2017-01-01',
  '2017-02-01',
  '2017-03-01',
  '2017-04-01',
  '2017-05-01',
  '2017-06-01',
);
GO

CREATE PARTITION SCHEME PS_Month AS PARTITION PF_Month ALL TO ([Primary]);
GO

CREATE TABLE Logs
(
     Id           INT NOT NULL,
     DateRecorded DATE NOT NULL,
     FixStatus    INT NOT NULL
);
GO

ALTER TABLE Logs
    ADD CONSTRAINT PK_Logs PRIMARY KEY (Id, DateRecorded)
    ON PS_Month(DateRecorded);
GO

CREATE NONCLUSTERED INDEX [IX_DateRecorded] ON Logs(DateRecorded)
    INCLUDE(FixStatus)
    ON PS_Month(DateRecorded);
GO

Se eu quiser consultar os logs, em ordem de data, me disseram que posso usar o número da partição para evitar uma classificação quando os resultados de cada partição forem reunidos novamente.

SELECT * FROM Logs WHERE ... ORDER BY $PARTITION.PF_Month(DateRecorded), DateRecorded

Os números das partições estão na ordem de quando cada partição foi criada ou estão na ordem de DateRecorded? Por exemplo, se eu adicionasse outra divisão à função enquanto ela estiver em uso, a ordenação por número de partição ainda funcionaria?

ALTER PARTITION FUNCTION PF_Month() SPLIT RANGE ('2016-12-01')

Eu tenho uma tabela que tem um índice clusterizado como abaixo:

CREATE CLUSTERED INDEX [IX_MachineryId_DateRecorded]
ON Machinery (MachineryId, DateRecorded)

Estou selecionando com base nos campos do índice clusterizado, além de mais um:

SELECT DateRecorded, Latitude, Longitude
FROM MachineryReading
WHERE MachineryId = 2127        -- First key in the index
AND DateRecorded > '2017-01-10' -- Second key in the index
AND DateRecorded < '2017-10-16' -- Second key in the index
AND FixStatus >= 2              -- Not a key, resulting in a scan
ORDER BY DateRecorded

Eu esperava que isso resultasse em uma simples varredura de índice clusterizado. No entanto, observando as estatísticas de consulta ao vivo e o plano de execução real, a maior parte do tempo de execução da consulta vem da classificação dos resultados após a verificação do índice. Por que os dados ordenados estão sendo classificados novamente?

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

Eu tenho uma tabela com uma coluna id que é incrementada automaticamente e várias outras colunas informativas. As linhas são inseridas nesta tabela com muita frequência. Quando os dados são lidos, a maioria das consultas é filtrada por uma chave estrangeira e um intervalo de datas.

Atualmente, há um índice clusterizado na coluna id e um índice não clusterizado nas duas colunas de importância (TrackerId e DateRecorded). Se eu trocar os índices, nossas consultas serão muito mais rápidas. Isso afetaria negativamente os tempos de inserção?

Eu estraguei as coisas no trabalho (oh não!). Tudo estava indo bem até cerca de dois dias atrás; consultas executadas instantaneamente, e o banco de dados tendia a ficar em torno de 20% da carga. Então me disseram que alguns de nossos dados históricos no banco de dados foram inseridos incorretamente e precisavam ser removidos e reinseridos nas seguintes tabelas:

CREATE TABLE TrackerStates(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TrackerId] [int] NOT NULL,
    [DateRecorded] [datetime] NOT NULL,
    [Latitude] [float] NOT NULL,
    [Longitude] [float] NOT NULL,
    [Altitude] [float] NOT NULL,
    -- Some other fields
    CONSTRAINT [PK_dbo.TrackerStates] PRIMARY KEY NONCLUSTERED  (Id)
)

CREATE CLUSTERED INDEX IX_TrackerId_DateRecorded_Processed ON TrackerStates (TrackerId, DateRecorded, Processed)
CREATE NONCLUSTERED INDEX IX_TrackerId_Processed ON TrackerStates (TrackerId) WHERE (Processed = 0)

CREATE TABLE TrackingPoints(
    [DatabaseId] [int] NOT NULL,
    [TrackerStateId] [int] NOT NULL,
    [RoadId] [int] NOT NULL,
    [Distance] [float] NOT NULL,
    PRIMARY KEY CLUSTERED (DatabaseId, TrackerStateId),
    FOREIGN KEY (DatabaseId) REFERENCES RammDatabases (Id),
    FOREIGN KEY (TrackerStateId) REFERENCES TrackerStates (Id)
)
CREATE NONCLUSTERED INDEX IX_DatabaseId ON TrackingPoints (DatabaseId)
CREATE NONCLUSTERED INDEX IX_TrackerStateId ON TrackingPoints (TrackerStateId)

CREATE TABLE TrackingSegments(
    [DatabaseId] [int] NOT NULL,
    [StartingStateId] [int] NOT NULL,
    [EndingStateId] [int] NOT NULL,
    [EntityId] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED (DatabaseId, StartingStateId, EndingStateId),
    FOREIGN KEY (DatabaseId) REFERENCES RammDatabases (Id),
    FOREIGN KEY (StartingStateId) REFERENCES TrackerStates (Id),
    FOREIGN KEY (EndingStateId) REFERENCES TrackerStates (Id),
    FOREIGN KEY (DatabaseId, StartingStateId) REFERENCES TrackingPoints (DatabaseId, TrackerStateId),
    FOREIGN KEY (DatabaseId, EndingStateId) REFERENCES TrackingPoints (DatabaseId, TrackerStateId)
)
CREATE NONCLUSTERED INDEX IX_DatabaseId ON TrackingSegments (DatabaseId)
CREATE NONCLUSTERED INDEX IX_DatabaseId_StartingStateId ON TrackingSegments (DatabaseId, StartingStateId)
CREATE NONCLUSTERED INDEX IX_DatabaseId_EndingStateId ON TrackingSegments (DatabaseId, EndingStateId)

Minha primeira tentativa de excluir os dados simplesmente demorou muito - são dados históricos, então há alguns milhões de linhas. Depois que isso falhou, perguntei por aí e alguém sugeriu desabilitar restrições de verificação, excluir e reativar as restrições para essas tabelas específicas (o que NUNCA farei novamente, péssima ideia). A desativação e a exclusão foram executadas rapidamente, tive que deixar a ativação em execução a noite toda, mas foi bem-sucedida.

A partir de então, o banco de dados está com 100% de CPU e uma das consultas ocasionalmente nunca é concluída. É a mesma consulta todas as vezes:

SELECT TOP 500 * FROM TrackerStates WHERE TrackerId = @TrackerId AND Processed = 0 ORDER BY DateRecorded

Essa consulta usa o índice IX_TrackerId_DateRecorded_Processede geralmente não leva tempo para ser executada. Ocasionalmente, porém, uma consulta ficará lá executando até atingir o tempo limite (após 30 segundos).

Até agora, tentei:

• Escalar o banco de dados em até 5 vezes o tamanho (menos tempos limite, mas ainda com 100% de uso da CPU)
• Reconstruindo os índices, pois estavam fragmentados para cerca de 80% (sem diferença)
• Alterando a consulta para o nível de isolamento READ UNCOMMITTED(sem diferença)

O que eu poderia fazer para consertar isso?

Aqui está o resumo: estou fazendo uma consulta de seleção. Todas as colunas nas cláusulas WHEREe ORDER BYestão em um único índice não clusterizado IX_MachineryId_DateRecorded, como parte da chave ou como INCLUDEcolunas. Estou selecionando todas as colunas, para que isso resulte em uma pesquisa de favoritos, mas estou apenas usando TOP (1), então certamente o servidor pode dizer que a pesquisa só precisa ser feita uma vez, no final.

Mais importante, quando forço a consulta a usar index IX_MachineryId_DateRecorded, ela é executada em menos de um segundo. Se eu deixar o servidor decidir qual índice usar, ele escolherá IX_MachineryIde levará até um minuto. Isso realmente sugere para mim que fiz o índice certo e o servidor está apenas tomando uma decisão ruim. Por quê?

CREATE TABLE [dbo].[MachineryReading] (
    [Id]                 INT              IDENTITY (1, 1) NOT NULL,
    [Location]           [sys].[geometry] NULL,
    [Latitude]           FLOAT (53)       NOT NULL,
    [Longitude]          FLOAT (53)       NOT NULL,
    [Altitude]           FLOAT (53)       NULL,
    [Odometer]           INT              NULL,
    [Speed]              FLOAT (53)       NULL,
    [BatteryLevel]       INT              NULL,
    [PinFlags]           BIGINT           NOT NULL,
    [DateRecorded]       DATETIME         NOT NULL,
    [DateReceived]       DATETIME         NOT NULL,
    [Satellites]         INT              NOT NULL,
    [HDOP]               FLOAT (53)       NOT NULL,
    [MachineryId]        INT              NOT NULL,
    [TrackerId]          INT              NOT NULL,
    [ReportType]         NVARCHAR (1)     NULL,
    [FixStatus]          INT              DEFAULT ((0)) NOT NULL,
    [AlarmStatus]        INT              DEFAULT ((0)) NOT NULL,
    [OperationalSeconds] INT              DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_dbo.MachineryReading] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.MachineryReading_dbo.Machinery_MachineryId] FOREIGN KEY ([MachineryId]) REFERENCES [dbo].[Machinery] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.MachineryReading_dbo.Tracker_TrackerId] FOREIGN KEY ([TrackerId]) REFERENCES [dbo].[Tracker] ([Id]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_MachineryId]
    ON [dbo].[MachineryReading]([MachineryId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_TrackerId]
    ON [dbo].[MachineryReading]([TrackerId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_MachineryId_DateRecorded]
    ON [dbo].[MachineryReading]([MachineryId] ASC, [DateRecorded] ASC)
    INCLUDE([OperationalSeconds], [FixStatus]);

A tabela é particionada em intervalos de meses (embora eu ainda não entenda realmente o que está acontecendo lá).

ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-01-01T00:00:00.000') 

ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-02-01T00:00:00.000') 
...

CREATE UNIQUE CLUSTERED INDEX [PK_dbo.MachineryReadingPs] ON MachineryReading(DateRecorded, Id) ON PartitionSchemeMonthRange(DateRecorded)

A consulta que eu normalmente executaria:

SELECT TOP (1) [Id], [Location], [Latitude], [Longitude], [Altitude], [Odometer], [ReportType], [FixStatus], [AlarmStatus], [Speed], [BatteryLevel], [PinFlags], [DateRecorded], [DateReceived], [Satellites], [HDOP], [OperationalSeconds], [MachineryId], [TrackerId]
    FROM [dbo].[MachineryReading]
    --WITH(INDEX(IX_MachineryId_DateRecorded)) --This makes all the difference
    WHERE ([MachineryId] = @p__linq__0) AND ([DateRecorded] >= @p__linq__1) AND ([DateRecorded] < @p__linq__2) AND ([OperationalSeconds] > 0)
    ORDER BY [DateRecorded] ASC

Plano de consulta: https://www.brentozar.com/pastetheplan/?id=r1c-RpxNx

Plano de consulta com índice forçado: https://www.brentozar.com/pastetheplan/?id=SywwTagVe

Os planos incluídos são os planos de execução reais, mas no banco de dados de preparação (cerca de 1/100 do tamanho do ao vivo). Estou hesitante em mexer no banco de dados ativo porque comecei nesta empresa há apenas um mês.

Tenho a sensação de que é por causa do particionamento, e minha consulta normalmente abrange todas as partições (por exemplo, quando desejo obter o primeiro ou o último OperationalSecondsregistrado para uma máquina). No entanto, as consultas que tenho escrito à mão estão sendo executadas de 10 a 100 vezes mais rápido do que o EntityFramework gerou, então vou apenas criar um procedimento armazenado.