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


The information in this article 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
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 "Applies to" section.

REFERENCES

For more information, see the following topics in SQL Server 2005 Books Online:
  • Displaying graphical execution plans (SQL Server Management Studio)
  • Graphical execution plan icons (SQL Server Management Studio)
  • Full-text indexes
  • Select Change Tracking (Full-Text Indexing Wizard)
  • Full-text catalog properties (Tables/Views page)
Modification Type:MajorLast Reviewed:4/11/2006
Keywords:kbtshoot kbExpertiseAdvanced kbsql2005fts kbprb KB916785 kbAudITPRO kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.