SQL SERVER – Regular Table or Temp Table – TempDB Logging Explained

Yesterday I wrote a blog post about based on the question SQL SERVER – Regular Table or Temp Table – A Quick Performance Comparison my client who is a recently hired me for Comprehensive Database Performance Health Check. The question was a very simple but interesting one and I answered it in the previous blog. After the blog posts, I have received quite a few questions and many people were still confused about it hence I decided to write this blog post about TempDB Logging.

Before you continue reading the blog post, I suggest you read the blog post.

Question: Is the behavior of no-logging applies to the temporary tables or temporary database TempDB?

Answer: The behavior is different for Temporary Tables and Regular Tables. Here is the brief experiment I have done which will help you clarify the difference between the temp table and regular tables.

Now observe the table which is displayed here in the table. You can see that Temp Table has always amazing performance irrespective of which database I am creating them as they are always created in the TempDB. Whereas when you look at the regular table, there is performance different between it is was created in the tempdb as well as in the regular user database. It takes twice more time in the regular user database for loading the data compared to the tempdb.

TempDB Logging Explained

When we change any value in the regular database, both the old and new values are stored in the Transaction log. The old value is used for UNDO (during the rollback) and the new values are used for REDO (during the roll forward when server restarts or recovery process kicks in).

In the case of the TempDB, there is no need to store the new values in the log file as there is no need for the REDO operation when SQL Server services restart at all. TempDB is always recreated from the scratch when the server instance is restarted, hence logging in the TempDB stores only UNDO values and no REDO Value. As TempDB logging records only half of the values, the efficiency of the TempDB is faster for the regular table.

I hope this explains why the operation in the TempDB are faster for many of the user operations/objects.

Reference: Pinal Dave (https://blog.sqlauthority.com)