February 17, 2012 at 8:38 am
I've inherited a forum database with a FT catalog defined on the subjects and bodies of forum posts. The catalog consists of the two columns from the one table. It's a fairly active forum but searches and inserts are slow.
Querying the attributes of the catalog show the following:
Unique Key Count: 0
Item Count: 3,987,981
Index Size: NULL
Catalog size is > 1 GB
I have some experience with FT Indexing, but I've never seen this before. The unique index for the table is an auto-incrementing identity. It's not defined as a PK, it's a Unique Index.
Any ideas on why the Unique Key Count is 0 and what I need to do? Thanks for any input.
February 20, 2012 at 1:33 pm
sometimes the index gets hung up, especially if multiple FT index are rebuilt at the same time.I found that I needed to separate them by like 30 min and manually rebuild them.
Usually you need to rebuild them and it goes away
are running 2005 or 2008?
February 20, 2012 at 1:44 pm
I ended up moving the catalog and index to a different disc in order to ease the i/o load. It's been populating without problems since.
Thanks for the reply.
February 20, 2012 at 2:07 pm
you probably already have these but just in case, here a couple of scripts if it get hung again
also be aware if this is 2005 it does not use sql server statistics, the query plan using a best guess of 10,000 rows. you will see that if you go to plan execution. Under est it will appear. This can cause a bad query plan. a lot of time you have to you a force query plan in order to fix the problem
select c.name,t.name,change_tracking_state_desc,crawl_start_date,crawl_end_date,*
from sys.fulltext_indexes i
join sys.fulltext_catalogs c
on i.fulltext_catalog_id = c.fulltext_catalog_id
join sys.tables t
on i.object_id = t.object_id
join sys.indexes idx
on i.unique_index_id = idx.index_id
and i.object_id = idx.object_id
order by c.name
select 'EXEC sp_fulltext_table ' + name + ' ,stop_change_tracking' from sys.tables
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply