Re: Performance problems -- need guidance on scaling

From: John Kane (jt-kane_at_comcast.net)
Date: 11/05/04 

Date: Thu, 4 Nov 2004 22:29:43 -0800

You're welcome, Phil,
Thanks for the update on the FT Catalog corruption, i.e.. reliability
problem & yes an int (or short char datatype) is the preferred datatype for
using with a FT Index.

The default value for sp_fulltext_service resource_usage is 3 with 5 being
the max (or dedicated) value. This not only controls the max number of
concurrent FTS query, but also what level of memory the MSSearch service
will use, up to a max of 512Mb, if this memory is available.

Resource_usage Concurrent connections
---------------- ------------------------
 1 128
 2 512
 3 1024 (default)
 4 2048
 5 4096

Valid value parameters are from 1 to 5, with 3 as the default; these values
indicate proportionally allocated memory within minimum and maximum
boundaries for the given physical memory available on the server. These
minimum and maximum values relate to the number of word lists kept by the
Microsoft Search engine until Shadow Merge completes. The following table
relates Microsoft Search resource levels with the minimum and maximum number
of word lists held in memory.

Parameter Word lists held in memory
====== ==================
1 20/30
2 20/30
3 20/40
4 20/50
5 30/60

For example, a server with 512 MB of RAM and a resource_usage value of 5
will get 60 word lists held in memory and a computer with 128 MB will get 30
word lists held in memory. The actual values are computed by determining the
available physical memory on the server. The number of rows maintained in
memory by the Microsoft Search service before the Master Merge is 250,000 or
500,000, depending on the available physical memory on the server. In
addition to the memory allocated to SQL Server, it is recommended that a
minimum of 15 MB of RAM be reserved for the Microsoft Search service and a
maximum of 512 MB of RAM be allocated for the Microsoft Search service when
you are full-text indexing tables with millions of rows."

So, a single CONTAINSTABLE clause, but for your complex queries, a long set
of AND'ed &/or OR'ed search words, this might be enough justify the CPU
usage.

As for SQL Server 2005 (Yukon) beta2 version, it is stable, but I'm not sure
if I'd say it was production ready. Beta 3 will be early next year, with the
RTM version scheduled to be released in the summer of 2005, although expect
some schedule slippage, based upon past experiences <g>. As for transferring
the data, you could (on a test server), detach your SQL 2000 mdf & ldf files
and copy them to a new Yukon installation, however, I doubt if you're be
able to transfer the FT Catalog files as the underlying structures have
drastically changed. Although, the time to build the Yukon based FT Catalog
will be order's of magnitude faster than the build time with SQL Server
2000!

Thanks,
John
PS: As for the MVP, time and over-allocation is still a factor, so we'll
see!

