Re: Full Text Search Performance

From: Ali Salem (AliSalem_at_discussions.microsoft.com)
Date: 10/21/04

  • Next message: WalrusWal: "Inconsistent Search Results" 
    Date: Thu, 21 Oct 2004 04:29:03 -0700

    thanks for your reply.

    I cannot limit the search in this way, as there are other parameters in my
    query that can affect the returned result set and that are ourside the FTS.

    I am asking for any figures about FTS performance? What should I expect from
    it? Is this behavior am getting normal for the data size or, I am having
    something wrong

    "Hilary Cotter" wrote:

    > That depends. SQL FTS performance is most sensitive to the number of rows
    > you are returning. You should limit your results set as much as possible - I
    > think you will find that the practical limit for most applications is around
    > 100-200 rows.
    >
    > Limit it by using the the top_n_by_rank operator in ContainsTable or
    > FreeTextTable, ie
    >
    > USE Northwind
    > GO
    > SELECT FT_TBL.Description,
    > FT_TBL.CategoryName,
    > KEY_TBL.RANK
    > FROM Categories AS FT_TBL INNER JOIN
    > CONTAINSTABLE (Categories, Description,
    > 'test',200
    > ) AS KEY_TBL
    > ON FT_TBL.CategoryID = KEY_TBL.[KEY]
    > ORDER BY KEY_TBL.RANK DESC
    > "Ali Salem" <AliSalem@discussions.microsoft.com> wrote in message
    > news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@microsoft.com...
    > > Hello,
    > >
    > > I have full text search enabled on two large tables, the first one has 3
    > > million records (FT index column length is around 512-1025 char), and the
    > > other one has around 1.6 records (FT index column length is around 50-120
    > > char)
    > >
    > > The simplest query that uses the smiplest form of FT on the first table FT
    > > index search needs 23-30 seconds to complete and from 10-15 seconds on the
    > > seconds table as it the indexed column is smaller.
    > >
    > > SQL server is deployed on 4 P servers with 8 GB of memory and attached to
    > a
    > > very powerful SAN system. Memory usage on the server is not very high so I
    > > believe there is enough memory for the mssearch service to use.
    > >
    > > I just wonder if this is the normal throughput of the Full Text search of
    > > SQL Server. If not, I will appreciate any tips and hints that might be the
    > > reason of the system.
    > > --
    > > Ali Salem
    >
    >
    >

  • Next message: WalrusWal: "Inconsistent Search Results"

    Relevant Pages

    • Re: FTS query performance on SQL 2005
      ... caching mechanism not only for SQL FTS, ... Issue this query ... Looking for a SQL Server replication book? ... the memory usage was steadily increasing. ...
      (microsoft.public.sqlserver.fulltext)
    • Re: How to clear memory usage after executing queries ?
      ... got scared when the memory usage reached 1.7 gig (the machine has 2 gig of ... select distinctfrom packet, then do query ... > Columnist, SQL Server Professional ... > The only way to clear up the memory usage is by stopping SQL Server Service ...
      (microsoft.public.sqlserver.programming)
    • Re: How to clear memory usage after executing queries ?
      ... So even if the SQL Server memory usage reached 1.7 gig (the machine has 2 ... > the first query put them there. ...
      (microsoft.public.sqlserver.programming)
    • How to clear memory usage after executing queries ?
      ... We have a SQL Server 2000 database that has about 10 million records. ... found this index execute the query that I want the fastest. ... The only way to clear up the memory usage is by stopping SQL Server Service ...
      (microsoft.public.sqlserver.programming)
    • Re: Full Text Search Performance
      ... SQL FTS performance is most sensitive to the number of rows ... > very powerful SAN system. ... Memory usage on the server is not very high so I ... > SQL Server. ...
      (microsoft.public.sqlserver.fulltext)