sql-server add tag
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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.