"Phil Sherrod" <phil.sherrod@REMOVETHISsandh.com> wrote in message
news:X-mdnTT0H6UGNBfcRVn-gg@giganews.com...
> John,
>
> Thank you for your message, and thank you for the help you provided
> previously. I hope you got your MVP rating.
>
> I never updated you on the solution to the reliability problems that we
> finally got from Microsoft. When we designed our system, we were
concerned
> about overflowing a 32-bit integer in a few years, so we used a double
> precision floating point value as the key; it had only integer values in
it.
> Apparently, there is a bug in FTS that causes it to crash occasionally if
> you use a floating point key. We dropped back to a 32-bit integer key,
and
> that solved the crashes.
>
> On 4-Nov-2004, "John Kane" <jt-kane@comcast.net> wrote:
>
> > I see that you've implemented many of the ideas & references we
discussed
> > previously under the "Re: Need help with full-text performance for large
> > database" thread in this newsgroup and more specifically, we discussed
> > that
> > FTI was mostly I/O bound, now that you're no longer i/o bound but are
now
> > bound by the CPU.
>
> Yes, that is correct. The switch to the Adaptec RAID controller with the
> 10k RPM disks tremendously increased our I/O capacity. So we're now CPU
> bound.
>
> I have read that putting the catalog and SQL table on separate RAID
> controllers improves performance, but the old Promise controller that we
> have is SO slow compared to the Adaptec that we opted to use it only for
our
> application files. We may consider adding a second Adaptec RAID
controller
> with two more SATA drives if you still think that splitting the table and
> catalog would provide significant performance improvement.
>
> > While I'm not sure if your server will support, multiple
> > CPU's, but that's the next step
>
> Our current server will support only a single CPU. We are considering a
new
> one with dual Xeon 3.2 GHz CPUs and 4 G of 800 MB FSB memory. Pretty much
> all of the servers we've found with more than 2 CPU capability use the
> slower 3.0 G CPU's with 333 or 300 MB FSB. I would appreciate your
comments
> about your feeling about whether this will be adequate.
>
> > and then use sp_configure to set SQL Server to use the other CPU to
avoid
> > the MSSearch service from affecting your normal SQL Server processing.
You
> > should set the sp_fulltext_service resource_usage level to 5 to allow
for
> > more concurrent SQL FTS queries.
>
> Please explain the sp_fulltext_service resource_usage level. What is the
> default value? How exactly do we increase it?
>
> Currently, we are sending 4 simultaneous searches to the server. Do you
> think this is a good number?
>
> > Could you post examples of your more complex searches that are taking 20
> > to
> > 30 seconds to complete? Are they using multiple CONTAINS or
CONTAINSTABLE
> > statements &/or depend upon additional where clause restrictions?
>
> There is only a single CONTAINSTABLE clause in any of our searches.
>
> Here is an example of a simple search that typically takes 2 to 10 seconds
> depending on the words specified:
>
> ("Basic" AND "Instinct" AND "Sharon" AND "Stone") AND (JPG OR GIF OR PNG
OR
> MPG OR AVI OR NZB)
>
> A complex query would combine up to 50 searches similar to the one shown
> above combined with OR, like this:
>
> (query1) OR (query2) or ... or (query50)
>
> Where query1, etc, is ("Basic" AND "Instinct" AND "Sharon" AND "Stone")
AND
> (JPG OR GIF OR PNG OR MPG OR AVI OR NZB) or something similar to it. In
> other words, each basic query will have AND and OR operators and up to 50
of
> them enclosed in parentheses will be combined using OR.
>
> Note that there may be 5 million or more entries in the catalog for JPG, 2
> million for MPG, etc., but the combination will probably only match a few
> messages if any.
>
> > Finally, I'd also recommend (if you have not already done so) that you
try
> > to get a copy of SQL Server 2005 (codename Yukon) beta2 that can be
> > downloaded from MSDN, if you are a subscriber to MSDN as Yukon contains
> > many, many FTS performance improvements!
>
> We have a Universal MSDN subscription, so that's not a problem. Do you
> think the beta2 version is reliable enough for production use?
>
> What's involved in transferring a table and catalog with 15 M rows to
Yukon?
> It would take a long time to build the index from scratch. Can Yukon
import
> SQL tables and FTS catalogs from SQL 8?
>
> --
> Phil Sherrod
> (phil.sherrod 'at' sandh.com)
> http://www.dtreg.com (decision tree modeling)
> http://www.nlreg.com (nonlinear regression)
> http://www.NewsRover.com (Usenet newsreader)
> http://www.LogRover.com (Web statistics analysis)

Relevant Pages

  • Re: Memory Issues
    ... Exchange and SQL Server. ... you identified memory problems and large amounts of paging. ...
    (microsoft.public.sqlserver.setup)
  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 Clust
    ... The Paging File usage is due to how the large memory allocation APIs work. ... Swap files for SQL do not need to be more than about 2-4 GB regardless of physical memory on the box. ... If you use Performance Monitor to track actual paging activity, you will see very little on a properly tuned SQL Server. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Allocating more memory to sql server
    ... Are you sure you have Windows 2000 Advanced Server and not just Server? ... SQL Server. ... memory used by SQL Server that will give a better idea of the actual usage. ... You also need to restart SQL Server after enabling AWE for it to take ...
    (microsoft.public.sqlserver.setup)