Full Text Search slow, then fast for 30 minutes, then slow again

We've just upgraded a database to SQL 2005 Standard sp1 on a Windows 2003 sp1 server and we are experiencing a problem with Full Text Search. We recreated a Full Text Catalogue in the new SQL 2005 db and it seemed to populate fine. However, now when we run a query on that FTI for the first time it takes about 30-45 seconds to return a result. After that first query, subsequent queries return the result immediately. Then if there is an approximate 30 minute gap until the next query is run then it goes back to taking 30-45 seconds to return the result. It seems as though the results of the query are cached and remain in the chache as long as queries continue to be submitted at <30 minute intervals. If a query is not submitted in 30 minutes then the cache expires. That is only my theory. Has anyone seen this issue? Any insite you can provide is greatly appreciated. Thanks.Does your system have internet access? On loading the system resources for FTS, it will connect to the internet to validate the signature. The duration is about what you've reported, 45sec. After about 30 minutes of no activity, the resouces will be unloaded to save memory.

So, to keep the resources in memory you could create a job that execute the query every 10minutes.|||

Hi OJ,

The signature of what? Is there any option to disable this validation?

Please advice. Thanks, Radu.

|||It's the signature of the DLLs/binaries used by MsSearch (FTS) service. You can turn off the checking by running

exec sp_fulltext_Service 'verify_signature',0

Full Text Search slow, then fast for 30 minutes, then slow again

We've just upgraded a database to SQL 2005 Standard sp1 on a Windows 2003 sp1 server and we are experiencing a problem with Full Text Search. We recreated a Full Text Catalogue in the new SQL 2005 db and it seemed to populate fine. However, now when we run a query on that FTI for the first time it takes about 30-45 seconds to return a result. After that first query, subsequent queries return the result immediately. Then if there is an approximate 30 minute gap until the next query is run then it goes back to taking 30-45 seconds to return the result. It seems as though the results of the query are cached and remain in the chache as long as queries continue to be submitted at <30 minute intervals. If a query is not submitted in 30 minutes then the cache expires. That is only my theory. Has anyone seen this issue? Any insite you can provide is greatly appreciated. Thanks.Does your system have internet access? On loading the system resources for FTS, it will connect to the internet to validate the signature. The duration is about what you've reported, 45sec. After about 30 minutes of no activity, the resouces will be unloaded to save memory.

So, to keep the resources in memory you could create a job that execute the query every 10minutes.|||

Hi OJ,

The signature of what? Is there any option to disable this validation?

Please advice. Thanks, Radu.

|||It's the signature of the DLLs/binaries used by MsSearch (FTS) service. You can turn off the checking by running

exec sp_fulltext_Service 'verify_signature',0

Full text index functino not installed properly

I recently upgraded a SQL server 7 installation to 2000 sp3a. This morning I
need to enable full text indexing. I went through the installation process
but the indexing options are not showing up. I tried a CLI and it told me
the services are not installed. How can I troubleshoot full text
installation or re-installs the serviceS?
The Microsoft Search service can be installed via the SQL Server setup
program. You might try the Add/Remove Programs entry for SQL Server to see
if you can add just the MS Search service.
HTH
Jerry
"CPLEV" <CPLEV@.discussions.microsoft.com> wrote in message
news:89D35CE3-D649-4AF6-8A3E-EB9ABD71105B@.microsoft.com...
>I recently upgraded a SQL server 7 installation to 2000 sp3a. This morning
>I
> need to enable full text indexing. I went through the installation
> process
> but the indexing options are not showing up. I tried a CLI and it told me
> the services are not installed. How can I troubleshoot full text
> installation or re-installs the serviceS?

Full text index functino not installed properly

I recently upgraded a SQL server 7 installation to 2000 sp3a. This morning
I
need to enable full text indexing. I went through the installation process
but the indexing options are not showing up. I tried a CLI and it told me
the services are not installed. How can I troubleshoot full text
installation or re-installs the serviceS?The Microsoft Search service can be installed via the SQL Server setup
program. You might try the Add/Remove Programs entry for SQL Server to see
if you can add just the MS Search service.
HTH
Jerry
"CPLEV" <CPLEV@.discussions.microsoft.com> wrote in message
news:89D35CE3-D649-4AF6-8A3E-EB9ABD71105B@.microsoft.com...
>I recently upgraded a SQL server 7 installation to 2000 sp3a. This morning
>I
> need to enable full text indexing. I went through the installation
> process
> but the indexing options are not showing up. I tried a CLI and it told me
> the services are not installed. How can I troubleshoot full text
> installation or re-installs the serviceS?

Full text index functino not installed properly

I recently upgraded a SQL server 7 installation to 2000 sp3a. This morning I
need to enable full text indexing. I went through the installation process
but the indexing options are not showing up. I tried a CLI and it told me
the services are not installed. How can I troubleshoot full text
installation or re-installs the serviceS?The Microsoft Search service can be installed via the SQL Server setup
program. You might try the Add/Remove Programs entry for SQL Server to see
if you can add just the MS Search service.
HTH
Jerry
"CPLEV" <CPLEV@.discussions.microsoft.com> wrote in message
news:89D35CE3-D649-4AF6-8A3E-EB9ABD71105B@.microsoft.com...
>I recently upgraded a SQL server 7 installation to 2000 sp3a. This morning
>I
> need to enable full text indexing. I went through the installation
> process
> but the indexing options are not showing up. I tried a CLI and it told me
> the services are not installed. How can I troubleshoot full text
> installation or re-installs the serviceS?

Full population very slow

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
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> 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
>
|||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
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> 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
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||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
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||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
"John Kane" wrote:

> 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
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>