Microsoft KB Archive/937086

= BUG: The full-text crawl takes a long time to finish when you create a full-text index on a column that has the varchar data type in SQL Server 2005 =

Article ID: 937086

Article Last Modified on 8/28/2007

-

APPLIES TO


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

-



SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2005, a table has a primary key constraint. The primary key constraint contains a column that has the varchar data type. You create a full-text index on the column. In this scenario, the full-text crawl takes a long time to finish. Additionally, the CPU usage is high during the full-text crawl.



CAUSE
This problem occurs because the sp_fulltext_getdata stored procedure converts values of the varchar data type to values of the nvarchar data type. The conversion operation causes a table scan.



WORKAROUND
To work around this problem, use one of the following methods.

Method 1
Change the data type of the column in the primary key constraint to the nvarchar data type.

After you use this method, the query optimizer does not add the conversion operation to the execution plan. Additionally, the table scan does not occur.

Method 2
Follow these steps:
 * 1) Add a new column that has the bigint data type.
 * 2) Set the Identity property of this column to the Identity(1,1) value.
 * 3) Delete the primary key constraint on the column that has the varchar data type.
 * 4) Define a primary key constraint on the column that has the bigint data type.
 * 5) On the column that has the varchar data type, create nonclustered indexes that are based on the need to improve the query performance.



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



MORE INFORMATION
The sp_fulltext_getdata stored procedure is an internal stored procedure that copies the values of the column in the primary key constraint into the shared memory. Then, the Microsoft Full-Text Engine Filter Daemon process (Msftefd.exe) starts to create the full-text index.

When the sp_fulltext_getdata stored procedure copies a value, the sp_fulltext_getdata stored procedure converts a value of the varchar data type to a value of the nvarchar data type. The conversion operation takes time, and the conversion operation causes high CPU usage.

When this problem occurs, you can use SQL Server Profiler to capture the RPC:Completed event class and the SP:StmtCompleted event class to obtain the page reads. The sp_fulltext_getdata stored procedure performs many page reads. The number of page reads is close to the number of pages of the table on which the full-text index is created. You can run the DBCC CHECKTABLE statement to obtain the number of pages of the table.

