Full Text Search Follies
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rpearl/2819.asp
-
Wow, a majorly good article, I faced similar problems a while back but on 2000 (fingers crossed we dont get something similar on 2005 when we upgrade at the end of the month, testing was fine so here's hoping).
Anyway, I just wanted to say welcome to the club of people laid low by FTS
-
Great article. We are in the planning stage of upgrading databases to SQL2K5 and several have FTS... so thanks for the information. Hopefully now I won't have the same issues
David
-
Un like sql 2000 FTS on SQL 2005 is a much more capable beast, but that also means it is much more capable of taking all the resources. You need to configure you SQL and Full text service to work together.
In your situation it would suggest that the correct ifilters are not installed, this is possibly due to the way that iFilters are registered in SQL 2005.
See http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
As for the DAC. it is enabled but not for remote connections, and there is a valid reason. You can only have one DAC, if someone is connected remotely with the DAC, you have no way of findging out who is connected. Which measn when your server is being screwed you can't connect.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Let me comment on a few problems you saw so that anyone who gets into your situation can benefit of reading this post.
1. CPU usage goes to 100% after upgrade(whether it is attach, restore, or in-place upgrade) due to full-text indexing.
The idea behind this behavior is that we want to utilize as much resource as possible to finish rebuild full-text indexing so it can be back online quickly. It is aimed for the application which heavily rely on full-text functionality(those users quite often have large number of full-text catalogs and large full-text catalog in their databases. They are more likely want to use 100% CPU.). From your experience, it sounds like we made a bad decision about the default behavior. Maybe it is better to let server rebuilding index slower by default and for advanced full-text user let them tune the rebuild performance. Once thing you can do to alleviate your situation: sp_fulltext_service ‘pause_indexing’, 0 will pause all full-text index on the box until you resume it or restart sqlserver service. Even though we use 100% CPU, but we should never used up all memory on the server during the rebuild. The page fault you saw is probably due to the search service use OS file mapping to read and write index files. Those page faults are due to I/O operation.
2. Upon further review of our logs, we noticed this: "Warning: No appropriate filter was found during full-text index population for table or indexed view '[MyDB].[dbo].[MyTable]' (table or indexed view ID 'X', database ID '1784393426'), full-text key value 0x0001E119. Some columns of the row were not indexed" So, it appeared that we had some sort of corruption with the full-text index, and the problem likely to be with the Filter that is not available for FTS.
The failure is due to third party filter installed on OS will not be loaded by default due to security concern. Your table must have used some file format which is not supported out of box in SQL 2000. To fix this problem, you will need to turn on sp_fulltext_service ‘load_os_resources’, 1. If the third party filter is not signed, you will also need to turn off signature verification in order to use it. Use sp_fulltext_service ‘signature_verification’, 0 to turn it off.
3. Backup failed due to full-text offline.
Full-text catalog modeled as a database file in SQL 2005, that’s how user get integrated manageability improvement. User can backup/restore individual full-text catalog, detach/attach database with full-text catalog, include full-text catalog in db mirroring setup, etc. This also post a new restriction to the user that if full-text catalog is offline you cannot backup the whole database(This is same as if a database file is offline, you cannot take full database backup.) . While you restoring SQL 2000 backup, since full-text catalog never is part of SQL 2000 backup. You have no way to relocate the full-text catalog. So after restore, it is very important to check sys.master_files to see if all your files are online. For full-text catalog, if the catalog path point to a invalid path, it will be offline. You can use alter database modify file to correct the path and rebuild the catalog to bring it online. Or simply drop the catalog. In SQL 2005 SP2, we have improvement error reporting for this kind of backup failure so user can fix or drop their full-text catalog before full database backup.
4. Full-text catalog 'AdiosMycatalog' has been lost. Use sp_fulltext_catalog to rebuild and to repopulate this full-text catalog" Lost!?
I would like to know more detail about this problem. First, we don’t have “Lost” error message. Please give me the real error message so I can see if there is a bug. Second, a full-text catalog or index can be drop when database is not full-text enabled, full-text is not installed, or full-text catalog is offline. The fact you can drop all fulltext catalogs by using detach without keep fulltext catalogs tells me that drop should go through.
Contact me if you have more issues. I will be happy to help you resolve them.
Thanks,
Jingwei Lu
Microsoft SQL Server
-
Robert,
I am sorry you found this troubles during upgrade; however, Jingwei is right in his comments.
We would like to get more details of your problems as your machine should not hang due population, even though if we use as much CPU as we can (possible bug?). As Jingwei mentioned, we use as much CPU as we can to accomplish order of magnitude better population performance in FTS 2005 than in FTS 2000. Also, we do not have any record of such error message about "Lost" FTCatalogs, try to give us more details please.
So far, our customers are extremely happy with FTS 2005 and glad that our population is that fast now. Note also that part of your pain was caused by the fact we now allow a full integration between the FTCatalogs and the DB (backup/restore, etc..), which is a huge enhancement for our customers (eventhough it caused so much pain in your case).
Be sure we are working hard here to find out what went wrong with your experience and in improving some aspects you hitted, so customers with non critical FTS dependencies can upgrade easier even when having FTCatalogs existing in their system.
Please feel free to contact me or Jingwei for more personalized and direct assesment on your FTS problems.
Thanks.
-
Fortunately I have not had the "experience" you had, but I wanted to let you know that I thoroughly enjoyed the article. I only wish that most other "technical" articles injected the same enthusiasm, warmth and humor. We have all been there, one way or another.
-
I could not agree more with Brad's comments above. Well done.
-
Thanks for article. Im not sure where to begin but we had some databases for sharepoint and wanted to use the search feature within sharepoint application which is the full text search but unfortunately the Service Full Text Service has been corrput and there are no registry settings. We are planning for a complete install of SQL and restore master but this seems way over kill to me. If you know of of anything to get the FTS back up without a restore im all ears. It seems it got corrupt around the same time that sharepoint was trying to be configured with full text indexing....
-
I understand the logic behind allowing the full text population to use up 100% of the CPU for an extended period, but what if we would NOT like to use 100% CPU during peak business hours -- regardless of whether or not the index is up-to-date?
Is there a way to throttle down the amount of resources the full-text engine will use or is it just an ON or OFF type of affair?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply