incomplete Full text index for large dbs in sql 2005??

incomplete Full text index for large dbs in sql 2005??

Post by Q1RTX0RCQ » Wed, 29 Nov 2006 13:30:01


we have created a full text catalogue on an Addresses table with exactly same
configuration on 3 databases. Two of them are on SQL 2005 and one on SQL 2000.

My Addresses table has 13 million rows and the full text was on the Full
Address column.

db is around 250GB

FT Index on SQL 2005 database was created very quickly (within 2 hours)
while SQL 2000 FT took 30 hours.

But my issue is SQL 2005 index does not contain all the words. For an
example following 2 queries gives me the exactly same number on the SQL 2000
database while on SQL 2005, first query only return 11 and the 2nd one 274
(274 is same on 2000 server/database as well)

SELECT count(*)

FROM [dbo].[Addresses]

WHERE CONTAINS(FullAddress, 'WARMINSTER')

GO

SELECT count(*)

FROM [dbo].[Addresses]

WHERE FullAddress like '%WARMINSTER%'

Is this a bug?

When i extracted the records which should have retrived in to a seperate
table (few hundred records) and created a FT index on that it works fine.


--
CTS DBA
 
 
 

incomplete Full text index for large dbs in sql 2005??

Post by Hilary Cot » Wed, 29 Nov 2006 22:39:22

I've had problems on very large databases with this. I think we were over
400,000,000 rows when we first noticed it. A reorganize would normally help.

Can you first run this query?

SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'

See if perhaps you are getting hits to Warministershireontheavon or
something like that. Note that most punctuation will be ignored.

If you are trully missing hits can you check the gatherer log to see if
there are any error messages?


--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.yqcomputer.com/

Looking for a FAQ on Indexing Services/SQL FTS
http://www.yqcomputer.com/

 
 
 

incomplete Full text index for large dbs in sql 2005??

Post by Q1RTX0RCQ » Thu, 30 Nov 2006 07:50:01

Thanks Hilary,

SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'

The Query returns only 4 records which means still so many records missing
in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
CATALOG cat_Addresses REORGANIZE) and update you with the results when it
completed.

thanks



--
CTS DBA
 
 
 

incomplete Full text index for large dbs in sql 2005??

Post by Q1RTX0RCQ » Thu, 30 Nov 2006 08:14:01

e-organize has been completed. But still no luck. And there is nothing in
the gatherer logs as well. See below from the gatherer logs for the entries
from re-organize and re-building I did today.

RE_BUILDING

2006-11-29 09:28:15.58 spid20s Informational: Full-text Full population
completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Number of documents
processed: 10038019. Number of documents failed: 0. Number of documents need
retry: 0.

2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.

2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto population
initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Population sub-tasks: 1.





RE-ORGANIZING

2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.


--
CTS DBA


"CTS_DBA" wrote:

 
 
 

incomplete Full text index for large dbs in sql 2005??

Post by Hilary Cot » Thu, 30 Nov 2006 22:41:28

t looks like all the rows were successfully processed. My concern still is
that perhaps there are some preceding or trailing characters which might
account for these rows not showing up. Can you confirm this?

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"CTS_DBA" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

incomplete Full text index for large dbs in sql 2005??

Post by Q1RTX0RCQ » Fri, 01 Dec 2006 15:04:01

ep, cause as mentioned earlier same DB, sql 2000 FT returns the expected
result
the other thing I'm worried is that sql 2005 took 2 hours and 2000 took
around 30 hours, huge different, and of course servers are , you can sya same
hard ware.


--
CTS DBA


"Hilary Cotter" wrote:

 
 
 

incomplete Full text index for large dbs in sql 2005??

Post by bstrackan » Sun, 03 Dec 2006 04:27:31

hat if you run

SELECT fulladdress FROM [dbo].[Addresses] WHERE
FullAddress LIKE '%WARMINSTER%'
AND NOT CONTAINS(FullAddress, 'WARMINSTER')

do the fulladdress results look funny in any way, or is WARMINSTER at
the start of a word? Maybe a non-ASCII character in there somehow?

Ben Strackany


CTS_DBA wrote:

 
 
 

incomplete Full text index for large dbs in sql 2005??

Post by Hilary Cot » Tue, 05 Dec 2006 22:08:24

his says everything processed successfully.

I am really not sure how to advice you on where to go from here.

One of my lingering concerns is that this query

SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'

returns 4 results - what are they?

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"CTS_DBA" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...