Post reply

Insert Into Using OpenQuery Causes Massive Fragmentation

  • August 14, 2012 at 2:22 pm

    #279681

    I use the following code to import a table from an Oracle DB to SQL Server every day:

    TRUNCATE TABLE mytable

    INSERT INTO mytable (firstname, lastname, city, state)

    SELECT * FROM OPENQUERY (mylinkedserver, 'SELECT firstname, lastname,

    city, state FROM mylinkedtable')

    The above causes massive fragmentation (90% to 99%) on my nonclustered indexes on "mytable". If I run the below code

    SELECT * INTO mytesttable

    FROM mytable

    TRUNCATE TABLE mytable

    INSERT INTO mytable

    SELECT * FROM mytesttable

    DROP TABLE mytesttable

    I end up with fragmentation of between 1% and 3% on my nonclustered indexes. What is causing this? Both statements are truncating the table and inserting new data into a blank table.

  • August 14, 2012 at 3:54 pm

    #1525826

    Could it be that data that comes from OPENQUERY is in completely different Order then when you copy table to table?

    --Vadim R.

  • August 15, 2012 at 2:11 am

    This was removed by the editor as SPAM

  • August 15, 2012 at 12:49 pm

    #1526171

    I have no clustered index on the table. Will the order of the copy from OpenQuery affect fragmentation even without a clustered index?

  • August 15, 2012 at 12:52 pm

    #1526173

    Inserted an ORDER BY on the non-clustered indexes. Fragmentation improved a little bit, but still looking at 50%-90% per index

  • August 15, 2012 at 5:05 pm

    #1526291

    Based on the fragmentation you experienced with OPENQUERY, I truly wonder if OPENQUERY is set-based or some form of "Hidden RBAR". The order of data on a single insert shouldn't matter to the indexes. There shouldn't be any fragmentation on a freshly truncated table that has only had a single insert applied to it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 16, 2012 at 1:43 am

    This was removed by the editor as SPAM

  • August 16, 2012 at 8:22 am

    #1526615

    Since you are truncating the table first and then inserting rows, it is SIGNIFICANTLY better to drop the nonclustered indexes, insert the data, and then recreate the nonclustered indexes. Better performance, less logging, less fragmentation.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • August 16, 2012 at 9:37 am

    #1526678

    Hi Kevin,

    Is there a reason why you would need to drop the nonclustered indexes after truncating a table and before inserting new data to prevent fragmentation? I am under the impression that inserting data into a just truncated table with nonclustered indexes would not cause fragmentation

  • August 16, 2012 at 10:04 am

    #1526699

    guerillaunit (8/16/2012)

    Hi Kevin,

    Is there a reason why you would need to drop the nonclustered indexes after truncating a table and before inserting new data to prevent fragmentation? I am under the impression that inserting data into a just truncated table with nonclustered indexes would not cause fragmentation

    What if the data comes in in "random" order?? Seems logical in that case that you would get (potentially massive, like you see) fragmentation, right?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • August 16, 2012 at 11:45 am

    #1526754

    guerillaunit (8/16/2012)

    Hi Kevin,

    Is there a reason why you would need to drop the nonclustered indexes after truncating a table and before inserting new data to prevent fragmentation? I am under the impression that inserting data into a just truncated table with nonclustered indexes would not cause fragmentation

    Take a step back and ask yourself why an index becomes fragmented. The answers are all there if you just think about it. Also, remember that an index with few pages will always show a certain amount of fragmentation just by the nature of how it is determined.

    Jared
    CE - Microsoft

Viewing 11 posts - 1 through 10 (of 10 total)

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