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.
Cumulative Update information:
This issue is fixed in the following cumulative update 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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.