Even after reading [The Data Loading Performance Guide][1] , I am still 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. 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? [1]: http://technet.microsoft.com/en-us/library/dd425070%28v=sql.100%29.aspx
TopAnswers
Databases
Top Answer
Paul White
> *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][1] [1]: https://support.microsoft.com/en-us/kb/3180087