Анализ, миграция и подготовка отчетов с помощью AMR

Обеспечиваем рост производительности при выполнении транзакций в памяти

Новый модуль In-Memory OLTP engine позволяет загружать в память таблицы и хранимые процедуры, что резко сокращает время отклика. При этом цель состоит не в том, чтобы разместить в памяти все таблицы базы данных и все хранимые процедуры, а в том, чтобы выбрать таблицы, обработка которых заметно влияет на быстродействие системы, а также хранимые процедуры, предполагающие сложные логические расчеты.

. Встроенная в среду SQL Server Management Studio (SSMS) функция AMR включает в себя следующие компоненты.

• Сборщик данных производительности (который собирает сведения о существующих таблицах и хранимых процедурах для анализа рабочих нагрузок) и отчеты с анализом скорости выполнения транзакций (которые на основе собранных данных формулируют рекомендации по выгрузке в систему In-Memory OLTP определенных таблиц и хранимых процедур).
• Помощник по оптимизации памяти Memory Optimization Advisor, который помогает пользователю в осуществлении процесса преобразования существующей таблицы в таблицу с оптимизированным использованием памяти.
• Помощник по компиляции в собственном коде Native Compilation Advisor (помогает пользователю выявить те элементы T-SQL, которые необходимо модифицировать до преобразования хранимой процедуры в хранимую процедуру, скомпилированную в собственном коде).

Хранение собранных сведений осуществляется с использованием новых наборов данных Transaction Performance Collection Sets, предназначенных для сбора информации о рабочих нагрузках и реляционной базе данных хранилища управляющих данных Management Data Warehouse. Наборы Transaction Performance Collection Sets включают в себя:

• набор данных об использовании хранимых процедур Stored Procedure Usage Analysis collection set (в который вводятся сведения о хранимых процедурах для преобразования последних в компилированные в собственном коде хранимые процедуры);
• набор данных об использовании таблиц Table Usage Analysis collection set (в который вводятся сведения о записанных на диски таблицах для преобразования последних в таблицы с оптимизированным использованием памяти).

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

Настройка хранилища управляющих данных

Чтобы настроить хранилище управляющих данных, откройте окно обозревателя объектов Object Explorer в среде SSMS. Откройте папку Management, правой кнопкой мыши щелкните на пункте Data Collection, а затем выберите пункты Tasks и Configure Management Data Warehouse. Таким образом вы запустите мастер настройки базы данных Configure Management Data Warehouse Wizard.

Пройдя через страницу приветствия, вы окажетесь на странице Select Configuration Task. Здесь нужно выбрать задание «Настроить базу данных хранилища управляющих данных».

На странице Configure Management Data Warehouse Storage следует указать имя базы данных, где будет размещено хранилище управляющих данных, а также имя сервера, на котором установлена упомянутая база данных. Если вам нужно создать эту базу данных, нажмите кнопку New.

На странице Map Logins and Users вы обнаружите учетные записи, используемые для подключения к серверу, на котором будет размещаться хранилище управляющих данных. При необходимости вы сможете модифицировать эти имена или назначить пользователей для ролей администратора, читателя и регистратора хранилища управляющих данных.

На странице Complete the Wizard убедитесь в корректности настройки хранилища управляющих данных. Если все параметры указаны верно, нажмите кнопку Finish. В случае успешного завершения процесса настройки хранилища управляющих данных вы увидите страницу, подобную той, что отображена на экране 1. Установка хранилища управляющих данных завершена.

Экран 1. Проверка корректности конфигурации хранилища управляющих данных

Настройка процесса сбора данных

Для настройки процесса сбора данных в среде SSMS откройте окно Object Explorer. Откройте папку Management, правой кнопкой мыши щелкните на пункте Data Collection, а затем выберите пункты Tasks и Configure Data Collection. Таким образом вы запустите мастер настройки сбора данных Configure Data Collection Wizard.

Вслед за страницей приветствия вы увидите страницу Setup Data Collection Sets, показанную на экране 2. Здесь потребуется указать не только имя сервера и базы данных, где будет размещаться хранилище управляющих данных, но и наборы сборщиков данных. В списке наборов элементов сбора выставьте флажок Transaction Performance Collection Sets, чтобы сборщик данных приступил к сбору статистических сведений, касающихся производительности транзакций.

Экран 2. Определение наборов сборщиков данных

Если хранилище управляющих данных размещается не на том экземпляре SQL Server, где установлен сборщик данных, и если агент SQL Server не выполняется с использованием доменной учетной записи, предоставляющей разрешения dc_admin на удаленном экземпляре, вам придется задействовать SQL Server Agent в качестве посредника. Если дела у вас обстоят именно так, обязательно выставьте флажок Use a SQL Server Agent proxy for remote uploads.

По завершении процедуры настройки на странице Setup Data Collection Sets нажмите кнопку Finish. Когда мастер завершит процедуру настройки, вам останется только запустить процесс сбора информации, охватывающий все пользовательские базы данных. Отметим кстати, что агент SQL Server необходимо выполнять на том экземпляре, который будет осуществлять сбор данных.

В папке SQL Server Agent's Jobs вы найдете имена заданий, осуществляющих сбор данных с рабочих нагрузок, а также заданий, выполняющих выгрузку собранной информации в хранилище управляющих данных. В заданиях по сбору данных используется соглашение об именовании collection_set_N_collection, где N — это число. В заданиях по выгрузке используется соглашение об именовании collection_set_N_upload, где N — это число.

По умолчанию средство AMR осуществляет сбор данных с помощью трех динамических административных представлений раз в 15 минут как для набора элементов сбора Stored Procedure Usage Analysis, так и для набора Table Usage Analysis. Задание по выгрузке выполняется раз в 30 минут для набора Stored Procedure Usage Analysis и раз в 15 минут — для набора Table Usage Analysis. Если вам необходимо ускорить выгрузку, можете выполнять эти задания вручную. Процесс выгрузки данных на быстродействии системы практически не сказывается.

Выполнение отчетов с анализом характеристик транзакций

Чтобы познакомиться с рекомендациями, построенными на сведениях, которые были собраны по всем вашим пользовательским базам данных на сервере рабочих нагрузок, нужно запустить отчеты с анализом характеристик транзакций. Правой кнопкой мыши щелкните на базе данных Management Data Warehouse, в раскрывшемся меню выберите пункты Reports, затем Management Data Warehouse и щелкните на Transaction Performance Analysis. На странице Transaction Performance Analysis Overview вы можете запустить на выполнение три отчета — в зависимости от того, какой тип информации требуется:

• Recommended Tables Based on Usage (таблицы, рекомендуемые по критерию частоты использования);
• Recommended Tables Based on Contention (таблицы, рекомендуемые по критерию соперничества за доступ);
• Recommended Stored Procedures Based on Usage (хранимые процедуры, рекомендуемые по критерию частоты использования)

Таблицы, рекомендуемые по критерию частоты использования. В этом отчете перечисляются таблицы, которые являются лучшими кандидатами для миграции в систему In-Memory OLTP исходя из частоты их использования. На экране 3 представлен образец такого отчета. В левой части экрана вы можете выбрать базу данных и указать число таблиц из нее, подлежащих миграции. После этого на диаграмме будут показаны отобранные таблицы. По горизонтальной оси откладывается объем работ, необходимых для переноса таблицы в систему In-Memory OLTP. По вертикальной оси откладывается выигрыш, получаемый вследствие миграции этой таблицы. Наиболее перспективные кандидаты для перемещения в In-Memory OLTP размещаются в правом верхнем углу диаграммы. Как вы можете убедиться, их миграция не требует особых затрат и дает наибольший выигрыш в производительности.

Экран 3. Выявление таблиц, наиболее подходящих для миграции по критерию частоты использования

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

Экран 4. Просмотр подробных статистических данных по?быстродействию обработки таблицы

Таблицы, рекомендуемые по критерию соперничества за доступ. Этот отчет показывает, какие таблицы являются наиболее перспективными кандидатами для перемещения в систему In-Memory OLTP по критерию конфликтов за доступ. Сравнение отчета, представленного на экране 3, с отчетом на экране 5, где анализируются сведения по частоте использования, показывает, что они во многом сходны.

