Performance delay when a full-text query is executed on a SQL Server with no connection to Internet.

To utilise the FULL-TEXT performance features in SQL Server 2005 the DBA might need to run a full-text query on a SQL Server instance. Occasionally the user might notice a delay in performance to produce the results of full-text query in a timely manner. This issue arises because Full-Text engine for SQL Server uses the word breaker that is related to the query which requires Internet connection to complete the process.

[quote] SQL Server 2005 Books Online:

Word breakers and stemmers perform linguistic analysis on all full-text indexed data. Linguistic analysis involves finding word boundaries (word-breaking) and conjugating verbs (stemming). The rules for this analysis differ for different languages, and you can specify a different language for each full-text indexed column. Word breakers for each language enable the resulting terms to be more accurate for that language. In the case where there is a word breaker for the language family, but not for the specific sub-language, the major language is used. For example, the French word breaker is used to handle text that is French Canadian. If no word breaker is available for a particular language, the neutral word breaker is used. With the neutral word breaker, words are broken at neutral characters such as spaces and punctuation marks.

The word breaker component uses the signature verification that involves a certification revocation check, during this check the process requires Internet connectivity. Due to unavailability of Internet access the query might affect a few seconds delay in providing results by loading the rest of word breaker process. By default the Full-Text SQL engine unloads a word breaker that has not been used for 5 minutes to reduce resource consumption.

Microsoft suggests users use their own discretion in opening an Internet connection during the Full-text query execution so as to avoid any vulnerabilities on SQL Server.  If the SQL Server instance is behind a firewall, install a firewall client, and then open the 80 port that is required by signature verification.


Leave a comment

Your email address will not be published.