company_banner

Эффективное управление индексами в Azure SQL Database с помощью Index Advisor

    Мы рады поделиться с вами очередной статьей из серии статей о облачных сервисах Microsoft Azure. В этот раз Андрей Антюфеев — руководитель программ Microsoft из команды SQL Server и Azure SQL Database, продолжит свой рассказ о работе с индексами в облачной БД Azure SQL Database. — Владимир Юнев
    Всем привет, эта заметка будет полезна всем, кто пользуется Azure SQL Database.


    В прошлой раз, мы обозревали первую версию Index Advisor. C тех пор помощник успел дорасти до GA, повысив стабильность, и обзавестись новым возможностями:

    • возможность автоматически применять рекомендации помощника
    • новые рекомендации (Drop Index)
    • визуализация нагрузки после создания индекса
    • другие улучшения

    Забудьте об управлении индексами


    Процесс создания новых индексов происходит в несколько этапов:

    1. Index Advisor предоставляет пользователю рекомендации индексов, которые дадут прирост производительности;
    2. Пользователь выбирает, какую рекомендацию применить, рекомендация переходит в состояние “Pending”;
    3. В это время Index Advisor делает контрольный замер вашей нагрузки и создаёт индекс;
    4. На последний стадии IA снова замеряет производительность и, если она ниже, чем до операции с индексами, операция отменяется. Индекс в статусе “Reverted”.


    Рис. 1. — Свойства индекса, эффект которого был негативный. Операцию можно повторить, нажав кнопку “Revert”.

    Через какое-то время нагрузка на базу данных может измениться, и понадобится новый индекс или старый станет ненужным. Пользователю придётся запускать этот процесс снова и снова.

    Автопилот


    Для тех, кто устал от необходимости думать об индексах, мы создали функцию автопилота – он автоматически применяет новые рекомендации и откатывает изменения если эффект оказался отрицательным.


    Рис. 2. — Включить автопилот можно в настройках Index Advisor


    Рис. 3. — В свойствах оконченных операций будет показано, кто её инициировал: пользователь или автопилот.

    «Я тебя породил, я тебя и убью»


    Index Advisor постоянно наблюдает за телеметрией и нагрузкой базы данных — если он обнаруживает индекс, который больше не приносит никакой пользы либо дублируется каким-то другим индексом, то он предложит его удалить.


    Например, в стадии внутреннего тестирования мы обнаружили базу данных с 72мя одинаковыми индексами, которые просто занимали место. Это произошло из-за сторонней программы по администрации индексов, которая сработала не так, как следовало.

    Для удаления индексов ещё важнее становится отличительная черта Index Advisor: верификация после. Благодаря использованию Query Store, Index Advisor знает, каким будет эффект создания или удаления индекса. Если он окажется негативным, то помощник автоматически вернёт всё как было.

    Дополнительные уведомления


    Каждый день мы публикуем тысячи рекомендаций. Если они все будут применены, производительность SQL Azure в целом и для клиентов ощутимо возрастёт. Поэтому мы будем стараться доносить эти рекомендации до пользователей как можно быстрее.

    Первым шагом является Status bar на экране базы данных:


    Если у вас есть активные рекомендации, то во время посещения нового портала Azure вы увидите уведомление, приглашающее применить их. В скоро будущем мы планируем делать регулярные уведомления о наличии рекомендаций в Index Advisor.

    Это не обман! Это работает


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

    Для этого нужно выбрать операцию, которая закончилась успешно, и запустить Query Insights:


    Как видите, индекс был создан 6-го числа, и общее потребление DTU упало с 80 до 40%, а также потребление CPU запросами упало. (Не всегда улучшение настолько значимое, но оно всегда есть)

    Совпадение? Не думаю. Query Performance Insight — инструмент полезный сам по себе, и о нём я построюсь рассказать в следующей заметке.

    Почему у меня нет никаких рекомендаций?


    Так может получиться, что ваша база данных не нуждается в дополнительных индексах в данный момент. Тогда Index Advisor покажет сообщение, объясняющее отсутствие рекомендаций. Причины могут быть следующими:

    1. Мы проанализировали вашу базу данных, и нам нечего добавить или убрать (вы отличный разработчик!);
    2. У нас недостаточно данных для надежных рекомендаций. Это происходит по нескольким причинам:
    3. Мы не можем внести изменения в структуру базы данных (например, если она отмечена как Read-only);
    4. В данный момент запущены другие операции по изменению индексов для вашей базы (новые рекомендации могут появиться после завершения всех запланированных изменений);
    5. У Index Advisor выходной (и такое бывает).


    Список со временем может поменяться, но мы постараемся всегда приводить конкретную причину.

    Другое


    Небольшие изменения как:

    • Подсветка синтаксиса в поле для скрипта по ручному выполнению рекомендации (Index Details -> “View Script”);
    • Если применение рекомендации завершилось ошибкой, причина будет указана в свойствах рекомендации.

    Новая модель рекомендаций


    Как вы могли уже догадаться, администрирование индексов — это только первый шаг. Со временем мы надеемся расширить наш Advisor новыми советами и рекомендациями.

    Сначала они будут запущены в виде предпоказа и отмечены звездочкой, например, Drop Index.

    Итог


    Index Advisor теперь в стадии GA, он поможет вам найти недостающие индексы (а так же предложит удалить ненужные) и улучшить производительность вашей базы данных.

    Включив автопилот Index Advisor, вы сможете оставить забыть об управлении индексами, облако сделает всё за вас.

    Обратная связь


    • Было бы очень круто услышать мнения/комментарии матёрых DBA и новых Code-first разработчиков. Вы можете начать дискуссию здесь.
    • На странице Index Advisor есть кнопка Feedback, где вы может высказать мнение по конкретному индексу.
    • Хотите передать привет командам SQL Server/SQL Azure: anant@microsoft.com постараюсь найти нужный адресат.

    Во второй части мы рассмотрим Query Performance Insight – инструмент, который показывает TOP-10 самых прожорливых запросов в вашей базе данных.

    Об авторе


    Андрей Антюфеев



    Руководитель программ SQL Server & Azure SQL Database, Microsoft
    sitox
    Microsoft
    Microsoft — мировой лидер в области ПО и ИТ-услуг

    Comments 6

      0
      Я очень жду автоматизацию REBUILD INDEX «из коробки». На SQL Server с sql agent все реализуется в два клика. А тут нужно (если не так, поправьте, пожалуйста — начал пользоваться Azure не так давно) писать программу на C#, дергающую SQL-ный скрипт ребилда/дефрагментации и создавать под нее WebJob? Как-то сложно для такой базовой операции.

      И вот интересно, если у меня жуткая фрагментация индекса, Advisor поймет, что в ней дело?
        0
        Мы рассматриваем такую возможность. Несколько тестов показали, что прирост производительность есть, но не всегда.(Увас есть какие-то пример когда это сильно улучшило ситуацию?)
        Если мы решим добавить Rebuild как ещё одну рекомендацию внутри помощника, то её можно будет включить автоматически.

        Есть неплохая статья по автоматизации здесь но пока да, выполнение скрипта.
          0
          Спасибо! Не знал про Azure Automation. Если честно, там все очень неинтуитивно, но лучше чем наша текущая версия с вебджобом (так как можно в онлайне редактировать скрипт). Надо учить Powershell )

          Пример, да, вот сейчас на работе несколько баз ~50ГБ, у которых индексы на основных таблицах за день фрагментируются до больше 90%. По утрам стоит скрипт с ребилдом и дефрагментацией. Без него запросы в админках с отчетами уходят по времени выполнения в плюс бесконечность.
        0
        Можно вопрос… Ваш продукт это Database Engine Tuning Advisor, но только для реалий ажура? Или же работает он по другому принципу?

        Определяется медленный запрос в кеше планов:

        SELECT SalesOrderID, OrderDate, [Status], TerritoryID
        FROM Sales.SalesOrderHeader
        WHERE OrderDate = '20050701'
        

        Потом в его плане смотрим на подсказку оптимизатора:

        <MissingIndexes>
            <MissingIndexGroup Impact="99.3093">
            <MissingIndex Database="[AdventureWorks2012]" Schema="[Sales]" Table="[SalesOrderHeader]">
                <ColumnGroup Usage="EQUALITY">
                <Column Name="[OrderDate]" ColumnId="3" />
                </ColumnGroup>
            </MissingIndex>
            </MissingIndexGroup>
        </MissingIndexes>
        

        Создается гипотетический индекс:

        CREATE NONCLUSTERED INDEX IX_OrderDate_H
        	ON Sales.SalesOrderHeader(OrderDate)
        	WITH (STATISTICS_ONLY = 1)
        

        И командой AUTOPILOT проверяется как новый индекс себя ведет:

        DBCC AUTOPILOT(0, 11, 1266103551, 13)
        GO
        SET AUTOPILOT ON
        GO
        SELECT SalesOrderID, OrderDate, [Status], TerritoryID
        FROM Sales.SalesOrderHeader
        WHERE OrderDate = '20050701'
        GO
        SET AUTOPILOT OFF
        GO
        

        и так методом перебора пока не найдется самый подходящий индекс…

        Спасибо за статью.
          +1
          А у кого есть реальный опыт работы с Azure SQL? Плюсы-минусы, реальные затраты на эксплуатацию, продуктив или только разработка? Поделитесь!

        Only users with full accounts can post comments. Log in, please.