Экран 5. Определение таблиц, наиболее подходящих для перемещения в соответствии с критерием соперничества за доступ

Вы можете выбрать базу данных и количество таблиц из нее, предлагаемое для миграции. На экране появится диаграмма, демонстрирующая объем работ, необходимых для перемещения таблиц (откладывается по горизонтальной оси), и получаемый в результате выигрыш (откладывается по горизонтальной оси). В правом верхнем углу экрана будут отображены наиболее перспективные кандидаты для перемещения по критерию соперничества за доступ. Чтобы ознакомиться с подробным отчетом, содержащим статистические данные по той или иной таблице, вы можете щелкнуть на ее имени в диаграмме. В этом отчете будут содержаться статистические данные по обращениям к таблице и по конфликтам.

Хранимые процедуры, рекомендуемые по критерию частоты использования. В этом отчете выделены хранимые процедуры, которые являются наиболее перспективными кандидатами для переноса в систему In-Memory OLTP в соответствии с критерием частоты использования (то есть с учетом суммарного процессорного времени). После того как вы выберете интересующую вас базу данных и укажете число хранимых процедур из этой базы, предлагаемых для миграции, на экране появится диаграмма с отображением наиболее перспективных кандидатов для миграции, как показано на экране 6.

Экран 6. Выявление хранимых процедур, подходящих для миграции по критерию частоты использования

Если вам нужно будет просмотреть подробные статистические данные по использованию той или иной хранимой процедуры, щелкните на ее голубой панели. Вы получите отчет, подобный тому, что представлен на экране 7.

Экран 7. Просмотр подробных статистических данных об?использовании хранимой процедуры

Работа с помощником по оптимизации

Определив, какие таблицы будут введены в систему In-Memory OLTP, вы сможете приступить к организации процесса миграции с помощью реализованного в средстве AMR помощника по оптимизации памяти. Чтобы активировать его, откройте в среде SSMS окно программы Object Explorer и перейдите к таблице, которую хотите переместить. Щелкните на ней правой кнопкой мыши и в открывшемся меню выберите пункт Memory Optimization Advisor.

После запуска на экране появится вводная страница; ее можно прочесть или оставить без внимания. Нажав кнопку Next, вы попадете на страницу Migration Optimization Checklist. Здесь помощник проверит, возможно ли осуществление миграции указанной вами таблицы. Если проверочный тест даст отрицательный результат по одному или нескольким пунктам, процесс миграции будет остановлен. В случае необходимости вы можете получить отчет по выполненному анализу. Если же против каждого пункта проверки будет поставлена зеленая «галочка», это значит, что таблица готова к началу процесса миграции; в таком случае вы можете переходить на следующую страницу.

На странице Migration Optimization Warnings вы найдете важную информацию о том, какие возможности недоступны для таблиц с оптимизированным использованием памяти, а также о других ограничениях. Последние не могут помешать процессу миграции, однако они могут стать причиной выхода из строя или непредсказуемого поведения других объектов.

Если какое-либо предупреждение касается таблицы, выбранной вами для перемещения, рядом с этим предупреждением будет отображен восклицательный знак в желтом треугольнике, как показано на экране 8.

Экран 8. Просмотр предупреждений Migration Optimization Warnings

В данном случае в индексированном столбце Person_OnDisk_Name заданной таблицы указан неподдерживаемый параметр сортировки French_CI_AS. Из всех параметров сортировки для индексов таблиц с оптимизированным использованием памяти допустимы лишь параметры BIN2. Таким образом, указанный индексный параметр должен быть изменен позднее в ходе миграции.

На странице Review Optimization Options, показанной на экране 9, вы можете изменить следующие настройки, применяемые по умолчанию:

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

Экран 9. Просмотр вариантов оптимизации

Кроме того, вы можете скопировать данные из исходной таблицы в новую таблицу с оптимизированным использованием памяти в ходе процесса миграции, а также изменить срок службы таблицы с оптимизированным расходованием памяти. По умолчанию ее параметр DURABILITY получает значение schema_and_data, но вы можете заменить его на schema_only. Для этого нужно задействовать настройку Check this box to migrate this table to a memory-optimized table with no data durability. Если вы примените указанную настройку, данные после перезапуска службы SQL Server будут потеряны. Иными словами, сохраняется только схема таблицы.

Наконец, на странице Review Optimization Options отображается предполагаемый объем памяти, необходимый для обработки таблицы с оптимизированным использованием памяти. Если имеющейся памяти недостаточно, процесс миграции завершится ошибкой.

Завершив выполнение необходимых операций на странице Review Optimization Options, вы можете, нажав кнопку Next, перейти на страницу Review Primary Key Conversion. Напомню, что на первом этапе процесса миграции осуществляется преобразование первичного ключа. Его можно преобразовать в:

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

По каждому индексу, содержащемуся в перемещаемой таблице, вам будет представлена страница Review Index Conversion, заполненная столбцами и типами данных для этого индекса. Настройки, которые вы можете модифицировать на странице Review Index Conversion, аналогичны настройкам, указанным на странице Review Primary Key Conversion. В данном случае вместо недопустимого параметра сортировки French_CI_AS для индексированного столбца Person_OnDisk_Name вам придется использовать параметр сортировки BIN2 в качестве типа данных Char.

На странице Verify Migration Actions перечислены все операции, которые будут выполняться в процессе переноса вашей таблицы в систему In-Memory OLTP. Вы можете получить сценарии этих операций, нажав кнопку Script. Завершив проверку всех параметров, вы можете запустить процесс миграции нажатием кнопки Migrate.

На экране 10 показано, как новая таблица с оптимизированным использованием памяти выглядит в окне SSMS. Если вы просмотрите ее свойства, то убедитесь, что для этой таблицы свойство Memory optimized имеет значение True, а схема и данные предназначены для длительного хранения.

Экран 10. Новая таблица с оптимизированным использованием памяти в окне SSMS

На экране 10 вы также можете увидеть, каким образом была переименована исходная таблица.

Работа со встроенным помощником по компиляции

Определившись с тем, какие хранимые процедуры вы планируете перенести в систему In-Memory OLTP, вы можете воспользоваться встроенным в AMR помощником по компиляции для организации их перемещения. Чтобы активировать помощника, в среде SSMS откройте окно Object Explorer и перейдите к хранимой процедуре, которую хотите переместить. Правой кнопкой мыши щелкните на данной хранимой процедуре и в открывшемся меню выберите пункт Native Compilation Advisor.

Выполнив необходимые действия на странице приветствия, вы перейдете на страницу Stored Procedure Validation. Если ваша хранимая процедура содержит элементы T-SQL, не поддерживаемые в процессе компиляции в собственном коде, на этой странице будут отображены соответствующие предупреждения. Если хранимая процедура не содержит недопустимых элементов, она может стать скомпилированной в собственном коде хранимой процедурой без каких-либо модификаций. Однако помощник по компиляции в собственном коде не перемещает хранимые процедуры, как перемещает таблицы помощник по оптимизации памяти. Процесс миграции нужно будет осуществлять самостоятельно.

Если хранимая процедура содержит недопустимые элементы T-SQL, процесс проверки завершится ошибкой. Чтобы ознакомиться с подробным описанием недопустимых элементов, нажав кнопку Next, перейдите на страницу Stored Procedure Validation Result, которая показана на экране 11.

Экран 11. Просмотр недопустимых элементов T-SQL

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

Не гадать на кофейной гуще

Средство AMR полезно потому, что позволяет нам строить обоснованные предположения относительно того, какие таблицы и хранимые процедуры могут обеспечить выигрыш в быстродействии в случае их перевода в систему In-Memory OLTP. Определившись с тем, какие таблицы подлежат преобразованию, мы можем быстро осуществить процесс миграции благодаря помощнику по оптимизации памяти. Что же касается помощника по компиляции в собственном коде, то хотя он может помочь вам в определении тех элементов языка T-SQL, которые необходимо изменить перед преобразованием хранимой процедуры в процедуру, скомпилированную в собственном коде, он, к сожалению, не участвует в осуществлении процесса миграции.