Блог

У меня относительно большая база данных, используемая для данных IoT, примерно около 60 миллионов записей. Вставки выполняются очень часто при МАССОВЫХ вставках из Stream Analytics.

Это моя схема таблицы:

 CREATE TABLE [dbo].[NVEControllerReadings](
[DeviceUniqueIdentifier] [nvarchar](100) NOT NULL,
[NVEControllerTimestamp] [datetimeoffset](7) NOT NULL,
[ProcessedInAzureUtc] [datetimeoffset](7) NOT NULL,
[ParameterTypeId] [int] NULL,
[InstanceId] [int] NULL,
[ParameterNumberId] [int] NOT NULL,
[ParameterValue] [float] NULL,
[ParameterText] [nvarchar](255) NULL)
  

При выполнении запросов мы всегда ищем последние записи для устройства, поэтому у меня есть следующий кластеризованный индекс:

 CREATE CLUSTERED INDEX [IX_NVEControllerReadings] ON [dbo].[NVEControllerReadings](
[DeviceUniqueIdentifier] ASC,
[NVEControllerTimestamp] DESC)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
  

У меня также есть некластеризованный индекс для покрытия индексов, ориентированных на ParameterTypeId, ParameterNumberId и InstanceID.

 CREATE NONCLUSTERED INDEX [IX_ParameterTypeId_ParameterNumberId_InstanceId] ON [dbo].[NVEControllerReadings](
[ParameterTypeId] ASC,
[ParameterNumberId] ASC,
[InstanceId] ASC) INCLUDE (     [ParameterValue]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
  

Значение параметра включено в качестве ключевого столбца, поскольку это конечный результат запроса, меня это интересует.

Однако при выполнении запросов, ориентированных на мой некластеризованный индекс, возврат одного результата может занять 3-5 минут, чего я не понимаю. Согласно моему плану исключения, некластеризованный индекс используется по назначению при поиске по индексу.

Вот ссылка на план выполнения:https://www.brentozar.com/pastetheplan/?id=r1NAwrRUN (Выполнение запроса заняло 03:32).

Я пытался перестроить свои индексы для достижения более низкой скорости фрагментации и обновления статистики, но пока безуспешно.

Кто-нибудь может указать мне направление к моей проблеме?

Заранее спасибо.

Вы по-прежнему выбираете каждую строку, а затем сортируете ее, но возвращается только первая строка. Попробуйте использовать его с другой стороны, используйте агрегатную функцию, чтобы ограничить выбор одной строкой, что-то вроде:

 SELECT [ParameterValue]
FROM [dbo].[NVEControllerReadings] n1
  join (select max(NVEControllerTimestamp) as Mostrecent, DeviceUniqueIdentifier
        from [dbo].[NVEControllerReadings] 
        WHERE DeviceUniqueIdentifier = '04EFB80706A7'
          AND ParameterTypeId = 19 AND ParameterNumberId = 24
          AND InstanceId = 1
        Group by DeviceUniqueIdentifier) n2 on n2.DeviceUniqueIdentifier = n1.DeviceUniqueIdentifier
     and n1.timestamp = n2.Mostrecent
  

По моему опыту, производительность Azure может сильно упасть, и вам часто придется пробовать множество разных перестановок в запросах. Это связано с тем, что под внешней оболочкой sql azure находится нечто, сильно отличающееся от локального экземпляра SQL server. Например, ваше решение с первичным ключом, вероятно, не работает, поскольку оно не хранит данные на страницах, расположенных в порядке кластеризации на физическом диске. В любом случае, надеюсь, это поможет!

Для этого запроса:

 SELECT TOP (1) [ParameterValue]
FROM [dbo].[NVEControllerReadings]
WHERE DeviceUniqueIdentifier = '04EFB80706A7' AND
      ParameterTypeId = 19 AND
      ParameterNumberId = 24 AND
      InstanceId = 1
ORDER BY NVEControllerTimestamp desc;
  

Включен оптимальный индекс (DeviceUniqueIdentifier, ParameterTypeId, ParameterNumberId, InstanceId, NVEControllerTimestamp desc) . Я бы попробовал это в первую очередь.