Gigantic Bug in SQL Server 2005 Full Text Query

Posted Wednesday, March 26, 2008 3:11 PM by Nathan Zaugg

You know you've done something wrong when it takes 30+ seconds to run a full-text query.  The most annoying part of this bug is the fact that it is something very small and inconsequential that "triggers" the bug.  It's a lot like an murder investigation where the killer turns out to be a Nun. 

Here is the setup---

  1. I have a full text index on a text field in my database.  This table happens to be our Phrase table (for data localization)
  2. I do a very simple full text search on that field in a SQL Server proc
  3. I want to test my proc so I add some fixed input
  4. Setting a value after the fact causes my full text to be *very* slow!!!

Here is the code

ALTER PROCEDURE [dbo].[AdvancedTrackSearch_TEST] ( @TrackTitleCrit xml ,@TrackDescCrit xml ,@RecordLabelCrit xml ,@CategoryCrit xml ,@ComposersCrit xml ,@TrackDuration int ,@TrackDurationOperator int ,@LangID int ,@PageSize int ,@PageNumber int ,@UserID int ) AS BEGIN   -- Temporary input DECLARE @TrackTitleStr nvarchar(2000) SET @TrackTitleStr = '"booty poppin"'   -- *********************************** -- * THIS LINE CAUSES THE PROBLEMS * -- * BY SIMPLY REMOVING THIS LINE * -- * THE QUERY TIME WILL GO FROM 30+ * -- * SECONDS TO LESS THAN 1 SECOND! * -- *********************************** SET @LangID = 66     DECLARE @TitlePhrases TABLE ( PhraseID int, DictionaryID int )   INSERT INTO @TitlePhrases SELECT PhraseID, DictionaryID FROM [dbo].[Phrase] WHERE --PhraseID NOT IN (SELECT PhraseID FROM @TitlePhrases) CONTAINS([TEXT] , @TrackTitleStr) --[Text] like @Phrase AND LanguageID = @LangID   -- Check the output SELECT * FROM @TitlePhrases   END   -- ****************************** -- NOW WE TRY TO EXECUTE THE PROC -- ****************************** DECLARE @return_value int   EXEC @return_value = [dbo].[AdvancedTrackSearch_TEST] @TrackTitleCrit = NULL, @TrackDescCrit = NULL, @RecordLabelCrit = NULL, @CategoryCrit = NULL, @ComposersCrit = NULL, @TrackDuration = NULL, @TrackDurationOperator = NULL, @LangID = NULL, @PageSize = NULL, @PageNumber = NULL, @UserID = NULL   GO  

It seems so simple and stupid but setting the LangID (even if we pass null into the actual query) causes the query to take a substantially longer time doing the full text search.

I hope someone finds an explanation!

--Nathan Zaugg

 

UPDATE:

As a matter of fate, me and my friend Phil Gilmore stumbled on the answer.  The trick is to "SET ARITHABORD ON" for one of the first things that you do in the query.  This is usually linked to arithmetic exceptions and overflows, but for some reason with out it there is little chance your query will perform.  If you look at the difference between the execution plans before setting that variable vs. after you can see that the execution plan changes a lot!  After ARITHABORT is ON the execution plans are again identical!  Check out my post on MSDN forums. 

Filed under: