Re: good, thorough documentation on full text issues?
- From: Simon Sabin <SimonSabin@xxxxxxxxxxxxxxx>
- Date: Thu, 18 Jan 2007 20:29:54 +0000 (UTC)
Hello tbh,
With full text on sql 2005, you need to make sure you manage resources properly. We initially just upgraded and ran a reindex. However it didn't run much faster. However this was becasue our DB is larger than our server memory. SQL was contending with full text for resources. Fixing SQL memory resolved the issue allowing FT to operate efficiently. This resulted in huge improvements.
We have upgraded from quad 32 bit boxes to 2 xdual core 64 bit boxes and the performance has rocketed.
The biggest is that under load FT doesn't complain, no more errors saying FT couldn't find a row or one of the many other errors due to searching data during changes being indexed.
Bottom line is go for it.
One word or warning, make sure you update stats especially on date columns. in 2005 the optimiser does get estimated rowcounts from FT however this can cause it to put a FT query with a nested loop in a query plan. This can be very painful if the optimiser has got the wrong row count due to out of date statistics.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
hi,
out website is starting to get serious about switching from SQL 2000
to SQL 2005. in the process we hope to address a few weak points in
our site's fulltext searching.
i'd like to find the best possible documentation or courses to get up
to
speed on this. i have googled quite extensively and found some
detailed
information, e.g. at msdn, in newsgroups, and the two fulltext
articles
here:
http://www.simple-talk.com/author/hilary-cotter/
(which strike me as very good as far as they go.)
my question is whether there are good books, articles, or courses that
go deeper still. maybe someone has assembled a list that i haven't
found yet?
in particular i'm interested in the following issues:
- i've read that SQL 2005 promises improvements in fulltext search,
but i
haven't seen anything dramatic in my reading and testing yet [*]; (one
example: supposedly the german noise word list was improved, but my
impression is it's the same as before; this is no big deal, we will
replace
it with something like the 100 most frequent German words when we
switch to
2005 -- good breaking point)
- i'd like to learn more about language-specific issues such as how
words
are parsed and stemming is done, in particular how can i tune and test
these
i'd be grateful for any pointers.
cheers,
Tim Hanson
[*] one quote from one of the above-sited articles is intriguing:
Similarly a table can be full-text indexed in a single
catalog; or to put it another way - a table's full-text
index cannot span catalogs. However, in SQL 2005 you
can full-text index views which may reside in different
catalogs than the underlying base tables. This provides
performance benefits and allows partitioning of tables.
however i'd need to learn much more to decide whether i can benefit
from it.
.
- Follow-Ups:
- References:
- Prev by Date: Re: How to get record count only from FTS
- Next by Date: Re: page faults
- Previous by thread: Re: good, thorough documentation on full text issues?
- Next by thread: Re: good, thorough documentation on full text issues?
- Index(es):
Relevant Pages
|
