Full population very slow

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ad - 21 Apr 2005 22:41 GMT

I've got SQL2000 Enterprise edition running on windows 2000 advanced server,
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.

The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.

mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.

Is there any way of speeding up population?

Thanks,

Ad

Reply to this Message

Hilary Cotter - 22 Apr 2005 00:40 GMT

set sp_fulltext_service 'resource_usage', 5

> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
[quoted text clipped - 23 lines]
>
> Ad

Reply to this Message

John Kane - 22 Apr 2005 01:24 GMT

Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:

at <current_time+1min> /interactive taskmgr.exe

when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.

Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.

Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

Regards,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> I've got SQL2000 Enterprise edition running on windows 2000 advanced server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
[quoted text clipped - 19 lines]
>
> Ad

Reply to this Message

Ad - 26 Apr 2005 03:17 GMT

sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?

Thanks,
Ad

Reply to this Message

John Kane - 26 Apr 2005 03:30 GMT

Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.

Regards,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
> it need stop and re-start of MSSearch service?
>
> Thanks,
> Ad

Reply to this Message

AD - 26 Apr 2005 09:00 GMT

The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.

Regards,

Ad

> Ad,
> I didn't think it would make much difference. However, where you get the
[quoted text clipped - 12 lines]
> > Thanks,
> > Ad

Reply to this Message