Full text slow startup on SQL 2005 x64, Win2003 x64

Hi there,
We have a problem with a slow startup of the SQL 2005 full text engine on a
new box. The first query (after a reboot or first query in the morning)
against the full text index is very slow, it takes around 30 seconds to
execute. The full text index is very small (~2 mb), so this should not be an
issue.
On another server with the same database we don't see this problem. The
first query is executed in less than a second.
The only difference between the servers is that the server with the slow
full text is running SQL 2005 Standard x64 and Windows 2003 Standard x64.
The other server is running SQL 2005 Standard and Windows 2003 Standard.
Any suggestions?
I was just about to do a post for the same thing. I'm seeing this same
problem on two machines; both are Win2k3 Enterprise (32 bit); one is SQL
Server 2005 Enterprise and one is using 2005 Developer; both machines have
2GB RAM. The first query using FTS that I run takes ~45 seconds and then
every one after that is very fast (< 1 second), for about 10 or 15 minutes.
Every 10 or 15 minutes (haven't timed it exactly), it does the 45 second
thing again. Both machines have the same database (one's a development copy),
and the table with the index is only about 60MB, with ~40K rows.
Any help would be much appreciated.
thanks,
michael
"Rasmus Waeherns" wrote:

> Hi there,
> We have a problem with a slow startup of the SQL 2005 full text engine on a
> new box. The first query (after a reboot or first query in the morning)
> against the full text index is very slow, it takes around 30 seconds to
> execute. The full text index is very small (~2 mb), so this should not be an
> issue.
> On another server with the same database we don't see this problem. The
> first query is executed in less than a second.
> The only difference between the servers is that the server with the slow
> full text is running SQL 2005 Standard x64 and Windows 2003 Standard x64.
> The other server is running SQL 2005 Standard and Windows 2003 Standard.
> Any suggestions?
>
>
|||Hi there,
We tried everything, from reinstalling SQL Server 2005, disabling services,
restoring new databases, rebuilding full text index - you name it, and
didn't find a solution for the problem.
We've now reinstalled Windows 2003 x64 and SQL 2005 x64 on the server and
now the problems are gone. Weird!
Best regards,
Rasmus Waehrens
"ackphht" <ackphht@.discussions.microsoft.com> wrote in message
news:1B500AE2-5EBD-461C-90FE-6B1594D0B9F8@.microsoft.com...[vbcol=seagreen]
>I was just about to do a post for the same thing. I'm seeing this same
> problem on two machines; both are Win2k3 Enterprise (32 bit); one is SQL
> Server 2005 Enterprise and one is using 2005 Developer; both machines have
> 2GB RAM. The first query using FTS that I run takes ~45 seconds and then
> every one after that is very fast (< 1 second), for about 10 or 15
> minutes.
> Every 10 or 15 minutes (haven't timed it exactly), it does the 45 second
> thing again. Both machines have the same database (one's a development
> copy),
> and the table with the index is only about 60MB, with ~40K rows.
> Any help would be much appreciated.
> thanks,
> michael
> "Rasmus Waeherns" wrote:

Full text search with MSQL 2005

Does MSQL 2005 Full-Text-Search engine make use of Microsoft Search Service like the previous version? (MSQL2000)

If yes, where there any improvements in performance? Is MSQL 2005 Full-Text-Search recommended AND ready for large scale / enterprise systems with lots of traffic? If yes, how is the performance?

I've been doing some research, but I can't find any good articles on it... if you know of any please add them to the thread! :)

Thank you very much!

You can read all about the architecture of SQL Server 2005 Full Text Search here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp

Buck Woody

|||

You can read all about the architecture of SQL Server 2005 Full Text Search here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp

Buck Woody

|||Did this help you out or do you still have questions? If you need anything else, let us know, otherwise you can mark this as "answered".|||how can I scale FTS service by installing different machines?|||

Although this is for SQL Server 2000, the information still holds:

http://support.microsoft.com/?scid=http%3a%2f%2fsupport.microsoft.com%2fsupport%2fsql%2fcontent%2f2000papers%2ffts_white+paper.asp

Buck Woody

Full text search with MSQL 2005

Does MSQL 2005 Full-Text-Search engine make use of Microsoft Search Service like the previous version? (MSQL2000)

If yes, where there any improvements in performance? Is MSQL 2005 Full-Text-Search recommended AND ready for large scale / enterprise systems with lots of traffic? If yes, how is the performance?

I've been doing some research, but I can't find any good articles on it... if you know of any please add them to the thread! :)

Thank you very much!

You can read all about the architecture of SQL Server 2005 Full Text Search here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp

Buck Woody

|||

You can read all about the architecture of SQL Server 2005 Full Text Search here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp

Buck Woody

|||Did this help you out or do you still have questions? If you need anything else, let us know, otherwise you can mark this as "answered".|||how can I scale FTS service by installing different machines?|||

Although this is for SQL Server 2000, the information still holds:

http://support.microsoft.com/?scid=http%3a%2f%2fsupport.microsoft.com%2fsupport%2fsql%2fcontent%2f2000papers%2ffts_white+paper.asp

Buck Woody

sql

Full text search with MSQL 2005

Does MSQL 2005 Full-Text-Search engine make use of Microsoft Search Service like the previous version? (MSQL2000)

If yes, where there any improvements in performance? Is MSQL 2005 Full-Text-Search recommended AND ready for large scale / enterprise systems with lots of traffic? If yes, how is the performance?

I've been doing some research, but I can't find any good articles on it... if you know of any please add them to the thread! :)

Thank you very much!

You can read all about the architecture of SQL Server 2005 Full Text Search here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp

Buck Woody

|||

You can read all about the architecture of SQL Server 2005 Full Text Search here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp

Buck Woody

|||Did this help you out or do you still have questions? If you need anything else, let us know, otherwise you can mark this as "answered".|||how can I scale FTS service by installing different machines?|||

Although this is for SQL Server 2000, the information still holds:

http://support.microsoft.com/?scid=http%3a%2f%2fsupport.microsoft.com%2fsupport%2fsql%2fcontent%2f2000papers%2ffts_white+paper.asp

Buck Woody

Full text search with MSQL 2005

Does MSQL 2005 Full-Text-Search engine make use of Microsoft Search Service like the previous version? (MSQL2000)

If yes, where there any improvements in performance? Is MSQL 2005 Full-Text-Search recommended AND ready for large scale / enterprise systems with lots of traffic? If yes, how is the performance?

I've been doing some research, but I can't find any good articles on it... if you know of any please add them to the thread! :)

Thank you very much!

You can read all about the architecture of SQL Server 2005 Full Text Search here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp

Buck Woody

|||

You can read all about the architecture of SQL Server 2005 Full Text Search here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp

Buck Woody

|||Did this help you out or do you still have questions? If you need anything else, let us know, otherwise you can mark this as "answered".|||how can I scale FTS service by installing different machines?|||

Although this is for SQL Server 2000, the information still holds:

http://support.microsoft.com/?scid=http%3a%2f%2fsupport.microsoft.com%2fsupport%2fsql%2fcontent%2f2000papers%2ffts_white+paper.asp

Buck Woody

Full text search multiple tables

Hi all,
I want to full text search on multiple tables.
Example my strSearch = example full text search engine.

"SELECT TOP (100) PERCENT FT_TBL.Description AS mota, FT_TBL.test1_ID, KEY_TBL.RANK
FROM dbo.test1 AS FT_TBL INNER JOIN
CONTAINSTABLE(test1, *, '"+strSearch+"') AS KEY_TBL ON FT_TBL.test1_ID = KEY_TBL.[KEY]
UNION ALL
SELECT TOP (100) PERCENT FT_TBL1.Description1 AS mota, FT_TBL1.test2_ID, KEY_TBL1.RANK
FROM dbo.test2 AS FT_TBL1 INNER JOIN
CONTAINSTABLE(test2, *, '"+strSearch+"') AS KEY_TBL1 ON FT_TBL1.test2_ID = KEY_TBL1.[KEY]"

I want to show results order such as GOOGLE: show top records full keyword "example full text search engine", then continune left phrases.

pls help me

You want to try freetexttable. If all words are found in a row, the row is given higher rank. However, there is no garantee that the full phrase is getting better scan than a partial match (for example, if there are several partial matches found)

If you want a match exactly the phrase, you need to do a phrase search. Probably you want to do a phrase search union freetext table?

Rank from different tables generally are not comparable. You can create an indexed view of the two table and build fulltext index over the indexed view to get comparable ranking.

Full Text Search Engine in Chinese Simplied

contains(name,'"张三"')

will not find the row in database with column named "name" and "张三" is sure there,but will find '张三一'，'张三二' why?

name column is sure in the fulltext category and data population is finished!ID Name Sex
-
1 张三 男
2 张三一 男
3 张三二 女
4 张三四 女
I execute the T-SQL Statement : contains(name, '"张三"'),and the Result :

ID Name Sex
-
2 张三一 男
3 张三二 女
4 张三四 女
Why the Row named 张三 is not in result？|||While I don't read Chinese, could you reply with the full output of the following SQL code?

select objectproperty(OBJECT_ID(N'<table_name>'), 'TableFulltextItemCount')

use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go

Please, note the LCID values from sp_help_fulltext_columns for the FULLTEXT_LANGUAGE column. This information should help identify what the problem is for your Full Text Search (FTS) enabled table.

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

|||This is due to wordbreaking behaviour in Chinese Simplified wordbreaker. '张三一' is got broken into '张' and '三一'. You could try freetext instead of contains to allow some fuzziness into the query.

select * from Table1 where freetext (*, N'张三')

Full Text Search Engine in Chinese Simplied

contains(name,'"张三"')

will not find the row in database with column named "name" and "张三" is sure there,but will find '张三一'，'张三二' why?

name column is sure in the fulltext category and data population is finished!ID Name Sex
-
1 张三 男
2 张三一 男
3 张三二 女
4 张三四 女
I execute the T-SQL Statement : contains(name, '"张三"'),and the Result :

ID Name Sex
-
2 张三一 男
3 张三二 女
4 张三四 女
Why the Row named 张三 is not in result？
|||While I don't read Chinese, could you reply with the full output of the following SQL code?

select objectproperty(OBJECT_ID(N'<table_name>'), 'TableFulltextItemCount')

use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go

Please, note the LCID values from sp_help_fulltext_columns for the FULLTEXT_LANGUAGE column. This information should help identify what the problem is for your Full Text Search (FTS) enabled table.

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

|||This is due to wordbreaking behaviour in Chinese Simplified wordbreaker. '张三一' is got broken into '张' and '三一'. You could try freetext instead of contains to allow some fuzziness into the query.

select * from Table1 where freetext (*, N'张三')

Full Text Search Engine and russian language support...

I try to develope application with full text search in SQl tables by fields, that containing russian text. I had to use "neutral" for language for word breaker.
Is there are russian addon's for MS SQL 2000, that can solve this problem? Would be included in MS SQL 2005 russian language support? If not - how can I solve it by other way?

You may want to post this on the database engine forum (for full-text search) - http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93

Full text issue

Hello,

I run a music related website (www.LowestCostMusic.com) and I'm using the SQL2000 full text search engine.

If you go to the site via this link... It would be searching on the phrase 'the way it is'

http://www.lowestcostmusic.com/search2.asp?search=the+way+it+is

Which is all noise words... yet could be a title of some sheet music that we carry. I need to know how to build the SQL full text catalog to allow this search to work properly... Any ideas? Thanks!

-MattMatt,

Thanks for using Microsoft SQL Server 2000 and Full-Text Search on your web site. This should help:

You need to update your noise words list and then repopulate your Full-Text index in order to accept searches on titles such as "the way it is" . You may find your noise word files under:

\Program Files\Microsoft SQL Server\MSSQL$INSTANCENAME\Ftdata\SQLServer\Config\ noise.enu (for US English)

You may add words to be excluded from the index, and likewise remove words that you would like included.

Once you are satisfied with changes, restart MSSearch service and you will need to repopulate your catalogs.

This should fix your problem right away.

All the best,
--andrew

Full Text Indexing Problem in Desktop Engine

Hello,

I have MS Desktop Engine Service Pack 1 and I want to do Full text indexing but when I tried full text indexing on desktop engine it gives me error. I found the solution that upgrade it to service pack 3. Then i download service pack 3 and try to upgrade it. but it gives error that no Sql server installation found. Plz help me. what should i do for full text indexing.

Will the full text indexing increase the speed of webpage accessing for searching?

Thanks in advance.

Fulltext is not a supported feature for MSDE. You have to upgrade to Sql2k5 Express edition to get Fulltext.

Full Text Catalog Not Building

Hi,
We have created a catalog for our search engine in our website and
everything was working fine before. Right after we created a new
stored procedure that updates the table in which the catalog was
referencing to, our search engine stopped working. We have created the
catalog multiple times, restarting services, repopulating catalogs,
etc. but got nothing working.
Do you guys have any idea about this?
Any help is really appreciated.
Baldwin
bbudiongan@.misicompany.com
Are there any error messages in the event log from mssci or mssearch?
<ibaldwinjr@.gmail.com> wrote in message
news:1177936766.967895.276810@.h2g2000hsg.googlegro ups.com...
> Hi,
> We have created a catalog for our search engine in our website and
> everything was working fine before. Right after we created a new
> stored procedure that updates the table in which the catalog was
> referencing to, our search engine stopped working. We have created the
> catalog multiple times, restarting services, repopulating catalogs,
> etc. but got nothing working.
> Do you guys have any idea about this?
> Any help is really appreciated.
> Baldwin
> bbudiongan@.misicompany.com
>
|||On Apr 30, 10:04 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Are there any error messages in the event log from mssci or mssearch?<ibaldwi...@.gmail.com> wrote in message
> news:1177936766.967895.276810@.h2g2000hsg.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Thanks for the immediate response. I am still trying to get the logs
from the DBA guys but what they say is they got an error like File Not
Found in th FTDATA folder. I asked them to recreate the catalog but
they can't. Instead they are presented with the error message saying
like start the rebuild, then full repopulate. I hope this makes sense
to you.
Baldwin

Full Catalog Update

Hi,
I ve found an issue about my seach engine text by using full-text catalogs.
So, my question is.. (maybe is stupid)
How to automatically update the catalog, when I update the tables ?
I ve seen there's a scheduler but does it mean, when I add some records into
the tables, the catalogue is not updated until the procedure starts ?
Stan>
> Hi,
> I ve found an issue about my seach engine text by using full-text
catalogs.
> So, my question is.. (maybe is stupid)
> How to automatically update the catalog, when I update the tables ?
> I ve seen there's a scheduler but does it mean, when I add some records
into
> the tables, the catalogue is not updated until the procedure starts ?
> Stan
--
Here is an extract from SQL Books online:
Maintaining Full-Text Indexes
There are three ways to maintain a full-text index:
Full rebuild
Rescans all rows. Completely rebuilds the full-text index. You can perform
a full rebuild immediately or on a schedule, using SQL Server Agent.
Timestamp-based incremental rebuild
Rescans those rows that have changed since the last full or incremental
rebuild. This requires a timestamp column on the table. Changes that do not
update the timestamp, such as WRITETEXT and UPDATETEXT, are not detected.
You can perform an incremental rebuild immediately or on a schedule.
Change tracking
Maintains a list of all changes to the indexed data. Changes made with
WRITETEXT and UPDATETEXT are not detected. You can update the full-text
index with these changes immediately, on a schedule, or as they occur,
using the background update index option.
The method you use depends on factors such as the CPU and available memory,
the amount and rate of change of data, the amount of available disk space,
and the importance of the full-text index being current. Use these
recommendations as a guide for selecting a maintenance method.
Use change tracking with the background update index option when CPU and
memory are available, the value of an up-to-date index is high, and
immediate propagation can keep up with the rate of changes.
Use change tracking with scheduled propagation when CPU and memory can be
used at scheduled times, disk space for storing changes is available, and
changes between the scheduled times are not so significant that the
propagation takes longer than a full rebuild.
Use a full rebuild when a large percentage of records change or are added
at once. If a large percentage of records change over an extended period of
time, consider using change tracking with scheduled or background update
index.
Use an incremental rebuild when a large number, but not a large percentage,
of documents change at one time. If a large number of records change over
an extended period of time, consider using change tracking with scheduled
or background update index.
Hope this helps,
--
Eric Cárdenas
support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Full Catalog Update

Hi,
I ve found an issue about my seach engine text by using full-text catalogs.
So, my question is.. (maybe is stupid)
How to automatically update the catalog, when I update the tables ?
I ve seen there's a scheduler but does it mean, when I add some records into
the tables, the catalogue is not updated until the procedure starts ?
Stan
>
> Hi,
> I ve found an issue about my seach engine text by using full-text
catalogs.
> So, my question is.. (maybe is stupid)
> How to automatically update the catalog, when I update the tables ?
> I ve seen there's a scheduler but does it mean, when I add some records
into
> the tables, the catalogue is not updated until the procedure starts ?
> Stan
Here is an extract from SQL Books online:
Maintaining Full-Text Indexes
There are three ways to maintain a full-text index:
Full rebuild
Rescans all rows. Completely rebuilds the full-text index. You can perform
a full rebuild immediately or on a schedule, using SQL Server Agent.
Timestamp-based incremental rebuild
Rescans those rows that have changed since the last full or incremental
rebuild. This requires a timestamp column on the table. Changes that do not
update the timestamp, such as WRITETEXT and UPDATETEXT, are not detected.
You can perform an incremental rebuild immediately or on a schedule.
Change tracking
Maintains a list of all changes to the indexed data. Changes made with
WRITETEXT and UPDATETEXT are not detected. You can update the full-text
index with these changes immediately, on a schedule, or as they occur,
using the background update index option.
The method you use depends on factors such as the CPU and available memory,
the amount and rate of change of data, the amount of available disk space,
and the importance of the full-text index being current. Use these
recommendations as a guide for selecting a maintenance method.
Use change tracking with the background update index option when CPU and
memory are available, the value of an up-to-date index is high, and
immediate propagation can keep up with the rate of changes.
Use change tracking with scheduled propagation when CPU and memory can be
used at scheduled times, disk space for storing changes is available, and
changes between the scheduled times are not so significant that the
propagation takes longer than a full rebuild.
Use a full rebuild when a large percentage of records change or are added
at once. If a large percentage of records change over an extended period of
time, consider using change tracking with scheduled or background update
index.
Use an incremental rebuild when a large number, but not a large percentage,
of documents change at one time. If a large number of records change over
an extended period of time, consider using change tracking with scheduled
or background update index.
Hope this helps,
Eric Crdenas
support professional
This posting is provided "AS IS" with no warranties, and confers no rights.