Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use SQL Server 2016

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

Symptoms


When you use Microsoft SQL Server 2016, increased PAGELATCH_EX and PAGELATCH_SH wait time occurs on the database pages that belong to sys.sysobjvalues and sys.sysseobjvalues system tables in TempDB.

Workaround


To work around this issue, review your application and apply the following methods where applicable. Also make sure that you use these methods as best practices in future Transact-SQL development:

  • Remove the DROP TABLE commands for any temporary tables that won't be reused within the same execution of the stored procedure. SQL Server will remove the temporary tables when the session that created the temporary tables has ended.
  • Avoid using the TRUNCATE TABLE command against temporary tables.
  • Avoid altering temporary tables after they have been created.
  • If indexes are created against the temporary table, consider moving these to inline index creation statements within the CREATE TABLE command.

Resolution


This fix is included in the following updates:

Cumulative Update 1 for SQL Server 2016 Service Pack 2

Cumulative Update 9 for SQL Server 2016 Service Pack 1