You are currently browsing the daily archive for April 20th, 2008.
Here is a summary of the new Full Text System Views in SQL 2008.
sys.fulltext_stoplists
A list of STOPLISTS in your database. STOPLISTS are database specific. STOPLISTS replace Noise Word Files.
sys.fulltext_stopwords
This will show you you’re STOPWORDS. You can filter this on the language and or stoplist of your choice. STOPWORDS are individual words that make up a STOPLIST and replace Noise Words in previous versions of SQL Server.
For more information on STOPLISTS, check out my SQL Agent’s post at:
http://sqlagents.wordpress.com/2008/04/20/noise-words-have-been-silenced/.
sys.fulltext_index_fragments
This is a great view; this will display a row for each full-text index fragment. I have posted before about this but basically if you see that you have between 30-50 fragments in a table you should be thinking of reorganizing (REORGANIZE) your fulltext index.
For a clear picture, use this query:
SELECT
OBJECT_NAME([table_id])
AS TableName,
COUNT([fragment_id])
AS Fragments
FROM
sys.fulltext_index_fragments
GROUP
BY
OBJECT_NAME([table_id])
HAVING
COUNT([fragment_id])
>=30
Well, this is the first of my blog posts about my trip to Seattle. This is not much more than an introductory hello from me in Seattle. I arrived with a stinker of a cold and due to sinus issues during the descent into Seattle I am currently, largely, deaf.
Not a great start to a week of intensive testing with iFTS and SQL Server 2008!.
As a company we rely on Full Text to delivery and with the advent of 2008 , it seems to work differently to how we expect. Therefore, the purpose of this visit is to ensure that 2008 can meet our businesses needs and what changes we should be thinking of implementing to make sure this happens.
In particular , I shall be testing the differences between the different CHANGE TRACKING methods of the full text catalog and the way in which joins to non-full text tables have been given a performance boost. We have made a lot of changes to our indexes (in SQL 2005) to ensure as much as possible is tokenized to negate the need for external joins. However, this does mean that our quieries use multiple ISABOUTS and WEIGHTing to bring back relevant results.
SQL 2008 is potentially a lot different. Future posts will be based on my work this week with Microsoft.
