Оптимизация хранилища базы данных

  • 5 мин

Для оптимизации хранилища базы данных следует использовать пропорциональное заполнение и настройку базы данных tempdb.

Общие сведения о производительности ввода-вывода

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

Число операций ввода-вывода в секунду для приложения может быть важным. Убедитесь, что вы выбрали правильный уровень служб и виртуальные ядра для ваших потребностей в операций ввода-вывода в секунду. Узнайте, как измерять количество операций ввода-вывода в секунду для локальных запросов при миграции в Azure. Если у вас есть ограничения на число операций ввода-вывода в секунду, вы можете столкнуться с длительными ожиданиями. В модели приобретения виртуальных ядер можно увеличить масштаб виртуальных ядер или перейти к критически важный для бизнеса или гипермасштабированию, если у вас недостаточно операций ввода-вывода в секунду. Для рабочих нагрузок при использовании DTU рекомендуется перейти на уровень "Премиум".

Задержка ввода-вывода — это еще один ключевой компонент производительности ввода-вывода. Для сокращения задержки операций ввода-вывода для базы данных SQL Azure рассмотрите переход на уровни "Критически важный для бизнеса" или "Гипермасштабирование". Для сокращения задержки ввода-вывода в Управляемом экземпляре SQL перейдите на уровень "Критически важный для бизнеса" или увеличьте размер файла или число файлов для базы данных. Для улучшения задержки в журнале транзакций может потребоваться использовать транзакции с несколькими статистиками.

Файлы и файловые группы

Специалисты по SQL Server часто используют файлы и файловые группы для повышения производительности операций ввода-вывода за счет физического размещения файлов. SQL Azure не разрешает пользователям размещать файлы в конкретных дисковых системах. Однако Azure SQL имеет ресурсные обязательства по производительности операций ввода-вывода в отношении скоростей, IOPS и задержек. Таким образом, абстракция физического размещения файлов может быть преимуществом.

В базе данных SQL Azure имеется только один файл базы данных (на уровне "Гипермасштабирование" обычно используется несколько), и его максимальный размер настраивается через интерфейсы Azure. Нет функциональных возможностей для создания дополнительных файлов.

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

Опишите пропорциональное заполнение

При вставке 1 гигабайт данных в базу данных SQL Server с двумя файлами данных можно ожидать, что каждый файл увеличится примерно на 512 мегабайт. Однако это не всегда так. SQL Server распределяет данные на основе размера каждого файла. Например, если оба файла данных имеют 2 гигабайта, данные будут равномерно распределены. Но если один файл равен 10 гигабайтам, а другой — 1 гигабайт, около 900 МБ будут входить в более крупный файл и 100 МБ в меньший. Это поведение распространено в любой базе данных, но в tempdb с интенсивной записью неравномерный шаблон записи может создать узкое место в самом большом файле, так как он обрабатывает больше записей.

Настройка Tempdb в SQL Server

SQL Server обнаруживает количество доступных ЦП во время установки и настраивает соответствующее количество файлов, до восьми, с равными размерами. Кроме того, поведение флагов трассировки 1117 и 1118 интегрируются в ядро СУБД, но только для tempdb. Для нагрузок, ориентированных на tempdb, может оказаться полезным увеличить количество файлов tempdb до более чем восьми, чтобы количество файлов совпадало с количеством ЦП на вашей машине.

Вы используете одинаковое использование tempdb как для SQL Server, так и для SQL Azure. Обратите внимание, что возможность настройки tempdb отличается, включая размещение файлов, количество и размер файлов, а tempdb также параметры конфигурации.

SQL Server использует tempdb для различных задач, кроме хранения пользовательских временных таблиц. Он используется для рабочих таблиц, включающих промежуточные результаты запроса, операции сортировки и хранилище версий для управления версиями строк, среди прочего. Из-за этого обширного использования важно разместить tempdb в хранилище с наименьшей задержкой и правильно настроить файлы данных.

Файлы базы данных всегда хранятся автоматически на локальных дисках tempdb SSD, поэтому производительность ввода-вывода не должна быть проблемой.

Специалисты SQL Server часто используют несколько файлов базы данных для секционирования выделений таблиц tempdb . Для базы данных SQL Azure число файлов масштабируется с числом виртуальных ядер (например, два виртуальных ядра равны четыре файлам) с максимальным числом 16. Количество файлов не настраивается с помощью T-SQL tempdb, но его можно настроить, изменив параметр развертывания. Максимальный размер tempdb масштабируется на количество виртуальных ядер. Вы получаете 12 файлов с Управляемым экземпляром SQL независимо от виртуальных ядер.

Параметр базы данных MIXED_PAGE_ALLOCATION имеет значение OFF и AUTOGROW_ALL_FILES имеет значение ON. Вы не можете настроить это, но, как и в SQL Server, это рекомендуемые значения по умолчанию.

Функция tempdb оптимизации метаданных, представленная в SQL Server 2019, которая может облегчить тяжелые конфликты с блокировкой, в настоящее время недоступна в База данных SQL Azure или Управляемый экземпляр SQL Azure.

Конфигурация базы данных

Обычно вы настраиваете базу данных с помощью инструкций T-SQL ALTER DATABASE и ALTER DATABASE SCOPED CONFIGURATION. Многие варианты конфигураций, повышающих производительность, доступны для SQL Azure. Ознакомьтесь со справочником ALTER DATABASE и ALTER DATABASE SCOPED CONFIGURATION T-SQL для различий между SQL Server, База данных SQL Azure и Управляемый экземпляр SQL Azure.

В Базе данных SQL Azure модель восстановления по умолчанию является полным восстановлением, что гарантирует, что база данных может соответствовать соглашениям об уровне обслуживания Azure (соглашения об уровне обслуживания). Это означает, что минимальное ведение журнала для массовых операций не поддерживается, за исключением tempdbслучаев, когда допускается минимальное ведение журнала.

Конфигурация MAXDOP

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

И наоборот, установка MAXDOP на более низкое значение может снизить давление памяти, что позволяет системе хранения работать более эффективно. Это важно в средах с ограниченными ресурсами памяти или высокими требованиями к хранилищу. Тщательно настраивая MAXDOP, вы можете сбалансировать производительность запросов и эффективность хранилища, обеспечивая оптимальное использование ресурсов ЦП и хранилища.

Параметр MAXDOP в SQL Azure можно настроить так же, как и в SQL Server, то есть с помощью следующих методов:

  • ALTER DATABASE SCOPED CONFIGURATION MAXDOP настройка поддерживается для SQL Azure.
  • Хранимая процедура sp_configure для "максимальной степени параллелизма" поддерживается для управляемого экземпляра SQL.
  • MAXDOP Подсказки запросов полностью поддерживаются.
  • Настройка MAXDOP с помощью регулятора ресурсов поддерживается для управляемого экземпляра SQL Server.