SQL Server Performance

Truncate table Vs Create and Drop table

Discussion in 'T-SQL Performance Tuning for Developers' started by ramasubbup, Nov 17, 2005.

  1. ramasubbup New Member

    We have a DW system.
    In the staging database we have to do a lot of calculations.
    We use TRUNCATE statement to clean up the staging tables and populate them
    My database recovery mode is 'FULL'

    My view is that using TRUNCATE will increase the transaction log size.


    1. Is it advisable to replace TRUNCATE with DROP/CREATE table Option?
    2. I have to give recommendation on maximum capacity my server can handle
    Is there any site where we have statistics for Inserts/Update/Delete operations
    for a given environment ?

    Advance thanks for any help

    Prabhu S
  2. ranjitjain New Member

    It's really wrong thinking.

    Truncate is the Minimally logged operation.
    Whereas Create and Drop will surely increase the Log Size.
  3. Madhivanan Moderator

    Truncate is enough

    Run sp_spaceused before and after truncation and see if there is size difference

    Madhivanan

    Failing to plan is Planning to fail
  4. ramasubbup New Member

    Thanks for your reply.

    Prabhu S
  5. ghemant Moderator

    Hi,<br />pleaser read :<br /><br /><a href='http://www.sql-server-performance.com/q&a118.asp' target='_blank' title='http://www.sql-server-performance.com/q&a118.asp'<a target="_blank" href=http://www.sql-server-performance.com/q&a118.asp>http://www.sql-server-performance.com/q&a118.asp</a></a><br /><br />2. I have to give recommendation on maximum capacity my server can handle<br />Is there any site where we have statistics for Inserts/Update/Delete operations<br />for a given environment ?<br /><br />It *depends* upon IO Ratio of your Disks , please refer below articles if it helps :<br /><br /><a href='http://www.sql-server-performance.com/statistics_io_time.asp' target='_blank' title='http://www.sql-server-performance.com/statistics_io_time.asp'<a target="_blank" href=http://www.sql-server-performance.com/statistics_io_time.asp>http://www.sql-server-performance.com/statistics_io_time.asp</a></a><br /><br /><a href='http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf' target='_blank' title='http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf'<a target="_blank" href=http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf>http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf</a></a><br /><br /><a href='http://www.sql-server-performance.com/jc_large_data_operations.asp' target='_blank' title='http://www.sql-server-performance.com/jc_large_data_operations.asp'<a target="_blank" href=http://www.sql-server-performance.com/jc_large_data_operations.asp>http://www.sql-server-performance.com/jc_large_data_operations.asp</a></a><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  6. mmarovic Active Member

    quote:Originally posted by ranjitjain

    It's really wrong thinking.

    Truncate is the Minimally logged operation.
    Whereas Create and Drop will surely increase the Log Size.
    Interesting! Ranjit, have you tested it?
  7. ranjitjain New Member

    quote:Originally posted by mmarovic


    quote:Originally posted by ranjitjain

    It's really wrong thinking.

    Truncate is the Minimally logged operation.
    Whereas Create and Drop will surely increase the Log Size.
    Interesting! Ranjit, have you tested it?
    Not Tested But from my viewpoint has to be that.
    Whats your opinion????????
  8. FrankKalis Moderator

    If this is really a DW, why do you run it in Full Recovery mode? Wouldn't Simple Recovery be good enough? Do you really need the additional security of Full-Recovery like restoring to point-of-failure?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  9. ramasubbup New Member

    Since it is a production system, it is set as FULL RECOVERY MODE.

    Will there be any impact in BACKUP/RESTORE process if I change the database
    to simple recovery only for the loading time and back to FULL RECOVERY
    ( If I am using a Differencial transaction log backup, won't it affect ?)
  10. FrankKalis Moderator

    Our DW is also in production. However, there are very few write operations. We don't take any backup of it at all since it is completely loaded from scratch once or twice a week and if anything happens in between it is simply loaded anew.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  11. mmarovic Active Member

    quote:Originally posted by ranjitjain


    quote:Originally posted by mmarovic


    quote:Originally posted by ranjitjain

    It's really wrong thinking.

    Truncate is the Minimally logged operation.
    Whereas Create and Drop will surely increase the Log Size.
    Interesting! Ranjit, have you tested it?
    Not Tested But from my viewpoint has to be that.
    Whats your opinion????????
    I don't know how it is implemented, but IMO it might be logged as much as truncate operation. My question should be actually have you read that somewhere or tested. I would really like to know the answer. It is interesting I haven't thought about that before. If there is significant difference between the two then it would be important to know which solution is better.
  12. FrankKalis Moderator

    TRUNCATE is a minimally logged transaction. The deallocation of the pages is recorded in the log. This produces only minimal overhead.

    A CREATE TABLE is likely to produce similar few overhead, since the log only records the fact that a new table is being created.

    A DROP TABLE is, IMHO, very similar to TRUNCATE in that only the fact that a table is being dropped is recorded. So, internally I suspect SQL Server to remove some pointers and some rows from the system tables and nothing more.

    So, in all three cases I would suspect neglectible impact on the size of T-Log, but I would also be really interessed when someone can pass some experience here.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  13. satya Moderator

    I agree with Frank, as in 3 cases referred Transaction log will have its usage.
    Checkhttp://www.sql-server-performance.com/q&a118.asp for reference on the truncate options.

    Truncate table is a logged operation--even though it is minimally logged. If it is executed within a transaction, a rollback will undo it.


    Prabhu,
    If you decide to keep the database in SIMPLE recovery model then ensure to maintain regular database backups that can help to recover the data in any failure conditions. Please take time to read about RECOVERY MODELS topic under books online that explains the situation and you're at best to decide which one to opt.

    Can you confirm the size of database and size of transaciton log.
    Also any issues on hard disk to increase the sizes?

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page