April 12, 2012 at 8:52 am
Hi All,
We are in the middle of migrating our data warehouse database 2005 to 2008. We have a couple of stored procedures that inserts around 50.000 records into a table that have a few foreign key constraints. What we have noticed is that the insert operation takes a lot longer in 2008 than it is 2005. It turns out that foreign keys are the root of the problem. To verify all foreign keys 2008, presumably does some additional checks or does it completely differently so that exclusive locks are generated for the table that data inserted into and CPU shoots up for a long time period.
When we disable all the foreign keys during the proc execution, it completes all operation about 5 minutes. With foreign keys are on it takes about 1 hour to complete the same operation.
It used to take 10 mins max to process the same procedure in SQL 2005.
I was wondering whether other people experienced the same sort of issues with 2008 and other than the work around that I mentioned above, is there any other solutions to this?
Perhaps some sort of setting is available in 2008?
Thanks
Enis
April 13, 2012 at 9:37 am
Hi All,
Has anyone have idea about this please?
Thanks
Enis
April 19, 2012 at 4:17 pm
How did you migrate the database? If you did a backup/restore or detach/attach did you update usage stats and rebuild all your indexes after the migration?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2012 at 2:21 am
Hi,
Thank you for your reply.
It was a backup and restore and part of the migration plan, we've updated stats and rebuilt indexes. This is infact a puzzling bit. It could be that we have other issues with the server perhaps. I can't find any article/post/blog about this. :ermm:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply