June 6, 2006 at 2:08 am
Does tempdb log? if yes, to what extent? pls guide
I am running stored procedures on VLDB. within these SP, several intermediatory tables are created which are dropped later within same SP.
if i change these tables from normal tables to ##table, i think i will gain performance as there should be no logging. intermediate table have insert, update, delete and drop statements on them.
Can any one help if my logic is right?
Thanks & Regards
Amit
June 6, 2006 at 7:00 am
All databases have a log and that includes the TempDB. And any transactions on the TempDB are logged.
-SQLBill
June 7, 2006 at 3:42 am
the only difference with tempdb is that the log is on the same device with data.
June 7, 2006 at 5:16 am
Logging in Tempdb is done in exactly the same way as for any other DB in Simple recovery mode. i.e. everything gets logged, so that anything can be backed out. When the transaction (explicit or implicit) does a COMMIT, the log data associated with that transaction becomes eligable to be truncated. The log truncate happens when all log data in a given logical log file is no longer required. (See BOL for more details of logical log file processing.)
If an intermediate table is going to be small, say under 1MB in size, then you can save meaningful amounts of time by storing the data in a table variable instead of a normal or temporary table. A table variable is initially created only in memory, and will only get written to disk if it is too big to hold in memory or is not accessed for some time.
You are unlikely to see any measureable performance improvement simply by changing from a normal table to a #temp or ##temp table.
The biggest performance gain may come by reviewing your SP processing to see if you can eliminate the need for some intermediate tables by taking a set-based approach to data retrieval.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 7, 2006 at 11:17 am
This is not the case. tempdb is like any other database it contains at a minimum of an .mdf file for data portion of the database and an .ldf file for the transaction log portion of the database. However by default both of these files reside in the defaut SQL Server data directory. It is up to you , the DBA, to move the .mdf and .ldf files to different disk spindles for performance, integrity and recovery.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply