How can I improve the performance of updating an SSIS 2008 package that works great in 2005?
I am migrating my datastore from SQL Server 2005
to . There is a significant performance degradation when the table is updated. The inserts work great. SQL Server 2008
I am using the same SSIS package in both environments, but 2008 still does not update correctly.
I ran full statistics on all tables. The process uses a temporary table. I reset all of the indexes (except the one required for the update), but none of these measures helped. I also wrote an upgrade guide that mimics SSIS and is fast as expected.
The update process uses a data flow task (there are other things in the task, like inserting into a table processed
to see what data was used in the update).
This is a new database where nothing works. Any suggestions?
Captured IO statistics
- 2005, CPU = 0, Reads = 150
- 2008, CPU = 1700, Read = 33,000
Database RAM:
- 2005, 40GB Total / 18 Sql Server
- 2008, 128GB Total / 110GB Sql Server
source to share
An issue was discovered in the execution plan. The plan in 2008 was to use different tables to create the update statement. Background: Because we are using indexed views that do not allow any other access when querying those tables, we built smaller / more compact tables that iViews use, not our dimensions, so that they are available to users. The optimizer selected these tables, not the ones we specified in the query.
When I originally did the explain plans, I was using the wrong query that didn't have this feature. It mattered.
Thank!
source to share