Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
 Posted - 2011-07-07 : 09:19:51
|
| I have been doing quite a bit of research of scaling up Fulltext population on SQL Server 2008 (see my question here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=162673[/url]) and I am noticing something that I have never realized before. It seems that periodically rebuilding a fulltext catalog is completely unnecessary and it would be sufficient to simply have daily incremental or update indexing on the fulltext index, while running a REORGANIZE on the catalog once a week. Apparently, a REBUILD is only required if there are fundamental changes to your catalog, such as a change in the stoplist or wordbreaker.I cannot find any definitive sources to confirm this, but what does your experience suggest? If this is true, it would take away a major source of pain that we are having at the moment.ThanksOS |
|
|
muratos
Starting Member
22 Posts |
Posted - 2011-07-08 : 11:59:42
|
| I don't have enough info specifically on fulltext but if we are talking about indexes in general, rebuilding is required when there is excessive fragmentation which can result in performance problems. There should be a dmv to see fulltext index fragmentations as well.Generally, these rebuild operations are made at quiet night hours daily 04:00 for example, only on a predefined % fragmented indexes. |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2011-07-10 : 05:04:08
|
| Hi there and thanks for your input. It's a good point and I will try to monitor the fragmentation levels to see if re-organize is sufficient.There is no DMV for this, but you can query the system catalog to do tell you how many file fragments each fulltext index has:SELECT OBJECT_NAME([table_id]) AS TableName, COUNT([fragment_id]) AS FragmentsFROM sys.fulltext_index_fragmentsGROUP BY OBJECT_NAME([table_id])The guidelines seem to be that once your fulltext index exceeds 30-50 fragments, you should run a REORGANIZE on the index. While the concepts of rebuild vs reorganize seem to be quite similar for both regular and fulltext indexes, the implementation is quite different and hence my question.Running a weekly re-organize on our fulltext index takes less than 15 minutes. A rebuild, on the other hand, takes 2 days. Which is why I was trying to determine if there is any need to run a rebuild at all. At the moment, I have disabled the rebuilds and replaced them with reorganize. I will monitor the fragmentation levels (and consequently, fulltext search speeds) for the next few weeks and let you know how it goes.ThanksOS |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2011-11-15 : 03:18:02
|
| Just a quick update to let you know that we've been running this setup for the last three months and it seems to be working perfectly. The FT index will usually be 20-100 fragments by the end of the week and the REORGANIZE manages to bring this down to one. Query speeds get slightly slower by the end of the week, but they bounce right back after the REORGANIZE. Overall, the solution seems to be working fine.Hope this helps somebody.OS |
|
|
|
|
|
|
|
|
Fulltext rebuild vs reorganize