Symptoms
Consider the following scenario:
-
You have an application that heavily uses the tempdb database, or you have a specific tempdb database workload.
-
You upgrade to Microsoft SQL Server 2016 or a later version and start seeing PFS page contention.
-
You follow tempdb database best practices and add several data files.
In this scenario, heavy PFS page contention continues and affects system performance.
Note If the page's map is easily divided by 8088, it is a PFS page. For example, page 2:3:905856 is a PFS in file_id=3 in tempdb.
Resolution
Cumulative Update information
This issue is fixed in the following cumulative updates for SQL Server:
Cumulative Update 7 for SQL Server 2017
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
This issue is fixed in the hotfix that is described in the following article:
4099490 On-demand hotfix update package for SQL Server 2016 SP1
This fix improves PFS page allocation by performing round-robin allocations across several PFS pages in the same data file in addition to the round-robin allocation across all tempdb data files. Therefore, the more PFS data pages that are contained in a data file and the more data files there are, the better the allocation distribution.
Note Because data is now spread across the PFS intervals within the tempdb database files, operations such as database shrink will become more resource-intensive. However, it is not generally recommended to shrink tempdb during the typical course of operation.
Service Pack information for SQL Server:
This update is fixed in the following service pack for SQL Server:
Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You do not have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update.
How to determine the version, edition and update level of SQL Server and its components
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Recommendations to reduce allocation contention in SQL Server tempdb database
