Re: Fulltext search too slow
- From: DC <dc@xxxxxxxxx>
- Date: Fri, 03 Aug 2007 06:18:51 -0700
On 3 Aug., 13:36, "Daniel Crichton" <msn...@xxxxxxxxxxxxxxxx> wrote:
DC wrote on Fri, 03 Aug 2007 02:54:26 -0700:
Thank you Dan, I watched the execution plan which looks innocent (98%
remote scan, 2% stream aggregate, 0% compute scalar). Is this the way
to detect parallelism or do I have to look somewhere else? I think
MAXDOP does not apply since we are using 2005 standart edition.
MAXDOP applies whenever multiple processors are involved. I've only got 2005
Workgroup Edition (one down from Standard) and I occassionally see
parallelism in queries on my two processor server (two physical Xeon HT
CPUs, so 4 logical processors that SQL Server sees). If you don't see a
parallel component in the query plan, then for that particular plan there is
no parallel processing going on. However, that's not to say that it won't if
circumstances change slightly.
The extremely varying latencies of the fulltext searches do only in
part correlate with the state of caching and the number of returned
hits. Within 10 minutes the same expression that returns about 10.000
hits will vary in latency between 1 and 20 seconds (and it is not
getting faster with later tries, only the first search is quiet sure
quiet slow). So I think there are other limiting factors, and since it
is not CPU or disk queues I am having high hopes about changing the
memory configuration as Simon proposed.
Memory well may improve things. My own server has 2GB RAM, of which 1.2 is
available to SQL Server. You also have to consider that some RAM will be
needed for the FTS service. You also might want to look at adjusting the FTS
resource setting if you're going to be handling lots of results. For
instance, look athttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesl...
(near the end, under "Other configurations".
The Workgroup edition is limited to 2GB RAM, and as I'm only running Windows
2003 Standard edition I'm pretty much near the limits of what I can achieve
on my server. However, so far it's proved to be more than capable of serving
my customers, so I've no interest in scaling up just yet.
Dan
Thank you for the interesting read about the lessons Microsoft
learned. Although I think they have not learned the one lesson: how to
build a really good ft engine ;)
They actually advice the poor customer in that article to set the ft
timeout to a higher value than the default 600 seconds. I know what
that means when I read it: they didn't know what to do or say. Who
waits 5 minutes for a search result?
When the optimized memory config does not help in my application, I
will have to start using http://www.quest.com/sql-turbo/ again, which
was lighting fast in comparison but used extended stored procedures
(in the version I used) and was poorly supported and a bit buggy
(sometimes the search failed and didn't start working again unless the
server was rebooted).
Regards
DC
.
- References:
- Fulltext search too slow
- From: DC
- Re: Fulltext search too slow
- From: Daniel Crichton
- Re: Fulltext search too slow
- From: DC
- Re: Fulltext search too slow
- From: Daniel Crichton
- Re: Fulltext search too slow
- From: DC
- Re: Fulltext search too slow
- From: Daniel Crichton
- Fulltext search too slow
- Prev by Date: Re: Fulltext search too slow
- Next by Date: Re: Fulltext search too slow
- Previous by thread: Re: Fulltext search too slow
- Index(es):
Relevant Pages
|
Loading
