Slow Text queries in SQL Server 2008

From StackOverflow.Net   (http://blog.stackoverflow.com/2008/10/adde-a-second-server/)

   From the article:

      While many queries are faster under SQL Server 2008, and the tooling is dramatically and indisputably better (intellisense for queries!), there is one downside for us: SQL Server 2008 is slower at full-text search operations than SQL 2005. That’s kind of a bummer because we rely heavily on full text. I saw a glimpse of this in my initial testing, when I had both servers up side-by-side with the same data, running the same queries, to see how much faster it would be (new versions are supposed to be faster, right?). My experiments showed that any full-text queries I tried were inexplicably slower on 2008, but we thought it had to do with different query plans, and was something we could work around.

   StackOverflow admin tried the workaround fix shown below but haven’t seen much improvement .

  

http://sqldev.wordpress.com/2008/09/16/sql-server-2008-full-text-slowness/

  

From the article:

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 tested the workaround for the fulltext query fix.

    On a large client DB, I ran the following query:

UPDATE tbExams

SET BusinessNotes=''

WHERE ExamID=

  Several times and the average execution time 3-4 secs

I applied DBCC TRACEON (7646, -1)

 

And then I ran the query again against different exams and the execution time dropped to 0-1 secs

Additional:  SQL Server 2008 must have AWE enabled to use all of the available memory the OS reports.
                   I assume this is necessary for 32/64 bit.