Re: Fulltext search too slow

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 at http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx
(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


.

Relevant Pages

  • Re: Fulltext search too slow
    ... MAXDOP does not apply since we are using 2005 standart edition. ... parallelism in queries on my two processor server (two physical Xeon HT ... so 4 logical processors that SQL Server sees). ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Slow UPDATE and DELETE on SQL Server 2000
    ... The two systems are SQL Server 2000; one with SP3a and one without, ... What is the sp_configure cost threashold for parallelism and max parallelism ... Another thing that can mess with the execution plans is the statistics. ... is AUTOCREATE and AUTOUPDATE set on both databases? ...
    (microsoft.public.sqlserver.server)
  • Re: SQL handling of parallell querying on Windows 2000 with XEON processor
    ... I am not from Microsoft, but I would configure SQL-Server to only use 2 ... processors for parallelism. ... This can be configured at the server level ...
    (microsoft.public.sqlserver.server)
  • Re: Duplicate Process ID
    ... the query inside your sp is doing parallel processing. ... More information on parallelism can be found in SQL Sever 2000 Books Online. ... Vyas, MVP (SQL Server) ...
    (microsoft.public.sqlserver.server)
  • Re: Limit number of CPUs?
    ... Can you explain more of what your intent is? ... There is a setting called MAXDOP (Maximum degree of Parallelism) at the instance level. ...
    (microsoft.public.sqlserver.server)
Loading