Улучшения tempdb в SQL Server 2016

Олонцев Сергей написал отличную статью: SQL Server 2016: конфигурация tempdb во время установки

Хотелось бы продемонстрировать вам небольшую вырезку из статьи и немного дополнить:

Во время установки SQL Server 2016, теперь можно указать размер и количество файлов БД

Предлагаемое количество файлов по-умолчанию можно узнать по следующей таблице:

Ядер CPU	Количество файлов в tempdb
2	2
4	4
8	8
32	8

Улучшения в производительности при работе с tempdb

Также в работу tempdb были внесены следующие изменения, направленные на оптимизацию и ускорение выполнения запросов:

• Кэширование временных объектов позволяет запросам, которые постоянно удаляют и создают временные объекты работать быстрее и уменьшают конкуренцию за системные ресурсы. В последних версиях SQL Server можно было регулярно видеть изменения и улучшения этого механизма.
• Уменьшена нагрузка на журнал транзакций в tempdb, снижено количество требуемых I\O операций.
• Доработан алгоритм накладывания latch’ей при выделении страниц, уменьшено их количество.
• При приращении tempdb теперь одновременно будет увеличен размер всех файлов (отпадает необходимость включать флаг трассировки 1117). Опция AUTOGROW_ALL_FILES включена по умолчанию и не может быть изменена. Это поможет избежать разбалансирования размеров файлов при постоянно приросте tempdb.
• Для временных объектов идет выделение только экстентами (блоками по 8 страниц, 64 кб). Отпадает необходимость включать флаг трассировки 1118. Это также поможет в большей части случаев.

Дополнение:

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

Select session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
		ResourceType = Case
When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
			When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
			When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
			Else 'Is Not PFS, GAM, or SGAM page'
			End
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%' 

Этим запросом мы пытаемся найти latch на системные страницы PFS, GAM, SGAM в базе данных tempdb. Если запрос ничего не возвращает или возвращает строки только с «Is Not PFS, GAM, or SGAM page», то скорее всего текущая нагрузка не требует увеличения файлов tempdb.

Дополнительно рекомендуется отслеживать счётчик производительности (Performance Monitor) на SQLServer:Databases — Transaction/sec в tempdb и если tempdb стал пропускать больше транзакций при той же нагрузке, значит вы идёте в нужном направлении.