Re: Full Text Search Performance

From: John Kane (jt-kane_at_comcast.net)
Date: 10/24/04

  • Next message: Hilary Cotter: "Re: Filter Html tags on Full text Search" 
    Date: Sun, 24 Oct 2004 09:56:25 -0700

    You're welcome, Ali Salem,
    Without actually testing English vs. Arabic & using the Neutral wordbreaker
    (for both cases) with a 1.6+ million row table and comparing the FTS query
    performance, I would tend to agree that Arabic text alone *should* not by
    itself account for this poor query performance. However, I would tend to
    think that this poor performance is related to the size of the table as it
    is over 1 million rows where the both the FTI and FTS performance starts to
    bog down. See SQL Server 2000 BOL title "SQL Full-text Search
    Recommendations" for more information on this.

    Your FT Catalogs are actually located on the L drive (L:\Microsoft SQL
    Server\MSSQL\FTDATA\) the same drive that SQL Server system database and
    tempdb are located also increasing the Resource Usage to 5, most likely will
    only give you a 5% boost, but still it is worth a try and given your
    machine's memory configuration (8 GB of memory) you should be able to ensure
    that SQL Server has enough memory (fixed memory) and allow the MSSearch to
    use at least 512Mb (max) of available RAM. What you read is correct, and no,
    setting the resource_usage to 5 does not substitute for setting SQL Server
    memory to fixed.

    For large FT Catalogs, I generally recommend using the Top_N_Rank parameter
    to limit the resultset, but you stated that you had "other parameters in
    [your] query that can affect the returned result set and that are outside
    the FTS. What are these other parameters? Are they other columns in this
    FT-enable table or are they joins with other tables? If so, can you test
    using CONTAINSTABLE and use the Top_N_Rank value set to 2000 (but not more
    that this "special" number)? Depending upon your answers there may be other
    alternative methods to add the "other parameters" to the RANK value and
    still get better performance, but you most likely will have to use
    CONTAINSTABLE and the Top_N_Rank value to get better FTS performance over
    CONTAINS with FT-enabled tables of greater than 1 million rows.

    Regards,
    John

    "Ali Salem" <AliSalem@discussions.microsoft.com> wrote in message
    news:1D6191EA-0333-4C2A-85AB-B3762575EEBC@microsoft.com...
    >
    > Thanks a lot for you prompt answers. Below is the script result you
    > requested. The database is on drive E on the production, the fulll text
    > catalogs are on drive L, which is also on the SAN.
    >
    > Please finde below my comments on your points:
    > 1) The Arabic language columns, Yes, these columns contains arabic text.
    > However, we are using neutral for FT. Arabic word breaker can follow the
    same
    > rules as english one, and I am not sure this might cause i problem in
    > performance, I have some doubts about this. the only think that i think is
    > important here is that arabic uses unicode here, so text size is twice!
    Also,
    > I am not getting arabic seach slower than english in a reasonable way, I
    can
    > understand a loss of 5-10% in performance for such a thing but not to get
    a
    > system that is 50 times slower for the simplest query that does not have
    much
    > linguistic as this is not being apllie don arabic by sql FT. The query
    that
    > should perform in sub second, is taking 30 or 40 seconds this is too much
    to
    > the point that I cannot understand it.
    >
    > 2) The drive letter that is used by the database is E, The FT catalgo are
    on
    > another SAN Partition and that is frive E. I think this should make
    > performance even better.
    >
    >
    > 3) Resource Usage, I can try to set to level 5, but how much difference
    this
    > will show, I mean can it be the reason for such a poor query performance!
    or
    > it should improve the performance by 10% or so?
    >
    > Also, I have read something about configuring SQL to use a fixed memory on
    > the server and leave at max 512 MB for mssearch, does setting the resource
    > usage flag to 5 substitutes the fixed sql server memory idea? or it is
    > something that i need to do in adition to that?
    >
    > Thanks a lot for you help and support. i really appreciate it.
    >
    > thank you,
    > Ali Salem
    > name
    > db_size owner
    >
    > dbid created status
    >
    >
    >
    >
    >
    >
    >
    > compatibility_level
    > --------------------------------------------------------------------------
    ------------------------------------------------------
    > -------------
    > --------------------------------------------------------------------------
    ------------------------------------------------------
    > ------ -----------
    > --------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ---------------------------------------------------------------------- -----
    --------------
    > testdb
    > 148399.88 MB
    > tstdmn\sysadmin
    > 19 Oct 14 2004
    > Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
    > Recovery=BULK_LOGGED, Version=539, Collation=Arabic_CI_AS, SQLSortOrder=0,
    > IsTornPageDetectionEnabled, IsAutoCreateStatistics,
    IsAutoUpdateStatistics,
    > IsFullTextEnabled
    >
    >
    >
    >
    80
    >
    >
    > name
    > fileid filename
    >
    >
    >
    > filegroup
    > size
    > maxsize growth usage
    > --------------------------------------------------------------------------
    ------------------------------------------------------
    > ------
    > --------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    ----------------------------------
    > --------------------------------------------------------------------------
    ------------------------------------------------------
    > ------------------ ------------------ ------------------ ---------
    > testdbSYS
    > 1
    > E:\E_DataBase\testdbSYS_Data.MDF
    >
    >
    > PRIMARY
    >
    > 10112 KB Unlimited 10% data only
    > testdb_Log
    > 2
    > E:\E_DataBase\testdb_Log.LDF
    >
    >
    > NULL
    >
    > 51200000 KB Unlimited 10% log only
    > testdb1
    > 3
    > E:\E_DataBase\testdb_INDEXES_Data.NDF
    >
    >
    > SECONDARY1
    >
    > 9216000 KB Unlimited 10% data only
    > testdb2
    > 4
    > E:\E_DataBase\testdb_MAIN_Data.NDF
    >
    >
    > SECONDARY2
    >
    > 40960000 KB Unlimited 10% data only
    > testdb3
    > 5
    > E:\E_DataBase\testdb_IMAGES_Data.NDF
    >
    >
    > SECONDARY3
    >
    > 39424000 KB Unlimited 10% data only
    > testdb4
    > 6
    > E:\E_DataBase\testdb_ALL_Data.NDF
    >
    >
    > SECONDARY4
    >
    > 11151360 KB Unlimited 10% data only
    >
    >

  • Next message: Hilary Cotter: "Re: Filter Html tags on Full text Search"

    Relevant Pages

    • Re: FTS Performance in SQL 2005
      ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... Can you post you query plans and the output of statistics IO ... SQL Server MVP ... cost relative to the whole batch, ...
      (microsoft.public.sqlserver.fulltext)
    • Re: How to clear memory usage after executing queries ?
      ... The second query will likely pick up a lot of its data pages in cache, ... SQL Server loves memory and the more you ... Yes, I would like to keep the data in cache for subsequent queries, but I ...
      (microsoft.public.sqlserver.programming)
    • Re: Full Text Search Performance
      ... You're welcome, Ali, ... Beta2 release for MSDN subscribers) as the new version of SQL Server has ... No, this is not normal query for a table of this size, but the number of ... > about resource usage, memory and drives...etc. ...
      (microsoft.public.sqlserver.fulltext)
    • Re: Memory Usage/Hog
      ... > Secondly, without having more detail information about my application, it would be difficult to ... There are several ways a query plan can be re-used. ... > that the OS uses to ask SQL Server for the memory back. ...
      (microsoft.public.sqlserver.server)
    • Wrong data returned by full text query
      ... My full text queries on a columns containing japanese text return strange ... SQL Server 2000 english with Japanese collation. ... query returns records which does not include the word "money". ...
      (microsoft.public.sqlserver.fulltext)
    Loading