Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert Into Using OpenQuery Causes Massive Fragmentation Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 2:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 8:48 AM
Points: 149, Visits: 329
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.
Post #1345020
Posted Tuesday, August 14, 2012 3:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:59 AM
Points: 990, Visits: 2,217

Could it be that data that comes from OPENQUERY is in completely different Order then when you copy table to table?
Post #1345067
Posted Wednesday, August 15, 2012 2:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 3,853, Visits: 4,991
Please bear in mind that there is no guaranteed ORDER BY in any RDBMS.
Consider adding an ORDER BY (outside the OPENQUERY function) on the data source column equivalent of your clustered index, if at all possible
e.g.
INSERT MyLocalTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 FROM OPENQUERY(MyLinkedServer,'SELECT Col1,Col2,Col3 FROM RemoteTable') ORDER BY Col1



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1345156
Posted Wednesday, August 15, 2012 12:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 8:48 AM
Points: 149, Visits: 329
I have no clustered index on the table. Will the order of the copy from OpenQuery affect fragmentation even without a clustered index?
Post #1345477
Posted Wednesday, August 15, 2012 12:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 8:48 AM
Points: 149, Visits: 329
Inserted an ORDER BY on the non-clustered indexes. Fragmentation improved a little bit, but still looking at 50%-90% per index
Post #1345479
Posted Wednesday, August 15, 2012 5:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:17 PM
Points: 36,715, Visits: 31,165
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1345610
Posted Thursday, August 16, 2012 1:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 3,853, Visits: 4,991
guerillaunit (8/15/2012)
I have no clustered index on the table. Will the order of the copy from OpenQuery affect fragmentation even without a clustered index?

Thiis should not cause fragmentation.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1345706
Posted Thursday, August 16, 2012 8:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #1345999
Posted Thursday, August 16, 2012 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 8:48 AM
Points: 149, Visits: 329
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
Post #1346070
Posted Thursday, August 16, 2012 10:04 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #1346096
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse