August 17, 2012 at 7:47 am
We did an inplace upgradeof Production database from sql server 2005 to SQL 2008 R2 with same configured Windows server. After upgraded bulk insert is taking longer. We update stats and rebuild index every day on this specific table.
From application in 2005 it was taking 22 mins to bulkinsert 10,000 Items, but in 2008 its taking 39 mins. While insert is running I am not able to select anything from the database.
Thanks
Dev.
August 17, 2012 at 7:52 am
I just did a select * from table in 2005 and 2008.
2005 - it took 35 mins for 16 million rows
2008- took 48 Mins for 16 Million Rows.
I see comptability level is set to 100 in 2008. Will changing this to 90 help?
August 17, 2012 at 2:46 pm
I've seen performance issues come up because of some trace flags that were set or acted differently between versions, e.g. 4199 and 4135. You might try playing around with those. Do you have a test server you can use instead of production?
August 17, 2012 at 3:09 pm
22 minutes for 10,000 rows? You have another problem somewhere. That should be closer to 22 seconds for 10,000 rows.
How big are the rows? Is there a BLOB involved? How many indexes on the table? Is there a trigger on the table?
What's the batch size on the bulk insert? Is there a lot of blocking going on while this is running?
August 17, 2012 at 3:11 pm
Do you see table locks occurring, is that why you can't read from the table when the insert is happening?
August 21, 2012 at 2:53 pm
Hi David,
Sorry that was a tupo, its not 10K items , its 100K items. Considering the fact that we have 16MM records and 33 index on the item table.
Rows have 72 columns. No blob involved. 32 index, 2 delete triggers, 10,000 batch Size. Yes there are lot of blocking going while this is running for any process that uses this table
HI,
Yes we do see table level lock. Other process gets locked when this is running. Insert bulk for 100K rows runs in batches og 10,000.
August 21, 2012 at 2:55 pm
Have you tried deleting all the indexes right before the bulk load and recreating them afterwards? That probably wouldn't explain the difference but it should speed things up substantially.
August 21, 2012 at 3:22 pm
Do you really need that many indexes? I've seen scenarios where that was appropriate, but they were few and far between.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply