0

I've got a reasonably large MSSQL database with a table of about 6 million rows and around 50k inserts daily. One of the things I'm starting to struggle with is fulltext search, as I've a requirement for one of the columns in the table to be fulltext indexed.

I've initially gone with MSSQL 2008 R2 built-in fulltext running on the same host as the database engine, but I've recently run into performance and reliability issues. I often see the CPU pegged at 100% and assume that this is the fulltext index running a population. I also see my frontend return innacurate or not up-to-date data in the results of a search which uses the fulltext index.

What are my options? I'm dimly aware that it might be possible to run the fulltext processing on a separate server, but I'm not sure how to implement that or whether I'd want to. I'm also aware of other options like Lucene.NET, but don't know if this would be the right approach to take.

My requirements are simply the ability for fulltext searches on this table to be as up-to-date as possible, bearing in mind the insert rate on the database.

What would people suggest?

Improve this question

1 Answer 1

Reset to default
1

I've got a reasonably large MSSQL database with a table of about 6 million rows and around 50k inserts daily.

A.K.A. tiny and not busy.

I often see the CPU pegged at 100% and assume that this is the fulltext index running a population. I also see my frontend return innacurate or not up-to-date data in the results of a search which uses the fulltext index.

What CPU do you have? Get a better one. Also the full text index will always lag - it is not real time. Even with not pegging the CPU it WILL fall behind. Because it is not synchroneus by design.

We need more infromation. I would at this point suggest getting a database server that is not in the league of a 100 USD ebay used part. YOu dont say anything how it looks, but unless you insert 2gb files the CPU should not fall behind SIGNIFICANTLY (and a small delay is the trade off for having fast inserts - alternatively use Lucene .NET and wait for the full text index processing to finish before inserting, which will not make you happy either, as inserts may take some time).

In general, a modern server (that is 6-12 real processor cores) should totally keep up with 50k inserts per day unless they happen not in a day but in a 10 minute period or so. Naturally dependso n the size (50.000 2gb text blobs are a lot).

Improve this answer
2
  • Size is relative :) Anyhoo, the server's an IBM HS21 blade, with 2x Xeon E5345s and 12GB RAM. Storage is provided over iSCSI to a Solaris ZFS box with 12x7.2k SATA drives. It's not a massive amount of data, so I wouldn't have expected the fulltext to lag like it does. If I did want a solution that provided as-realtime-as-possible fulltext indexing, would I be better off sticking with and troubleshooting MSSQL's FT, or looking at a different approach?
    – growse
    May 17, 2011 at 8:04
  • Depends. The server sounds ok, I would check data file latency... if that is goof, it definitely is good.The advangage of a Lucene.Net solution is that at the end you can put it into a SEPARATE server, increasing your processing budget quite a bit. If you turn off full text indexing... is the CPU usage ok? Also, does it catch up? If there IS a big backlog (created full text index) then it willtake time to create the index... the main question is what happens when the index is "current" with the updates...
    – TomTom
    May 17, 2011 at 10:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .