mssql2019: ускорить tempdb и перенести их на NVME диск
Лучший способ — добавить файлы командой ALTER DATABASE tempdb ADD FILE, задать одинаковый размер и прирост, потом перезапустить службу SQL Server.
►1) Посмотреть, что есть сейчас:
|
1 2 3 4 5 |
USE master; GO SELECT name, file_id, type_desc, physical_name, size*8/1024 AS size_mb FROM sys.master_files WHERE database_id = DB_ID('tempdb'); |
Обычно там tempdev (data) и templog (log).
►2) Добавить ещё 3 data-файла (чтобы всего было 4):
|
1 2 3 4 5 6 7 8 |
USE master; GO ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'q:\!sql-temp\tempdb2.ndf', SIZE = 2048MB, FILEGROWTH = 512MB); ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'q:\!sql-temp\tempdb3.ndf', SIZE = 2048MB, FILEGROWTH = 512MB); ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = 'q:\!sql-temp\tempdb4.ndf', SIZE = 2048MB, FILEGROWTH = 512MB); |
Поменяйте q:\!sql-temp\ на путь к вашему NVMe-диску.
►3) Выровняйте размеры
Убедитесь, что все четыре data-файла одинаковые по размеру и приросту:
|
1 2 3 4 5 6 7 8 |
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 2048MB, FILEGROWTH = 512MB); ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev2, SIZE = 2048MB, FILEGROWTH = 512MB); ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev3, SIZE = 2048MB, FILEGROWTH = 512MB); ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev4, SIZE = 2048MB, FILEGROWTH = 512MB); |
►4) Перезапустить SQL Server
Файлы tempdb создаются заново при каждом старте службы, поэтому новые data-файлы появятся только после рестарта.
Рекомендации:
- Число файлов обычно = числу vCPU, но не более 8. Для начала — 4 файла нормально.
- Все файлы храните на одном NVMe-разделе, не нужно разводить по дискам.
- Лог (templog.ldf) оставьте одним файлом.
Что будет, если на диске с tempdb закончится место
Запущенные запросы начнут падать с ошибками
►1) Типично в error log и клиенте будет что-то вроде:
|
1 2 |
Msg 1101, Level 17, State 12, Line 1 Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. |
►2) Новые сессии тоже могут пострадать — если для их операций требуется tempdb (сортировки, хеши, CTE, индексы, версии строк для snapshot isolation, и т. д.).
►3) SQL Server не «умирает» сразу, но всё, что требует tempdb, не сможет выполняться. Фактически система будет наполовину «висеть».
►4)Служба SQL Server продолжает работать, но нагрузка фактически парализуется.
Что происходит дальше
- Как только вы освободите место или увеличите размер файла tempdb — SQL Server снова сможет работать.
- После перезапуска службы tempdb пересоздаётся с нуля и занимает заново заданный размер. Ошибки исчезают, если на диске теперь есть свободное место.
Как защититься
- Выставить фиксированный размер и автогроус tempdb (например, сразу по 2–4 ГБ, прирост 512 МБ или 1 ГБ). Тогда рост будет прогнозируемый.
- Мониторинг свободного места на том диске (Zabbix, SCOM, perfmon, любой алертинг).
- Диск под tempdb лучше не переполнять другими файлами. Иногда админы выделяют под tempdb отдельный LUN/раздел.
- Если места немного — лучше сразу ограничить рост файлов (MAXSIZE), чем поймать полный стоп всего сервера.
SQL не падает, но запросы, требующие tempdb, начинают валиться. После освобождения места — система оживает. После перезапуска tempdb полностью пересоздаётся.