Почему файлы tempdb в MS SQL Server растут неравномерно и как это исправить?

Системный администратор IT и автоматизация бизнеса
← На главную

При настройке высоконагруженных серверов для работы с 1С:Предприятие администраторы часто сталкиваются с рекомендацией разделять базу данных tempdb на несколько файлов данных. Это делается для снижения конкуренции за системные ресурсы. Однако, после выполнения этой операции можно заметить странное поведение: один файл вырастает до огромных размеров (например, 10 ГБ), в то время как остальные остаются минимального размера (например, 1 ГБ). При этом статистика показывает, что почти вся нагрузка ложится именно на этот большой файл.

В этой статье мы подробно разберем, почему так происходит, как работает алгоритм распределения данных в SQL Server и какие настройки необходимо применить, чтобы система работала эффективно.

Суть проблемы: неравномерная нагрузка

Представим ситуацию: мы разделили tempdb на 8 файлов по 1 ГБ каждый и переместили их на отдельный быстрый диск. Ожидание таково, что SQL Server будет писать данные во все файлы равномерно. Реальность же часто такова, что первый файл начинает расти, забирая на себя все операции чтения и записи, а остальные простаивают.

Это происходит из-за особенностей внутреннего алгоритма работы SQL Server, называемого Proportional Fill (Пропорциональное заполнение).

Как работает алгоритм Proportional Fill

SQL Server не использует простой перебор файлов (Round Robin) в чистом виде. Вместо этого он использует алгоритм, который распределяет запись данных пропорционально свободному месту в файлах.

Разберем механизм возникновения проблемы по шагам:

  1. Изначально у нас есть 8 файлов по 1 ГБ. Свободного места в них поровну. Запись идет равномерно.
  2. В какой-то момент места во всех файлах не хватает для выполнения крупной транзакции (или серии транзакций).
  3. Срабатывает механизм автоувеличения (Autogrowth). Если настройки не синхронизированы, может вырасти только один файл (например, первый).
  4. Теперь первый файл имеет размер, скажем, 2 ГБ (и в нем появилось много свободного места), а остальные — по 1 ГБ (и они почти полны).
  5. Алгоритм Proportional Fill видит, что в первом файле свободного места значительно больше, чем в остальных.
  6. С этого момента SQL Server начинает направлять практически весь поток новых данных именно в первый файл.
  7. Из-за постоянной записи первый файл заполняется быстрее и снова вынужден расти. Круг замыкается: файл становится все больше, и чем он больше (и чем больше в нем свободного места после расширения), тем больше нагрузки он на себя оттягивает.

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

Зачем вообще делить tempdb на файлы?

Прежде чем переходить к решению, давайте вспомним, зачем мы это делаем. Это не вопрос скорости диска (особенно если у вас SSD). Проблема кроется в конкуренции за блокировки в оперативной памяти (Latch Contention).

При создании и удалении временных таблиц SQL Server должен обновлять специальные системные страницы, которые отслеживают занятость места:

Если файл один, то и набор этих страниц один. Все потоки 1С выстраиваются в очередь, чтобы обновить одну и ту же страницу в памяти. Если файлов несколько — таких страниц становится больше, и очереди исчезают.

Решение проблемы для MS SQL Server 2014 и старше

Для версий SQL Server до 2016 года (включая 2008, 2012, 2014) проблему решают два флага трассировки (Trace Flags). Без них добиться равномерной работы крайне сложно.

Флаг 1117 (Trace Flag 1117)

Этот флаг меняет поведение автоувеличения файлов. Если он включен, то при необходимости расширения одного файла в файловой группе, SQL Server принудительно расширяет все файлы в этой группе одновременно.

Это гарантирует, что файлы всегда будут оставаться одинакового размера, а значит, и количество свободного места в них будет расти синхронно. Алгоритм Proportional Fill будет видеть одинаковое свободное место и распределять нагрузку равномерно.

Важно: Этот флаг действует на весь экземпляр SQL Server, то есть на все базы данных, а не только на tempdb. Однако, как правило, в пользовательских базах данных файлы данных и логов разнесены по разным файловым группам, и это поведение не вредит.

Флаг 1118 (Trace Flag 1118)

Этот флаг заставляет SQL Server выделять место сразу полными экстентами (Uniform Extents), избегая использования смешанных экстентов (Mixed Extents). Это снижает конкуренцию за SGAM-страницы.

Как правильно включить флаги

Многие допускают ошибку, включая флаги командой DBCC TRACEON в текущей сессии. Это не сработает для процессов автоувеличения, которые происходят в фоне.

Флаги необходимо прописать в параметры запуска службы SQL Server (Startup Parameters) через SQL Server Configuration Manager:

  1. Откройте SQL Server Configuration Manager.
  2. Зайдите в свойства службы SQL Server.
  3. Перейдите на вкладку Startup Parameters.
  4. Добавьте параметры: -T1117 и -T1118.
  5. Перезапустите службу SQL Server.

Особенности SQL Server 2016 и новее

Начиная с версии SQL Server 2016, поведение этих флагов стало стандартом:

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

Практические рекомендации по настройке (Best Practices)

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

1. Предварительное задание размера (Pre-sizing)

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

Если вы выделили под tempdb отдельный диск на 100 ГБ и создали 8 файлов, сразу задайте размер каждого файла примерно по 12 ГБ. Заполните все доступное пространство сразу при старте. Это исключит срабатывание механизма автоувеличения и, как следствие, перекосы в алгоритме Proportional Fill.

2. Одинаковые настройки автоувеличения

Убедитесь, что у всех файлов tempdb стоят абсолютно идентичные настройки Autogrowth. Например, у всех стоит приращение по 512 МБ. Если у одного файла будет стоять 100 МБ, а у другого 10%, флаг 1117 может отработать некорректно или возникнет рассинхронизация размеров.

3. Количество файлов

Классическая рекомендация Microsoft: начинать с количества файлов, равного числу логических ядер процессора, но не более 8. Если после этого вы все еще наблюдаете ожидания на страницах PFS/GAM (PAGELATCH_UP, 2:1:1, 2:1:3), можно увеличивать число файлов кратно 4 (до 12, 16 и т.д.), но обычно 8 файлов достаточно для большинства систем 1С.

Пример диагностики проблемы

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


USE tempdb;
GO

-- Создаем тестовую таблицу
CREATE TABLE [dbo].[TestTable](
    [ID] [INT] IDENTITY PRIMARY KEY NOT NULL,
    [Filler] [CHAR](4000) NOT NULL
);
GO

-- Генерируем данные для вставки
INSERT INTO dbo.TestTable (Filler)
SELECT TOP (100000) b.[name]
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b
WHERE a.type = 'P';
GO

-- После выполнения можно проверить размеры файлов и их использование
-- через системные представления sys.dm_db_file_space_usage

Запустив такой скрипт, вы можете в реальном времени наблюдать через мониторинг активности или системные представления, как прирастают файлы. Если настройки выполнены верно (включены флаги 1117/1118 или используется SQL 2016+ с правильными настройками), все файлы должны расти синхронно и иметь одинаковый процент заполнения.

Резюме

Проблема неравномерного использования файлов tempdb возникает из-за разницы в свободном месте внутри файлов. Чтобы решить её:

  1. Установите одинаковый начальный размер для всех файлов.
  2. Идеально — сразу займите файлами всё место на диске, чтобы исключить авторост.
  3. Для SQL Server 2014 и ниже обязательно добавьте флаги -T1117 и -T1118 в параметры запуска службы.
  4. Убедитесь, что параметры автоувеличения идентичны для всех файлов.
← На главную