архивариусMegabyteЕсли вдруг кому будет интересно, напишу, что делал.
1) Добавление файлов tempDb до 4шт на 4 процессора, как по рекомендации Микрософт, ничего не дало.
1 шт. не на процессор, а на физическое ядро, до 8 шт не задумываясь, если больше то нужно смотреть.(даже на одном и том же диске)
авторRecommendations to reduce allocation contention in SQL Server tempdb database
Symptoms
You observe severe blocking when the SQL Server is experiencing heavy load. When you examine the Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks], you observe that these requests or tasks are waiting for tempdb resources. Additionally, you will notice that the wait type is PAGELATCH_UP and wait resource points to pages in tempdb. These pages might be of the format 2:1:1, 2:1:3 and so on (PFS and SGAM pages in tempdb).
Note If a page is evenly-divisible by 8088, then it is a PFS page. For example, page 2:3:905856 is a PFS in file_id=3 in tempdb.
Cause
When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve tempdb to be unresponsive for short periods of time.
Resolution
There are several steps you can take to improve the concurrency of tempdb:
As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
Consider implementing the best practice recommendations in the Technet article titled Working with tempdb in SQL Server 2005.
If the previous steps do not significantly reduce the allocation contention and the contention is on SGAM pages, implement trace flag -T1118. Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages. Note that this trace flag affects every database on the instance of SQL Server. For information about how to determine whether the allocation contention is on SGAM pages, see Monitoring contention caused by DML operations subtopic under Working with tempdb in SQL Server 2005 page on Technet.
Starting with SQL Server 2016, some of these configuration changes are automatic and do not need user intervention. For more information
инсталятор SQL сам создает теперь несколько файлов tempdb .
Мы пока на 2008м, правда скоро планируем апгрейд.
