FUll Text Index Very slow

  • mbassem91

    SSC Enthusiast

    Points: 172

    I am using full text indexing for partial match and Today it started to be very slow when I use the following queries

    It takes more than 1 min to return the results.

    SELECT *

    FROM

    [Syn].[CustomerNamesSyn] AS Customer WITH(NOLOCK)

    WHERE

    CONTAINS(Customer.CustomerName, ‘ ”any input string *” ’)

     

    The full text index is set on CustomerName column

    I was using same query for more than a year and has not experienced such slowness before.

    What could have went wrong?

  • Sue_H

    SSC Guru

    Points: 90701

    To start, you would probably want to check the full text log. That would list the last crawl and any errors that may have occurred. You would also want to do a quick check of sys.fulltext_indexes to see if the crawl completed, start and end times.

    Sue

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 23078

    Did you look at the execution plan to ensure that SQL was using the full text index?

    a nice read on CONTAINS vs CONTAINSTABLE is here:

    http://www.qdpma.com/CBO/FullTextQueryPlans.html

    If it IS being used, my next thought is that you are not doing maintenance on the full text index and it is getting fragmented and causing performance issues.

    My third thought is that your query is actually part of a stored procedure and that '"any input string *"' is an input to that stored procedure and you are being hit with parameter sniffing.

    Microsoft also has some tips on how to improve query performance with full text queries:

    https://docs.microsoft.com/en-us/sql/relational-databases/search/improve-the-performance-of-full-text-queries?view=sql-server-2017

    A small thing that makes me a bit nervous with your query is that you have "WITH(NOLOCK)" in there.  There are risks with using NOLOCK and in general, it is advised to avoid it where possible.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply