An Microsoft KB article,
Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use SQL Server 2016, really catch my eye. This article mentions that in order to workaround that TempDB
PAGELATCH wait symptom in SQL Server 2016, you should
remove the DROP TABLE commands for any temporary tables that won't be reused within the same execution of the stored procedure. What? Don't drop temporary tables inside stored procedures? Dropping them before the stored procedure finish should be the coding practice that many SQL developers believed in! But in fact, according to SQL Server book online, a local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished, so actually SQL developers not need to worry about it. Another suggestions mentioned in the KB article also worth to note:
- 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.
Inline Index Creation was introduced in SQL Server 2014, and
further improved in SQL Server 2016. Below is an simple example demonstrating the idea of that KB:
CREATE PROCEDURE uspTestTempTable
AS
BEGIN
CREATE TABLE #tempTable1 (
pk int PRIMARY KEY,
col1 int INDEX idxTempTbl1Col1,
col2 int,
col3 int,
INDEX idxTempTblCol2Col3 (col2, col3)
);
INSERT #tempTable1 (pk, col1, col2, col3) VALUES (0, 1, 2, 3);
SELECT * FROM #tempTable1;
/* DON'T DROP the TEMP TABLE */
END
GO
EXEC uspTestTempTable;
EXEC uspTestTempTable;
