Need help with full-text performance for large database

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Phil Sherrod (phil.sherrod_at_REMOVETHISsandh.com)
Date: 06/12/04 

Date: Sat, 12 Jun 2004 16:19:11 GMT

We are having significant performance problems with full-text indexing on one
column of a large database.

System configuration:
  Windows 2000 Advanced Server with all service packs as of 6/10/04.
  We are using SQL 7 with all service packs as of 6/10/04.
  The CPU is a 2 GHz Athlon.
  The system has 1.5 G of RAM.
  We are using two 7200 RPM IDE disks in a Raid 0 configuration. All of the
  SQL databases and FT catalogs are on these disks.

Database:
  One column is indexed. The items in the column tend to be about 60
  characters long with approximately 10 words.
  The indexed table has a change-date-time column.
  The table currently has about 2,600,000 rows, but about 200,000 rows are
  added each day.
  We expect the table to grow to about 6,000,000 rows and then stabilize with
  about 200k rows added & deleted daily.

Searches are being directed to the database through our application which
fields Internet search requests from tens of thousands of clients around the
world. Fortunately, clients are not making continuous requests, so we average
about 20 searches per minute (we would probably have a higher search rate if we
could respond faster).

Another program running on the same computer as the SQL server is adding
records to the database pretty much non-stop during the day. On average, about
200k new rows are added per day. This program is also doing a moderate amount
of I/O to a series of sequential files that hold the messages being added.

A typical search will find between 10 and 500 matches whose results must be
returned to the client. The application that is fielding the search requests
and sending the replies is running on a separate computer. It is
multi-threaded and has no problems keeping up with the incoming requests and
outgoing results -- the bootleneck is the actual search.

We are struggling to keep up with the searches. The searches commonly involve
a number of words with the AND operator and sometimes also OR. While some
simple searches are completed in a few seconds, the typical time for a search
is in the range of 6 seconds. Some searches timeout after 40 seconds which is
what we have set as the command timeout on the SQL query. The application that
handles the requests is multi-threaded and can present multiple searches to the
SQL server simultaneously. We often see 5 threads presenting simultaneous
search requests.

An incremental indexing run last night took 7 hours to handle the new 200k
records added yesterday. We have found that if we attempt to do the
incremental index during the busy part of the day, it totally clobbers
searches, and we end up with many searches timing out after 40 seconds. So we
are doing one incremental index per day which starts at 3 AM. Would change
tracking be better or worse than incremental indexing?

The bottleneck seems to be disk I/O on the SQL server. The CPU is averaging
about 20% with short bursts to 100%.

What can we do?

We could upgrade the CPU to a 3 GHz, but with the average CPU utilization at
20% it doesn't look like that would help.

We are running Raid 0 on our two disks, so it is not clear what to do to boost
their performance significantly.

Perhaps SQL 7 full-text indexing just isn't up to this job and we should look
for some better indexing software.

I am open to any suggestions. Thank you.

Phil Sherrod

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)