You are currently browsing the category archive for the 'iFTS' category.
I was lucky enough to visit Microsoft during the CTP period and was testing out integrated full text search in 2008. An issue we experienced was that full text can be slow when there is a high number of updates to the index and is caused by blocking on the docidfilter internal table.
It appears to happen if:
- You have AUTO tracking on your full text indexes; although we changed it to manual and still had this issue.
- You experience Full-text queries taking a long time to execute; normally when updates are happening at the same time so you might only see this in production.
- One or more of your queries are complicated or take some time to complete.
You can check to see if your system is slow due to this by:
- SELECT * FROM sys.dm_os_wait_stats statement , it shows very high wait times some of the locks.
- Running Sp_who2; it should consistently show that the full-text gather is blocking full-text queries and, in turn, is being blocked by the queries.
The current work around for this issue is to use a global trace flag microsoft kindly enabled on the RTM build. To use this, type the following Transact-SQL statement:
DBCC TRACEON (7646, -1)
This has a minor side-effect.. which may or may not be important in your organisation. Certainly in ours, it is not. Your full text result may be ever so slightly out of date – e.g. you might return or not return a particular document from the index. However, this issue relates to the dirtyness of an index in terms of 10’s of milliseconds. Important? Unlikely….
I believe there is a knowledge base article on this.
Simon Sabin posted about this a few weeks back but I hit the issue he described today so I wanted to write about it and what I had to do.
The issue I am talking about is related to the account Full Text host uses to perform out of process tasks. This account, in CTP6, is created when CTP6 is installed. However it will use whatever default expiry conditions you have within your infrastructure.
Now, all this was fine when we first did some testing. However, coming back to it today I needed to drop some full text indexes and create them again. I dropped them but when I tried to create them, the full crawl wasn’t working. After checking the error log, it was clear that root cause of this was the FDHost Account’s password had expired….
On our system, the user name was called FDH$MSSQLSERVER. As per Simon’s post, the first step is to change the password on your Server and set any expiry details you need to (e.g. not expire). Next you will need to sync this with SQL Server. There is Stored Procedure for this, which can be used as follows:
EXEC sp_fulltext_resetfdhostaccount
@username = ‘FDH$MSSQLSERVER’,
@password = ‘Katmai2008′
This worked nicely. However, to get the full text index to start to work you need to give it a little kick. You can RESUME the population of the index by running:
ALTER
FULLTEXT
INDEX
ON
[MyFullTextTable]
RESUME
POPULATION
If you are not sure of the indexes that haven’t been populated yet and want to resume all of them , you can run this simple query to generate the script needed. Then simple run the script that gets returned.
SELECT
‘ALTER FULLTEXT INDEX ON ['
+
OBJECT_NAME(object_id)
+
'] RESUME POPULATION ‘
FROM
sys.fulltext_indexes
WHERE has_crawl_completed = 0
By the way, this issue has been fixed in the RC0 (refresh) release due out in May. It will now ask you to specify an account to use for the FDHost process. Upgrading an existing install will also prompt you to change this. However, if anyone is using CTP6 currently you might have the same issue I experienced today.
Having not long arrived back in the UK and with good intentions to blog like mad during my trip to Seattle, my health whilst out visiting Microsoft unfortunately wasn’t permitting me to do much else after the work I was there to do.
I will be posting a more concise post later this week about the time I spent with iFTS. However, I would like to say it is certainly a version 1.0 product in it’s current CTP6 form.
We (as a company) will have issues replacing our Yukon (2005) boxes with 2008, partly due to the way we have improved performance in 2005; crazy, I know. So, if you use multiple ISABOUT terms, have laden your full text indexes with TOKENs to improve overall performance in 2005 you will have potentially a chunk of changes to your existing database(s) awaiting you.
It may be that we use full text in a way unlike any other company in the world… but this is unlikely.
I will also post some information about ISABOUT and WEIGHT terms, there isn’t too much on the internet about the usage of ISABOUT and how the WEIGHT affects the ranking so I will help to improve that.
The fantastic developers at Microsoft, specifically the full text search team , I am sure will be working like mad to resolve issues and improve the overall performance. There is a lot to be excited about in SQL Server 2008 and iFTS does have huge potential.
This may be only relevant to 2008 but if you find Fulltext index creation taking a long time to complete you may be affected by a potential bug in CTP6. If you are experiencing this problem you can try running the following statement prior to the creation of the index. This will disable the checking of the OFFICE filter signature.
sp_fulltext_service
‘verify_signature’, 0
On machines we have here, the signature does not appear to exist for the dll and therefore the crawl is taking a long time to complete.
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.
Simon Sabin has found a bug in CTP6 of SQL Server 2008. If you use iFTS and have experienced issues as described by Simon please follow his suggestions. His post has been summarised here:
iFTS does some stuff out of process for security and reliability in CTP6 the run this process under a local user account FDH$<instance name> by default.
However this accounts password may expire or even may be set to change on next logon (depending on domain settings etc).
If you find iFTS is not working then go into Local Users and Groups. Select the FDH$ account and check the settings. If you need to change the password there is a stored procedure called sp_fulltext_resetfdhostaccount. You can use this to sync the password in SQL once you have changed it in the User manager.
You can read his full post here > http://sqlblogcasts.com/blogs/simons/archive/2008/04/11/sql-server-2008-breaking-bug.aspx
All being well I should be flying off to the States within the next couple of weeks to spend some time with Microsoft and specifically iFTS. If anyone has any burning issues or questions they would like to raise, please respond to this blog. My time is pretty much focused on our company’s application and it’s use of full text but there will be opportunity for side meetings regarding other aspects of SQL Server 2008.
I shall be blogging whilst in Seattle (connection permitting) and preparing some larger posts specifically on iFTS on my return.
One neat feature of SQL Server 2008 is the ability to see how fragmented your fulltext indexes are. By running the following you can see how many fragments you have.
SELECT * FROM sys.fulltext_index_fragments
The rule of thumb is that if you have between 30-50 fragments per 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
As SQL Server 2008 CTP6 is now with us I will be doing a post on the new integrated full text search (iFTS) shortly. iFTS includes a number of key changes including support for STOPLIST, mixed query performance, thesaurus improvements and of course full text indexes are stored and maintained inside SQL Server now. There is so much more to write about this and I intend to cover one or two features in separate posts.
