Microsoft KB Archive/916785

= The INSERT operation is very slow when you try to insert many rows into a table that contains a full-text index =

Article ID: 916785

Article Last Modified on 4/11/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



Bug #: 412873 (SQLBUDT)



SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2005, you try to insert many rows into an empty table. The empty table contains a full-text index that is built on a column. The index is maintained by using change tracking. In this scenario, you may notice the following:
 * The INSERT operation is very slow.
 * The speed at which the rows are inserted severely decreases. This behavior occurs when you increase the number of rows in the operation.



CAUSE
This problem occurs because an incorrect execution plan is generated for the operation. Because the table is empty, the Clustered Index Scan execution plan is used instead of the Clustered Index Seek execution plan. The SQL Server query optimizer does not consider the increase in the table size during the execution. Therefore, the overall performance becomes very slow when many rows are inserted.



WORKAROUND
To work around this problem, you can use one of the following methods:
 * Use fewer rows in the INSERT operation. You can separate the rows into more batches.
 * Set the Change Tracking property of the full-text index to Manual. To apply the changes that occur to the underlying data to the full-text index, you must start or schedule this process manually.
 * Build the full-text index after you complete the INSERT operation.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

