SQL Server FTS performance after adding many records

Question

We have a web application that allows clients to manage large lists of names. For searching on these lists, we use SQL Server 2008's FTS, which usually works well. Our largest client has 900,000 names and enjoys sub-second search times.

For another new client, however, I recently imported 150,000 names, and performance is terrible (as in, server-debilitatingly terrible). I checked the full-text indexer and it claims to have recently completed a crawl.

Looking at the execution plans, I notice that in the fast case (for the larger client), SQL Server does the FTS first and then does index seeks on the result. For the newer client, it does an index seek first (150,000 times, apparently, for the new records) and THEN does the FTS.

So I tried the WITH(INDEX(MyFullTextIndex)) hint, but SQL Server says that the index does not exist. Apparently it doesn't consider these FTS indexes to be "real" indexes. How can I force SQL Server to always use the FTS first?

UPDATE: I tried regenerating the statistics, to no avail. Same performance issues.

Here are the execution plans:

FAST performance: http://frameaction.com/LLExecutionPlan01.sqlplan

SLOW performance: http://frameaction.com/LLExecutionPlan18.sqlplan

Answer 1

Check the statistics on each table. The two servers may have different sets of statistics, because SQL will automatically generate statistics depending on your settings. If it's created different statistics, it will choose different query plans for the queries.

Then, make sure your statistics are updated. Info on updating your statistics for each table is here in Books Online:

http://msdn.microsoft.com/en-us/library/ms187348.aspx

I'd update with fullscan to make sure you get good data.

Finally, if neither of those works, run the query in SQL Server Management Studio, but use the "Include Actual Query Plan" option. Then right-click anywhere on the query plan and choose to Save as XML. Post the two different query plans somewhere on the web, and we'll take a look to see what the difference is and why.

Answer 2

Not sure this will help but you can try using CONTAINSTABLE - which lets you change the join order.

SELECT customer_id FROM customers WHERE CONTAINS(customername, 'Foobar')

Becomes

SELECT customer_id FROM Customers AS FTTABLE INNER JOIN CONTAINSTABLE (Customers, customername, 'Foobar') FTINDEX ON FTINDEX.[Key] = FTTABLE.customer_id

Answer 3

I tried adding the "OPTIMIZE VALUE FOR UNKNOWN" hint at the end of my query (I also had to add a dummy variable to the query to have something to reference in the hint). So far it seems to be working nicely. I'm a little nervous about adding hints, and so am still on the lookout for a better solution, but for now this is working.

Answer 4

I've never used SQL Server's FTS service, but like the first poster said, I have found that when loading data warehouses or reporting databases that certain queries that had been running fine ran very slow aftering loading a large amount of data. Running sp_updatestats after each load fixed the problem (even if the auto update statistics database option is checked (which it is by default).

Answer 5

The FTS index is a black box that sql server can only ever join it's results set to, it always searches the entire table regardless of any where clauses or inner joins. One way i've found fo increasing the performance of a FT search is by performing the join and any non parameter based where clauses within a schema bound view and searching a full text index on that view.