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 Search Installation doesn't work??

I installed Full Text Search on my Standard Personal
Edition following the steps in this Microsoft Link:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;827449&Product=sql2k
When I try to enable full text search, I get the following
error message. Can anyone help? :
Server: Msg 7609, Level 17, State 2, Procedure
sp_fulltext_database, Line 46
Full-Text Search is not installed, or a full-text
component cannot be loaded.
I can see Microsoft search (which I didn't before) on the
Service Manager. I have also started the service. Why
does it show grayed out in Enterprise Mananager - Tools -
Full Text Indexing - eventhough I installed it? Please
HELPPPPP. Thanks
JohnCan anybody help me with this Please. I am waiting
desperately. The installation seemed fine but I dont seem
to see the Full Text Indexing thing - it is grayed out in
Enterprise Manager and not found in suporting services.
Help!!!!
John
>--Original Message--
>I installed Full Text Search on my Standard Personal
>Edition following the steps in this Microsoft Link:
>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;827449&Product=sql2k
>When I try to enable full text search, I get the
following
>error message. Can anyone help? :
>Server: Msg 7609, Level 17, State 2, Procedure
>sp_fulltext_database, Line 46
>Full-Text Search is not installed, or a full-text
>component cannot be loaded.
>I can see Microsoft search (which I didn't before) on the
>Service Manager. I have also started the service. Why
>does it show grayed out in Enterprise Mananager - Tools -
>Full Text Indexing - eventhough I installed it? Please
>HELPPPPP. Thanks
>John
>.
>|||I fixed the problem. Reapply SP3a fixed the Full Text
problem. Thx.
John
>--Original Message--
>Can anybody help me with this Please. I am waiting
>desperately. The installation seemed fine but I dont
seem
>to see the Full Text Indexing thing - it is grayed out in
>Enterprise Manager and not found in suporting services.
>Help!!!!
>John
>
>>--Original Message--
>>I installed Full Text Search on my Standard Personal
>>Edition following the steps in this Microsoft Link:
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;827449&Product=sql2k
>>When I try to enable full text search, I get the
>following
>>error message. Can anyone help? :
>>Server: Msg 7609, Level 17, State 2, Procedure
>>sp_fulltext_database, Line 46
>>Full-Text Search is not installed, or a full-text
>>component cannot be loaded.
>>I can see Microsoft search (which I didn't before) on
the
>>Service Manager. I have also started the service. Why
>>does it show grayed out in Enterprise Mananager - Tools -
>>Full Text Indexing - eventhough I installed it? Please
>>HELPPPPP. Thanks
>>John
>>.
>.
>

Full Text Search Indexing HTML - does the filter expect certain tags to be present as standard?

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)

However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @.SearchWord nvarchar(256)
SET @.SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @.SearchWord)

I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).

Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):

Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:

Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.

etc.

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>

I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?I've further isolated what the problem is and have started a new thread to request more specific help...
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1844786&SiteID=17

Full text indexing question

All,

I am using the standard edition of SQL Server 2005.

I have a table I want to put a full text index on but it is greyed out. What are the reasons why it would be greyed out?

Thx

jonp

Jonp,
Assuming you're using the standard edition of SQL Server 2005 RTM and that the SQL Server FullText Search (msftesql.exe) service is installed and running, there should be no reasong for the "defining full text index" content menu item to be grayed out.

Could you post the full output of @.@.version from the server where this problem exists as well as confirm that the SQL Server FullText Search (msftesql.exe) service is installed and running on this server?

Thanks,
John
SQL 2005 Full Text Search Blog
http://spaces.msn.com/members/jtkane/|||

I verified it was running on the box.

I had to do the following :

USE customer

EXEC sp_fulltext_database 'enable'

After that all seems to work. Any reason why it wouldnt be enabled initially?

|||Cool...
Is it possible that your database 'customer' was in 80 compatability mode? Could you check that via the following SQL code?

EXEC sp_dbcmptlevel 'customer'
-- should return: The current compatibility level is 90.

Thanks,
John
SQL 2005 Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||

It is 90. Does that just mean that I am using SQL Server 2005?

I assume 80 would be SQL Server 2000, right?

|||Yes, it does or specificlly, it means that the database 'customer' is in '90' or the default SQL Server 2005 database compatibility mode. If it had returned '80', that would indicate that the 'customer' database on your SQL Server 2005 server was in '80' or SQL Server 2000 compatibility mode.

However, SQL Server 2005 compatiblity mode (90) databases are FT-enabled by default and using sp_fulltext_database to FT-enable should not be necessary, unless at sometime in the past this database was specificly disabled via sp_fulltext_database. I'm not sure if you can confirm that, however, if you use standard create database scripts that may do this, please review them.

Thanks,
John|||

Not quite John. Databases created using create database have full text enabled by default. However databases created through management studio you have to explictly check the full text box to enable full text.

I raised this and got a half hearted response. Basically the default setting in management studio is the opposite of that stated in BOL.

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=24d1edf0-3e4c-4bac-bc6e-51b143ca5322

To correct the issue, go to the database properties and on the files section check the full tec check box.