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?
Best Answer
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 intempdb
, 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 logREDO
information. You can test to see whether rows or pages are being logged using the undocumentedsys.fn_dblog
. Regular logging will feature row-logged records likeLOP_INSERT_ROWS
.Note that adding
TABLOCK
to a local temporary table is required to get parallel execution withINSERT...SELECT
queries in SQL Server 2016, see the Microsoft Knowledge Base article:Poor performance when you run INSERT.. SELECT operations in SQL Server 2016