locked
Full-text index is not being used RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I've created a full-text index on [comment] column (which is of type nvarchar(max)) in OrgData table. I have also clicked "Start Full Population" many times and it completed successfully ... well, I'm not sure if it completed successfully but I did not get any error, and this is the full-text index properties window for OrgData table ...I can't see anything wrong in it:

    The problem is that when I run at the same time two queries that retrieve just the [comment] column (the one where the full-text index is defined on), the query first uses" where like" and the other uses "where contains". The one with the "where contains" got the higher "Query cost" percentage, 51%, when I thought it will be 0% expecting it will use the full-text index!

    The strange thing is that if I run the "where contains" query alone it finishes in 00:00:00 seconds, however the one with "where like" (which has lower query cost percentage 49%) finishes in five seconds !!!!

    Someone explains this to me please. And why FullTextMatch cost is 0% and the Clustered Index Scan cost is 96% (in the second figure)?! what should i do to make it hit the full-text index? is there anything wrong in my full-text index ?

    Tuesday, March 18, 2014 7:32 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    The costs are useful, but the Plan Explorer still does a better job of showing you the details.  

    I also make use of the statistics, but am aware that statistics and reality do not always match.  For one thing, both Scalar functions and Multi-Statement Table-Valued functions (which are not "inline") are not reflecting their true cost.  This has been true since those features were introduced. 

    See a proposed change at: https://connect.microsoft.com/SQLServer/feedback/details/273443/the-scalar-expression-function-would-speed-performance-while-keeping-the-benefits-of-functions

    I see you have a Table-valued function, but I do not know if it is multi-statement or inline. Once you know where the hidden overhead might be, then you can assess things further.  So, please do not dispair, but just be aware of the possibility that there may be hidden details.

    RLF

    • Proposed as answer by Elvis Long Monday, March 31, 2014 8:44 AM
    • Marked as answer by Fanny Liu Friday, April 4, 2014 7:59 AM
    Friday, March 21, 2014 3:51 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    The full-text index will only be used if you use any of the full-text functions: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE. It wlil never be used with LIKE.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 18, 2014 8:07 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    yes I know! ... and this is what i did, if you read all my question!....

    I asked why the query with CONTAINS (which should use the full-text index) has higher query cost percentage than when using LIKE (which I know that it does not use full-text index, and that's why I've created a full-text index)...

    please read the whole question post before you kindly answer ... thanks anyways  

     
    Tuesday, March 18, 2014 8:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The Query Costs are not alway reliable, so don't be too surprised.  I have had query plans where one query claimed to need almost no resources, but by the clock ran much slower.  Actual clock time is a better way to measure performance, though you would need to run it repeatedly to get an average.

    Also, for examining Execution Plans you might want to download and use SQL Sentry Plan Explorer which offers some help in analyzing the plans.

    The LIKE '%goal%' must scan the whole column looking for this string.  It is impossible to seek to the correct rows, since the string is embedded.

    The CONTAINS() looks up the prebuilt word index and can return your word search more efficiently.

    RLF

     

    Tuesday, March 18, 2014 3:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks SQLWork, however even if SQL Sentry Plan Explorer show some more useful information yet the query Plan Diagram is the same! .... I tried to UPDATE STATISTICS OrgData, but it remained the same

    And even if this assured to me that the full-text index is being used (as the duration and CPU is much less with CONTAINS) I started doubting the execution plan! when me and most of the people use it in query optimization, especially in much more complex queries... I mean the queries in this question are too simple that the "Actual clock time" is enough as you said... in other queries _you know_ we look to the execution plan to find the areas where we need to improve.... so what to do ?!    .... saying "The Query Costs are not alway reliable" removes an essential instrument from our optimization tool box!

    Wednesday, March 19, 2014 5:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The costs are useful, but the Plan Explorer still does a better job of showing you the details.  

    I also make use of the statistics, but am aware that statistics and reality do not always match.  For one thing, both Scalar functions and Multi-Statement Table-Valued functions (which are not "inline") are not reflecting their true cost.  This has been true since those features were introduced. 

    See a proposed change at: https://connect.microsoft.com/SQLServer/feedback/details/273443/the-scalar-expression-function-would-speed-performance-while-keeping-the-benefits-of-functions

    I see you have a Table-valued function, but I do not know if it is multi-statement or inline. Once you know where the hidden overhead might be, then you can assess things further.  So, please do not dispair, but just be aware of the possibility that there may be hidden details.

    RLF

    • Proposed as answer by Elvis Long Monday, March 31, 2014 8:44 AM
    • Marked as answer by Fanny Liu Friday, April 4, 2014 7:59 AM
    Friday, March 21, 2014 3:51 PM