|
|
You're in the
sql server full text search
group:incomplete Full text index for large dbs in sql 2005??
sql server full text search:
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. --
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.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "CTS_DBA" <CTSDBA@discussions.microsoft.com> wrote in message news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@microsoft.com... > 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
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’s completed. thanks -- CTS DBA [quoted text, click to view] "Hilary Cotter" wrote:
> 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.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > > > "CTS_DBA" <CTSDBA@discussions.microsoft.com> wrote in message > news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@microsoft.com... > > 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 > >
Re-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 [quoted text, click to view] "CTS_DBA" wrote:
> 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’s > completed. > > thanks > > > > -- > CTS DBA > > > "Hilary Cotter" wrote: > > > 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.nwsu.com/0974973602.html > > > > Looking for a FAQ on Indexing Services/SQL FTS > > http://www.indexserverfaq.com > > > > > > > > "CTS_DBA" <CTSDBA@discussions.microsoft.com> wrote in message > > news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@microsoft.com... > > > 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 > > > >
It 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 [quoted text, click to view] "CTS_DBA" <CTSDBA@discussions.microsoft.com> wrote in message news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@microsoft.com... > Re-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: > >> 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's >> completed. >> >> thanks >> >> >> >> -- >> CTS DBA >> >> >> "Hilary Cotter" wrote: >> >> > 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.nwsu.com/0974973602.html >> > >> > Looking for a FAQ on Indexing Services/SQL FTS >> > http://www.indexserverfaq.com >> > >> > >> > >> > "CTS_DBA" <CTSDBA@discussions.microsoft.com> wrote in message >> > news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@microsoft.com... >> > > 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 >> > >> > >> >
What 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 [quoted text, click to view] CTS_DBA wrote:
> 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's > completed. > > thanks > > > > -- > CTS DBA > > > "Hilary Cotter" wrote: > > > 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.nwsu.com/0974973602.html > > > > Looking for a FAQ on Indexing Services/SQL FTS > > http://www.indexserverfaq.com > > > > > > > > "CTS_DBA" <CTSDBA@discussions.microsoft.com> wrote in message > > news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@microsoft.com... > > > 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 > > > > > >
This 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 [quoted text, click to view] "CTS_DBA" <CTSDBA@discussions.microsoft.com> wrote in message news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@microsoft.com... > Re-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: > >> 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's >> completed. >> >> thanks >> >> >> >> -- >> CTS DBA >> >> >> "Hilary Cotter" wrote: >> >> > 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.nwsu.com/0974973602.html >> > >> > Looking for a FAQ on Indexing Services/SQL FTS >> > http://www.indexserverfaq.com >> > >> > >> > >> > "CTS_DBA" <CTSDBA@discussions.microsoft.com> wrote in message >> > news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@microsoft.com... >> > > 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 >> > >> > >> >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |