PFS page round robin algorithm improvement in SQL Server 2014, 2016 and 2017

Applies to: Microsoft SQL Server 2016 Service Pack 1SQL Server 2016 DeveloperSQL Server 2016 Enterprise More

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       

Cumulative Update 1 for SQL Server 2016 SP2

Cumulative Update 9 for SQL Server 2016 SP1

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 Pack 3 for SQL Server 2014

 

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.