Как настроить Azure SQL для автоматического перестроения индексов?

Обновление: обратите внимание, что команда инженеров опубликовала обновленное руководство, чтобы лучше систематизировать некоторые предложения в этом ответе в более официальном месте от Microsoft, поскольку об этом просили некоторые клиенты. Руководство по индексированию SQL Server/БД. Спасибо, Конор

оригинальный ответ:

Я укажу, что большинству людей вообще не нужно думать о перестроении индексов в SQL Azure. Да, индексы дерева B+ могут стать фрагментированными, и да, это может привести к некоторым накладным расходам пространства и некоторым накладным расходам ЦП по сравнению с идеально настроенными индексами. Итак, есть несколько сценариев, когда мы работаем с клиентами над перестроением индексов. (Основной сценарий — это когда у клиента может закончиться свободное место в настоящее время, так как дисковое пространство в SQL Azure несколько ограничено из-за текущей архитектуры). Итак, я призываю вас сделать шаг назад и подумать, что использование модели SQL Server для управления базами данных не является неправильным, но оно может стоить или не стоит ваших усилий.

(Если в конечном итоге вам потребуется перестроить индекс, вы можете использовать модели, размещенные здесь другими авторами, — они, как правило, являются прекрасными моделями для сценариев задач. Обратите внимание, что управляемый экземпляр SQL Azure также поддерживает агент SQL, который вы также можете использовать. для создания заданий для сценариев обслуживания операций, если вы того пожелаете).

Вот некоторые детали, которые могут помочь вам решить, можете ли вы быть кандидатом на перестроение индекса:

• Ссылка, на которую вы ссылаетесь, взята из сообщения 2013 года. Архитектура SQL Azure была полностью переделана после этого сообщения. В частности, аппаратная архитектура перешла от модели, основанной на локальных вращающихся дисках, к модели, основанной на локальных твердотельных накопителях (в большинстве случаев). Таким образом, руководство в исходном сообщении устарело.
• У вас могут быть случаи в текущей архитектуре, когда у вас может не хватить места с фрагментированным индексом. У вас есть варианты перестроить индекс или перейти на больший размер резервирования на некоторое время (что будет стоить больше денег), который поддерживает выделение большего дискового пространства. [Поскольку локальное пространство SSD на машинах ограничено, размеры резервирования примерно связаны с пропорциями машины. По мере того, как мы получаем более новое оборудование с большими/больше дисками, у вас появляется больше возможностей масштабирования].
• Влияние фрагментации SSD относительно невелико по сравнению с вращающимися дисками, поскольку стоимость произвольного ввода-вывода на самом деле не выше, чем последовательного. Затраты процессора на прохождение еще нескольких промежуточных страниц дерева B+ скромны. Я обычно видел накладные расходы, возможно, 5-20% максимум в среднем случае (что может или не может оправдать регулярные перестроения, которые имеют гораздо большее влияние на рабочую нагрузку при перестроении)
• Если вы используете хранилище запросов (которое включено по умолчанию в SQL Azure), вы можете оценить, заметно ли помогает перестроение определенного индекса вашей производительности. Вы можете сделать это в качестве теста, чтобы увидеть, улучшится ли ваша рабочая нагрузка, прежде чем тратить время на создание и управление операциями перестроения индекса самостоятельно.
• Обратите внимание, что в настоящее время в SQL Azure нет управления ресурсами внутри базы данных для пользовательских рабочих нагрузок. Таким образом, если вы начнете перестроение индекса, вы можете в конечном итоге потреблять много ресурсов и влиять на свою основную рабочую нагрузку. Конечно, вы можете попытаться запланировать выполнение задач в нерабочее время, но для приложений с большим количеством клиентов по всему миру это может оказаться невозможным.
• Кроме того, я отмечу, что многие клиенты имеют задания по перестройке индекса, потому что они хотят, чтобы статистика обновлялась. Нет необходимости перестраивать индекс только для перестроения статистики. В недавних версиях SQL Server и SQL Azure алгоритм обновления статистики стал более агрессивным для больших таблиц, а модель того, как мы оцениваем кардинальность в случаях, когда клиенты запрашивают недавно вставленные данные (с момента последнего обновления статистики), была изменена в более поздней совместимости. уровни. Таким образом, часто бывает так, что клиенту вообще не нужно обновлять статистику вручную.
• Наконец, я отмечу, что влияние устаревшей статистики исторически заключалось в том, что вы получали регрессии выбора плана. Для повторяющихся запросов это влияние во многом было смягчено введением функции автоматической настройки в хранилище запросов (которая принудительно использует предыдущие планы, если замечает значительное снижение производительности запросов по сравнению с предыдущим планом).

Официальная рекомендация, которую я даю клиентам, состоит в том, чтобы не беспокоиться о перестроении индекса, если только у них нет приложения уровня 1, в котором они продемонстрировали реальную потребность (выгоды перевешивают затраты) или если они являются независимыми поставщиками ПО SaaS и пытаются настроить рабочую нагрузку. над многими базами данных/клиентами в эластичных пулах или в многопользовательской структуре базы данных, чтобы они могли снизить себестоимость производства или избежать нехватки места на диске (как упоминалось ранее) в очень большой базе данных. У крупнейших клиентов, которые есть у нас на платформе, мы иногда видим ценность в выполнении операций с индексами вручную с клиентом, но нам часто не нужно иметь постоянную работу, когда мы выполняем такие операции просто в кейс. Цель команды разработчиков SQL заключается в том, что вам вообще не нужно беспокоиться об этом, и вместо этого вы можете просто сосредоточиться на своем приложении. Конечно, всегда есть вещи, которые мы можем добавить или улучшить в наших автоматических механизмах, поэтому я полностью допускаю возможность того, что у отдельной клиентской базы данных может возникнуть потребность в таких действиях. Я сам не видел ничего, кроме случаев, которые я упомянул, и даже они редко являются проблемой.

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

Удачи - каким бы ни был ваш результат, я надеюсь, что это поможет вам сделать правильный выбор.

С уважением, Конор Каннингем Архитектор, SQL

Вы можете использовать автоматизацию Azure для планирования задач обслуживания индекса, как описано здесь:Перестроение индексов базы данных SQL с помощью службы автоматизации Azure

Ниже приведены шаги:

1) Создайте учетную запись службы автоматизации, если у вас ее нет, на странице https://portal.azure.com и выберите «Создать» > «Управление» > «Учетная запись автоматизации».

2) После создания учетной записи автоматизации откройте сведения и нажмите «Runbooks» > «Просмотреть галерею».

Введите в поле поиска слово «индексы», и появится runbook «Индексирует таблицы в базе данных Azure, если они имеют высокую фрагментацию»:

4) Обратите внимание, что автором модуля Runbook является группа разработчиков SC Automation в Microsoft. Нажмите «Импорт»:

5) После импорта модуля Runbook теперь давайте добавим учетные данные базы данных в активы. Нажмите «Активы» > «Учетные данные», а затем нажмите кнопку «Добавить учетные данные…».

6) Задайте имя учетных данных (которое будет использоваться позже в модуле Runbook), имя пользователя и пароль базы данных:

7) Теперь снова щелкните Runbooks, затем выберите «Update-SQLIndexRunbook» из списка и нажмите кнопку «Изменить…». Вы сможете увидеть сценарий PowerShell, который будет выполнен:

8) Если вы хотите протестировать скрипт, просто нажмите кнопку «Тестовая панель», и откроется тестовое окно. Введите необходимые параметры и нажмите «Пуск», чтобы выполнить перестроение индекса. Если возникает какая-либо ошибка, она регистрируется в окне результатов. Обратите внимание, что в зависимости от базы данных и других параметров это может занять много времени:

9) Теперь вернитесь в редактор и нажмите кнопку «Опубликовать», чтобы включить модуль Runbook. Если мы нажмем «Пуск», появится окно с запросом параметров. Но поскольку мы хотим запланировать эту задачу, вместо этого мы нажмем кнопку «Запланировать»:

10) Щелкните ссылку «Расписание», чтобы создать новое расписание для модуля Runbook. Я указал один раз в неделю, но это будет зависеть от вашей рабочей нагрузки и степени фрагментации ваших индексов с течением времени. Вам нужно будет настроить расписание в зависимости от ваших потребностей и выполнения начальных запросов между выполнениями:

11) Теперь вводим параметры и запускаем настройки:

ПРИМЕЧАНИЕ: вы можете играть с разными расписаниями с разными настройками, т. е. с определенным расписанием для определенного стола.

На этом вы закончили. Не забудьте изменить настройки ведения журнала по своему усмотрению:

Автоматизация Azure хороша, и цены также незначительны.

Некоторые другие варианты, которые у вас есть,

1. Создайте задачу выполнения sql и запланируйте ее через агент sql. Задача выполнения sql должна содержать код перестроения индекса вместе с перестроением статистики.

2. Вы также можете создать связанный сервер с SQLAZURE и создать задание агента sql. Чтобы создать связанный сервер с лазурью, вы можете увидеть эту ссылку SO: Мне нужно добавить связанный сервер в MS Azure SQL Server

Как предложил @TheGamiswar, добавьте связанный сервер, а затем создайте хранимую процедуру следующим образом:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [LinkedServerName].[RemoteDB].[dbo].[sp_RebuildReorganizIndexes]   
AS  
BEGIN  
    ALTER INDEX PK_MyTable ON MyTable REBUILD WITH (STATISTICS_NORECOMPUTE = ON, ONLINE=ON);
    ALTER INDEX IX_MyTable ON MyTable REBUILD WITH (STATISTICS_NORECOMPUTE = ON, ONLINE=ON); --Nonclustered index

    ALTER INDEX PK_MyTable ON MyTable REORGANIZE;
    ALTER INDEX IX_MyTable ON MyTable REORGANIZE;
END

Затем на связанном сервере используйте «Агент SQL Server», чтобы создать новое задание и расписание:

Для получения дополнительной информации см. https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-2017

Вы можете рассмотреть возможность использования агента Azure Elastic Job для автоматизации обслуживания базы данных SQL Azure в нескольких базах данных в одном задании.

Подробнее об этом читайте на странице https://azureops.org/articles/automating-azure-sql-database-maintenance-tasks/

Помимо использования службы автоматизации Azure для автоматизации задания перестроения индексов в базе данных SQL Azure, вы также можете рассмотреть функцию Автоматическое управление индексами в базе данных SQL Azure, как описано в этом блог SQL Server Database Engine.

Вы можете сделать это, перейдя на портал Azure, перейдите в базу данных Azure SQL и выберите Автоматическая настройка. Если вы хотите, чтобы это было включено для всех ваших баз данных в определенном Azure SQL Server, вы также можете включить то же самое на Azure SQL Server.