Consider the following scenario. You upgrade a package from Microsoft SQL Server 2005 to Microsoft SQL Server 2008. Then, you run a data flow task that uses the OLE DB Destination adapter. In this scenario, you experience a decrease in SQL Server performance when you run the package that was upgraded to SQL 2008. This problem occurs if the data flow task involves the following options:
Fast-load data access mode
Updates and inserts that are applied to the same table
This problem occurs because, during the package upgrade from SQL Server 2005 to SQL Server 2008, the default value for the Maximum insert commit size property of the OLE DB destination is changed from 0 to MAX_INT (2147483647).
The default value for the Maximum insert commit size property of the OLE DB destination adapter is changed in SQL Server 2008 because of a change in locking behavior in the SQL Server 2008 storage engine. If the maximum insert commit size property still used 0 as its value, the package might hit an application deadlock at execution time.If you decide to keep 0 as the Maximum insert commit size property of the OLE DB destination adapter, you may receive the following warning message:
The Maximum insert commit size property of the OLE DB destination oledbdestcomponent is set to 0. This property setting can cause the running package to stop responding. For more information, see the F1 Help topic for OLE DB Destination Editor (Connection Manager Page).