Post reply

Effect of Truncate on Indexes

  • April 19, 2009 at 6:45 am

    #129415

    We have about 50 tables that contain almost static data. At times about 0.1% of data in some tables changes in development environment. We then script it out and promote to other environments.

    The process we follow is -

    1. Truncate all the 50 tables.

    2. Re-load data in these 50 tables via script.

    Question:-

    What is the effect on Indexes? Do I need to rebuild indexes on these tables after re-loading the data? As per my understanding, when the data is truncated, the pages are deallocated and same applies to Index. When you re-load the data via script, the index gets populated/created with insertion and it would be contiguous due to single data load.

    Need help to understand the effect of Truncate and re-load on Indexes.

    Thanks.

  • April 19, 2009 at 10:03 am

    #980356

    Patelbhai (4/19/2009)

    Question:-

    What is the effect on Indexes?

    Indexes definition remain the same.And this will apply to the new records as they are added.

    Do I need to rebuild indexes on these tables after re-loading the data?

    Before reloading the data there is nothing to reindex, you can sp_spaceused 'yourtable' to check the data pages but after you insert check out the fragmentation levels- but I doubt there would be any fragmentation as there is a clustered index already on the table with no data. As you insert the data the server will put them in order. What is your fill factor value?

  • April 19, 2009 at 12:02 pm

    #980375

    Patelbhai (4/19/2009)

    Do I need to rebuild indexes on these tables after re-loading the data?

    Depends how you're reloading. If it's in one operation, unlikely. If you're doing lots of small inserts, maybe. Check the fragmentation level straight after the reload and see what the avg fragmentation % is.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 19, 2009 at 7:33 pm

    #980476

    does truncating force a rebuild(resetting?) on statistics if auto-update statistics is ON?

    i know there is a threshold of 20% rows changed before the auto update occurs, but since truncate is not logged, will the statistics get updated since 100% of the rows were affected?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 20, 2009 at 1:03 am

    #980530

    Lowell (4/19/2009)

    does truncating force a rebuild(resetting?) on statistics if auto-update statistics is ON?

    No. Truncate doesn't affect the rowmodcnt/colmodcntrs at all.

    but since truncate is not logged...

    Logging and stats have nothing to do with each other. Besides, truncate statements are logged.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply