Оптимизация временной БД (tempdb)

При оптимизации производительности Microsoft SQL Server помимо настройки сервера, баз данных, транзакций и запросов важно обеспечить максимальную производительность временной базы данных (tempdb), особенно если программное обеспечение интенсивно использует временные таблицы (например, 1С), либо используется версионность как уровень изоляции транзакций.

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

Временная база данных является системной базой данных, которая использоваться как для системных, так и для прикладных задач:

  • Выполнение запросов с инструкциями ORDER BY, GROUP BY или операторами множеств (UNION, INTERSECT, EXCEPT). В плане выполнения необходимо обратить внимание на операторы Sort, Hash match, Spool.
  • Выполнение скриптов с использованием курсоров типа static или keyset.
  • Создание временных таблиц и табличных переменных.
  • Использование табличных функций.
  • Использование триггеров.
  • Подключение клиентов в режиме MARS (Multiple Active Result Sets).
  • Создание и обновление индексов.
  • Уровень изоляции транзакций с версионностью (SNAPSHOT).
  • Использование больших объектов (varchar(max), nvarchar(max), varbinary(max), text, ntext, image, xml) как параметров или переменных.
  • Выполнение команды DBCC CHECK.
  • Использование Service Broker или Database mail.

Анализ

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

SELECT SUM(user_object_reserved_page_count)*8 as usr_obj_kb,
SUM(internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM(version_store_reserved_page_count)*8 as version_store_kb,
SUM(unallocated_extent_page_count)*8 as freespace_kb,
SUM(mixed_extent_page_count)*8 as mixedextent_kb
FROM tempdb.sys.dm_db_file_space_usage

Соответственно, в первой колонке  usr_obj_kb мы видим сколько данных во временной базе данных используется в прикладном коде, например, при создании временных таблиц. Колонка internal_obj_kb показывает, сколько данных используется для системных задач, а version_store_kb показывает объем данных для хранения версий строк при использовании версионности.

Более детальный анализ использования временной базы данных в реальном времени можно сделать с помощью следующего запроса:

SELECT es.session_id
, ec.connection_id
, es.login_name
, es.host_name
, st.text
, su.user_objects_alloc_page_count
, su.user_objects_dealloc_page_count
, su.internal_objects_alloc_page_count
, su.internal_objects_dealloc_page_count
, ec.last_read
, ec.last_write
, es.program_name
FROM tempdb.sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec ON su.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st

В этом запросе мы видим объем данных во временной базе для каждой сессии.

Для оценки производительности необходимо сделать анализ производительности ввода-вывода для файлов данных временной базы данных:

SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files 
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

Если время записи данных (avg_write_stall_ms) меньше 10 мс, то это значит хороший уровень производительности. Между 10 и 20 мс  — приемлемый уровень. Более 20 мс — низкая производительность, необходимо сделать детальный анализ. Более 50 мс — имеются проблемы с вводом-выводом для временной базы данных.

Рекомендации

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

Начальный размер временной базы данных

Рекомендуется минимальный размер временной базы данных:

  • Для небольших рабочих БД — 1024MB для данных и 256MB для журнала транзакций.
  • Для средних рабочих БД — 5120MB для данных и 1024MB для журнала транзакций.
  • Для больших рабочих БД — 10024MB для данных и 2048MB для журнала транзакций.

Если нагрузка на временную базу данных является высокой и она увеличивается больше, чем рекомендуемый размер, то, увеличьте ее начальный размер на необходимое значение.

Размещение файлов данных

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

Для ускорения ввода-вывода рекомендуется создавать несколько файлов данных в зависимости от количества логических процессоров, выделенных для сервера базы данных (Database Engine). Если логических процессоров меньше 8, рекомендуется создать файл данных для каждого логического процессора.

Если логических процессоров больше 8, рекомендуется создать 8 файлов данных и оценить нагрузку на временную базу данных. При необходимости создать дополнительные файлы данных по следующей формуле: ([количество логических процессоров] — 8) / 4.

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

Для снижения времени задержки и балансировки нагрузки рекомендуется задавать одинаковый размер файлов.

Для ускорения увеличения размера файлов данных рекомендуется настроить моментальную инициализацию файлов. Особенно это важно для больших баз данных.

Аппаратное обеспечение

При размещении временной базы данных для ускорения ее работы используются различные аппаратные решения:

  • SSD, в т.ч. PCI Express — с падением стоимости и увеличением надежности может стать оптимальным решением для сервера базы данных. Один твердотельный накопитель может заменить по производительности RAID-массив из HDD, будучи дешевле. Но необходимо тщательно выбирать на основе параметров подходящую модель (см., например, рекомендации SQL Server and SSDs).
  • RAID 10, в т.ч. на основе SSD — поскольку временная база данных является системной и ее сбой приведет к остановке сервера базы данных, необходимо, помимо производительности, также обеспечить надежность.
  • RAM disk — размещение файлов в оперативной памяти (программное решение) или на PCI карте с DIMM (аппаратное решение). Может ускорить производительность операций с временной базой данных на порядок и более.

Источники

  1. База данных tempdb
  2. Tempdb Configuration Best Practices in SQL Server
  3. Working with tempdb in SQL Server 2005
  4. TempDB Performance and Configuration
  5. Recommendations to reduce allocation contention in SQL Server tempdb database
  6. Using Solid State Disks in SQL Server Storage Solutions
  7. Migrating Server Storage to SSDs: Analysis of Tradeoffs
  8. Put TEMPDB on a RAM DRIVE
  9. Уровни RAID и SQL Server