Решения для базы данных tempdb в SQL Server

Tempdb — критически важная системная база данных в Microsoft SQL Server. Ее уникальная особенность состоит в том, что это общий ресурс, к которому обращаются все пользователи экземпляра SQL Server для размещения временных объектов пользователя, таких как таблицы, переменные таблицы, временные индексы, курсоры и результаты функции с табличным значением. Кроме того, она используется для размещения внутренних системных объектов для рабочих таблиц, хешей, сортировки, временного хранилища больших объектов, операций объединения и хеш-соединения, а также других нужд системы. Интенсивность использования базы данных tempdb в SQL Server (https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-2017) может быть крайне низкой или постоянно высокой. Это зависит от многих факторов, в частности от размера базы данных, качества и эффективности программного кода приложения и базы данных, структуры таблиц, использования временных таблиц или табличных переменных, применения курсоров и функций с табличным значением и от нагрузки. Как бы то ни было, ответственные за построение и администрирование среды SQL Server сотрудники должны постараться сделать tempdb как можно более производительной, учитывая особенности разработки, факторы масштабирования, бюджета и предполагаемого использования. Ключевое условие при этом — обеспечить приемлемые характеристики хранилища.

Во многих случаях администраторы баз данных, обязанные поддерживать оптимальный уровень производительности SQL Server,не те специалисты, которые подготовили серверы. При благоприятном раскладе у них будет достаточно влияния, чтобы предложить рекомендации относительно уровней загрузки процессора, оперативной памяти и хранилища данных. Возможно, к их мнению прислушаются даже при выборе оборудования. Однако, по мере того как все больше рабочих нагрузок перемещается на «облачные» платформы, такие как Microsoft Azure и Amazon AWS, большинство ключевых решений инфраструктуры уходит из компетенции администратора баз данных. Эта статья должна послужить напоминанием о том, что при принятии решения о переходе в «облако» нельзя полностью игнорировать инфраструктурные соображения. В компаниях по-прежнему остаются вопросы по инфраструктуре, критически важные даже при работе в «облачной» среде, где варианты ограничены.

Рекомендации по настройке tempdb

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

Рекомендации по количеству файлов tempdb

Решение относительно количества файлов данных, подготавливаемых для базы данных tempdb в SQL Server, зависит от числа логических ядер процессора. Для экземпляров SQL Server, работающих менее чем с восемью логическими ядрами (если такие еще существуют), должно быть соотношение 1:1 между числом логических ядер и файлами данных, размещенных в tempdb. Когда имеется восемь или более логических ядер, поначалу следует подготовить восемь файлов данных. Если в tempdb отмечается конкуренция за выделенные ресурсы (она проявляется в увеличении значения параметра PAGELATCH_UP для ресурса ожидания, размещенного в tempdb), то добавляйте по четыре файла данных, пока это не прекратится.

Рекомендации по размерам файлов

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

В первую очередь важно настроить все файлы данных на увеличение с одинаковым интервалом. В процессе разработки и тестирования вы сможете собрать метрики, характеризующие предполагаемое использование tempdb, и реализовать их при внедрении базы данных в производство. Определить размеры файлов tempdb поначалу немного труднее, но возможности «облачной» подготовки упрощают задачу, так как подготовка хранилища обычно представляет собой интерактивный процесс в «облаке». Если нужно больше места, его можно выделить без обязательного простоя. Моя основная цель при определении размера файла для tempdb (и большинства других баз данных) — попытаться заранее довести свои файлы данных и журналов до максимальной прогнозируемой величины. Я не полагаюсь на автоматическое увеличение, но предусматриваю этот вариант, обеспечивая дополнительное пространство или выдачу предупреждений таким образом, чтобы иметь большой запас времени для выделения пространства по требованию.

Выбор хранилища для tempdb

Остается принять решение относительно класса хранилища для базы данных tempdb в SQL Server. Об этом решении проще всего забыть, особенно для «облачных» экземпляров SQL Server. Большинство поставщиков «облака» предоставляют хранилища с различными уровнями производительности. Например, у Microsoft Azure и Amazon AWS есть многочисленные варианты с дисками SSD наряду с жесткими дисками. Для большинства «облачных» экземпляров существует два варианта достижения целевого показателя операций ввода-вывода в секунду (IOPS). В зависимости от поставщика, вы можете подготовить класс накопителя с показателем IOPS в зависимости от размера диска в гигабайтах или приобрести класс накопителя, обеспечивающий определенный минимальный уровень IOPS. Предусмотрено также увеличение показателя IOPS, если выбранный уровень неприемлем для рабочего приложения, это еще одно достоинство «облачных» экземпляров SQL Server. Большинство изменений хранилища, связанных с коррекцией показателя IOPS для SQL Server, представляют собой интерактивные операции. Корректировка заданного показателя IOPS вверх или вниз обычно не влечет за собой перезапуска службы или перезагрузки сервера.

Пример из практики

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

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

Экран 1. Начальная задержка диска SQL Server

Мною был сделан ряд выводов относительно элементов данных, просто на основании показателей задержки, полученных в результате одного запроса к динамическому объекту управления sys.dm_io_virtual_file_stats:

1. Файлы данных и журналов для баз данных пользователя находятся на одном томе, что приводит к смешанному доступу при операциях чтения и записи, конфликтующих друг с другом.
2. Системные базы данных размещены на одном диске с операционной системой. Обычно я предпочитаю выделить особый диск для системных баз данных (помимо tempdb), но это не самая приоритетная задача.
3. Задержка диска, выделенного для tempdb, была ошеломляющей по сравнению с другими дисками.

Задержка операций чтения основного файла данных (32,0 мс на диске D по сравнению с 20 мс для любых файлов данных tempdb на диске E) вызывала вопросы, пока я не учел количество операций чтения и записи. Число операций чтения для основной пользовательской базы данных в 11 раз меньше аналогичного показателя для tempdb за тот же период времени. Операции записи для той же базы данных составляли половину таких операций для tempdb. Учтите также фактор оперативной памяти: пользовательская база данных может 10 раз разместиться в буферном пуле. Основная часть страниц пользовательской базы данных считывается в буфер один раз, и многократные циклы ввода-вывода обращены лишь к «грязным» страницам. Картина доступа к tempdb совершенно иная и менее постоянная из-за особенностей использования tempdb. Показатели задержки для пользовательской базы данных не соответствуют заданной мною целевой величине IOPS для оптимально функционирующего сервера (не более 5 мс), но их улучшение далеко не так критично, как корректировка характеристик tempdb.

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

Экран 2. Оптимизация задержки только благодаря изменению типа диска

Изменив лишь класс накопителя и установив более высокий уровень IOPS и интенсивность пакетной передачи, мы смогли улучшить показатели задержки чтения и записи более чем на 60% на томе tempdb. Это привело к общему уменьшению блокировок из-за запросов с зависимостями от операций с участием tempdb.

Готовы ли вы выделить время для анализа характеристик tempdb? Возможно, в вашем случае тоже существуют простые и быстрые пути решения проблем.