SQL Server 2008 performance decreases when you use a dataflow task that involves a fast load

SYMPTOMS
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

CAUSE
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).
MORE INFORMATION
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).
STATUS
This behavior is by design.
Properties

Article ID: 955717 - Last Review: 08/06/2008 19:29:52 - Revision: 1.1

Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Developer

  • kbtshoot kbprb KB955717
Feedback