or
sql-server
pixelated imported from SE
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
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
Get minimal logging when loading data into temporary tables

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.