Оптимизация хранилища базы данных
Для оптимизации хранилища базы данных следует использовать пропорциональное заполнение и настройку базы данных tempdb.
Что такое пропорциональная заполнение?
Если вы добавляете 1 ГБ данных в базу SQL Server с двумя файлами данных, можно ожидать, что размер каждого из этих файлов увеличится примерно на 512 МБ. Однако такое равномерное увеличение размера происходит далеко не во всех случаях, поскольку SQL Server будет добавлять данные в файлы на разных томах в зависимости от размера файла данных. Если в приведенном выше примере размер каждого из файлов данных равен 2 ГБ, можно предполагать, что распределение данных будет осуществляться равномерно. Тем не менее, если один из этих файлов имеет размер 10 ГБ, а другой — 1 ГБ, примерно 900 МБ данных будет добавлено в первый файл и лишь 100 МБ во второй. Такое поведение свойственно любой базе данных, однако из-за интенсивности операций записи в базе tempdb неравномерное распределение операций записи может привести к возникновению узкого места при работе с большим файлом, поскольку в него будет записываться гораздо больше данных.
Настройка базы данных tempdb в SQL Server
В SQL Server 2016 это поведение было изменено за счет определения доступного на момент установки количества ЦП и настройки соответствующего количества файлов данных (до 8) одинакового размера. Кроме того, в ядро СУБД было встроено поведение флагов трассировки 1117 и 1118, однако сделано это было только для базы данных tempdb. Для интенсивных рабочих нагрузок базы данных tempdb количество файлов tempdb можно увеличить более чем до 8 в соответствии с количеством ЦП на вашей машине.
В SQL Server база данных tempdb используется далеко не только для хранения пользовательских временных таблиц. В ней, помимо прочего, также находятся рабочие таблицы, которые используются для размещения промежуточных результатов запроса, операций сортировки, хранилища версий для управления версиями строк и многие другие данные. В связи с этим важно размещать базу данных tempdb в хранилище с минимально возможной задержкой и надлежащим образом настроить ее файлы данных.
В версиях, предшествующих SQL Server 2016, в базе tempdb по умолчанию использовался только один файл данных. В связи с этим было возможно возникновение состязания между несколькими процессами, пытающимися получить доступ к системным страницам в базе данных tempdb. Одно из распространенных решений этой проблемы предусматривало включение флага трассировки 1118, который изменяет способ выделения сегментов. Также во многих случаях рекомендовалось создавать несколько файлов данных в базе tempdb. Поскольку SQL Server использует алгоритм пропорционального заполнения для баз с несколькими файлами данных, также важно контролировать, что эти файлы имеют одинаковый размер и растут с одинаковой скоростью. Для этого многие администраторы баз данных используют флаг трассировки 1117, который принудительно задает одинаковую скорость роста файлов данных в базах, содержащих несколько таких файлов.