Медленный запрос Azure SQL при нацеливании на индекс

У меня есть относительно большая БД, используемая для данных IoT, примерно около 60 миллионов записей. Вставки выполняются очень часто в BULK Inserts из 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]

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

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

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

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

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

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


sql sql-server azure-sql-database azure-sql-managed-instance
person Mortenkp25    schedule 07.03.2019    source источник
comment
Моей первой идеей было бы: вы используете неправильный тип хранилища данных для данных, которые вы должны хранить. Взгляните, например, на Data Lake Store. .   -  person rickvdbosch    schedule 07.03.2019

Ответы (2)


arrow_upward
2
arrow_downward

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

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 находится нечто, сильно отличающееся от локального экземпляра SQL-сервера. Например, ваше решение с первичным ключом, вероятно, не работает, потому что оно не хранит данные на страницах, расположенных в порядке кластеризации на физическом диске. Во всяком случае, надеюсь, что это поможет!

person Randall    schedule 07.03.2019
comment
В этом есть смысл. Не лучше ли было бы исключить временную метку из кластеризованного индекса и включить ее в некластеризованный вместо этого как DESC? - person Mortenkp25; 07.03.2019
comment
Это, наверное, лучшая идея. - person Randall; 07.03.2019

arrow_upward
0
arrow_downward

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

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). Я бы попробовал это в первую очередь.

person Gordon Linoff    schedule 07.03.2019
comment
Как насчет моего кластеризованного индекса (DeviceId, NVEControllerTimestamp)? Следует ли это изменить, когда теперь у меня есть некластеризованный индекс с этими ключами? - person Mortenkp25; 07.03.2019
comment
@ Мортенкп25 . . . Вам нужен индекс со всеми четырьмя столбцами в WHERE (по крайней мере), а затем столбец упорядочения. - person Gordon Linoff; 07.03.2019
comment
Я обнаружил, что на самом деле это проблема фрагментации. После перестроения индексов они снова быстро становятся фрагментированными. Я попытаюсь ввести столбец Identity и перепроектировать свой кластерный индекс как составной ключ (DeviceUniqueIdentifer, Id). Спасибо :) - person Mortenkp25; 07.03.2019