Статьи

TempDB для производительности

Март 25, 2018
Вступление


TempDB играет очень информативную роль в случае проблем с производительностью. В этой статье мы пытаемся узнать что-то, связанное с TempDB. Мы берем ссылки от Microsoft MSDN для этой статьи.

Надеюсь, это будет информативным.
Когда мы используем TempDB

База данных системы TempDB — это глобальные ресурсы для всех пользователей, связанных с интерфейсом SQL Server. Следующие объекты хранятся в TempDB, упомянутом ниже.
Пользовательские объекты  явно создаются пользователем, а область действия пользовательского объекта зависит от конкретной сессии или в рамках подпрограммы, в которой он создается. Здесь подпрограмма означает хранимую процедуру (SP), триггер или пользовательскую функцию (UDF).

Пример пользовательского определения объектов приведен ниже

· Пользовательские таблицы и индексы

· Системные таблицы и индексы

· Глобальные временные таблицы и индексы

· Локальные временные таблицы и индексы

· Табличные переменные

· Таблицы, возвращаемые в табличных функциях
Внутренние объекты  создаются при необходимости ядром базы данных SQL Server для обработки операторов SQL Server. Внутренние объекты создаются и удаляются в рамках оператора.

Внутренние объекты могут быть одним из следующих:
  • Рабочие таблицы для операций курсора или буферизации и временного хранения больших объектов (LOB).
  • Рабочие файлы для операций хеширования или объединения хэшей.
  • Промежуточные результаты сортировки для таких операций, как создание или перестроение индексов (если указано SORT_IN_TEMPDB), или определенных запросов GROUP BY, ORDER BY или UNION.

Хранилища версий — это набор страниц данных, которые содержат строки данных, необходимые для поддержки функций, использующих управление версиями строк. Существует два хранилища версий: общее хранилище версий и онлайн-хранилище версий индекса.

Хранилища версий содержат следующее:


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

· Версии строк, которые создаются транзакциями изменения данных для таких функций, как: операции с индексами в сети, множественные активные наборы результатов (MARS) и триггеры AFTER.
Управление TempDB

Поэтому мы должны особенно заботиться о TempDB для поддержания производительности базы данных. Здесь Microsoft дает нам некоторые рекомендации, которые мы собираемся обсудить.

1. Установите модель восстановления TempDB в ПРОСТОЙ. Эта модель автоматически восстанавливает пространство журнала.

2. Разрешить автоматическое увеличение файлов TempDB по мере необходимости. Это позволяет файлу расти, пока диск не заполнится. Старайтесь избегать роста файла TempDB с небольшими значениями, поскольку автоматическое увеличение занимает определенное время, и наше приложение этого не допустит.

Вот таблица рекомендаций от Microsoft.
Размер файла TempDB
Приращение FILEGROWTH
От 0 до 100 МБ

10 МБ
От 100 до 200 МБ

20 МБ
200 МБ или больше

10% *

3.Выделите место для всех файлов TempDB, установив для размера файла достаточно большое значение, чтобы удовлетворить типичную рабочую нагрузку в среде. Это предотвращает слишком частое расширение TempDB, что может повлиять на производительность.


4. Создайте столько файлов, сколько необходимо для максимизации пропускной способности диска.

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

6. Поместите TempDBdatabase на диски, которые отличаются от тех, которые используются пользовательскими базами данных.
Как измерить размер и темп роста TempDB 
SELECT name AS FileName, size*1.0/128 AS FileSizeinMB,
CASE max_size WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0 AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO
FileName FileSizeinMB (без имени столбца) GrowthValue GrowthIncrement

темпдев 8.000000 Автостоп включен. 10 Значение роста в процентах.

templog 0.500000 Авто рост включен. 10 Значение роста в процентах.

Надеюсь, вам понравится.

9561
Share: