Get minimal logging when loading data into temporary tables

Question

After reading The Data Loading Performance Guide, I am still unsure if it's necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index, in order to get minimal logging.

Obviously, the temp table is created in TempDB, which operates in the SIMPLE recovery mode, so I would have thought that it was a perfect candidate for minimal logging; however, I can not find a passage to confirm it.

Is a temporary table a candidate for minimal logging, and if so, is it worth adding the TABLOCK hint as recommended for permanent tables?

Answer 1

I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging.

No. Local temporary tables (#temp) are private to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (##temp) or a regular table (dbo.temp) created in tempdb, because these can be accessed from multiple sessions.

Note that even where the full minimal logging optimizations are not applied, tables created in tempdb benefit from other optimizations like not needing to log REDO information. You can test to see whether rows or pages are being logged using the undocumented sys.fn_dblog. Regular logging will feature row-logged records like LOP_INSERT_ROWS.

---

Note that adding TABLOCK to a local temporary table is required to get parallel execution with INSERT...SELECT queries in SQL Server 2016, see the Microsoft Knowledge Base article:

Poor performance when you run INSERT.. SELECT operations in SQL Server 2016