Hi,

I have created a sample project to verify the full-text improvements from
SQL 2005 to SQL 2008.

Most queries run much imrpoved, but occasionally I get queries that never
finish (say, after 40 minutes).

Basically, the queries that run good and bad are the same, but the bad ones
typically have more criteria in the CONTAINS filter.

Query A = runs good:

select count(*)
FROM myMessages m  WITH (NOLOCK)  
WHERE  m.im__id IN(74)  
AND contains (msg_body, 'test')
AND (  m.msg_date >= 'Jun  1 2008 12:00AM'  AND  m.msg_date <= 'Jul 30 2008
11:59PM'  )  

 |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
      |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
           |--Hash Match(Left Semi Join,
HASH:([m].[im_message_id])=(FulltextMatch.[docid]))
                |--Index
Seek(OBJECT:([smarsh].[dbo].[myMessages].[IX_myMessages_id_msgdate] AS [m]),
SEEK:([m].[im__id]=(74) AND [m].[msg_date] >= '2008-06-01 00:00:00.000' AND
[m].[msg_date] <= '2008-07-30 23:59:00.000') ORDERED FORWARD)
                |--Table-valued function
               
Query B = never finishes:
               
select COUNT(*)
FROM myMessages m  WITH (NOLOCK)  
WHERE ( (  m.im__id IN(226)  )  )
and contains (msg_body, 'pink OR red OR blue OR purple OR fred OR barney OR
susie')
AND (  m.msg_date >= 'Apr 25 2007 12:00AM'  AND  m.msg_date <= 'Jun 23 2008
11:59PM'  )  
AND (  m.privileged = 0 )

 |--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(int,[Expr1073],0)))
      |--Stream Aggregate(DEFINE:([Expr1073]=Count(*)))
           |--Hash Match(Left Semi Join,
HASH:([m].[im_message_id])=([Expr1070]))
                |--Nested Loops(Inner Join, OUTER
REFERENCES:([m].[im_message_id], [Expr1072]) OPTIMIZED WITH UNORDERED
PREFETCH)
                |    |--Index
Seek(OBJECT:([smarsh].[dbo].[myMessages].[IX_myMessages_id_msgdate] AS [m]),
SEEK:([m].[im__id]=(226) AND [m].[msg_date] >= '2007-04-25 00:00:00.000' AND
[m].[msg_date] <= '2008-06-23 23:59:00.000') ORDERED FORWARD)
                |    |--Clustered Index
Seek(OBJECT:([smarsh].[dbo].[myMessages].[PK_myMessages] AS [m]),
SEEK:([m].[im_message_id]=[smarsh].[dbo].[myMessages].[im_message_id] as
[m].[im_message_id]),  WHERE:([smarsh].[dbo].[myMessages].[privileged] as
[m].[privileged]=(0)) LOOKUP ORDERED FORWARD)
                |--Compute Scalar(DEFINE:([Expr1070]=CASE WHEN
FulltextMatch.[docid] IS NOT NULL THEN FulltextMatch.[docid] ELSE [Expr1008]
END))
                     |--Merge Join(Full Outer Join, MANY-TO-MANY
MERGE:([Expr1061])=(FulltextMatch.[docid]), RESIDUAL:(FulltextMatch.[docid] =
CASE WHEN FulltextMatch.[docid] IS NOT NULL THEN FulltextMatch.[docid] ELSE
[Expr1006] END))
                          |--Sort(ORDER BY:([Expr1061] ASC))
                          |    |--Compute Scalar(DEFINE:([Expr1061]=CASE
WHEN FulltextMatch.[docid] IS NOT NULL THEN FulltextMatch.[docid] ELSE
[Expr1006] END))
                          |         |--Compute
Scalar(DEFINE:([Expr1008]=CASE WHEN FulltextMatch.[docid] IS NOT NULL THEN
FulltextMatch.[docid] ELSE [Expr1006] END))
                          |              |--Hash Match(Full Outer Join,
HASH:(FulltextMatch.[docid])=([Expr1063]))
                          |                   |--Table-valued function
                          |                   |--Compute
Scalar(DEFINE:([Expr1063]=CASE WHEN FulltextMatch.[docid] IS NOT NULL THEN
FulltextMatch.[docid] ELSE [Expr1004] END))
                          |                        |--Compute
Scalar(DEFINE:([Expr1006]=CASE WHEN FulltextMatch.[docid] IS NOT NULL THEN
FulltextMatch.[docid] ELSE [Expr1004] END))
                          |                             |--Hash Match(Full
Outer Join, HASH:(FulltextMatch.[docid])=([Expr1065]))
                          |                                  

                          |                                  |--Compute
Scalar(DEFINE:([Expr1065]=CASE WHEN FulltextMatch.[docid] IS NOT NULL THEN
FulltextMatch.[docid] ELSE [Expr1002] END))
                          |                                      

NULL THEN FulltextMatch.[docid] ELSE [Expr1002] END))
                          |                                            

                          |                                                

                          |                                                

NULL THEN FulltextMatch.[docid] ELSE FulltextMatch.[docid] END,
[Expr1030]=CASE WHEN FulltextMatch.[docid] IS NOT NULL THEN
FulltextMatch.[docid] ELSE FulltextMatch.[docid] END))
                          |                                                
    |--Hash Match(Full Outer Join,
HASH:(FulltextMatch.[docid])=(FulltextMatch.[docid]))
                          |                                                
         |--Table-valued function
                          |                                                
         |--Table-valued function
                          |--Table-valued function

I see some documentation about forcing a optimize to possibly make the Query
B act like Query B, but I don't see examples - and also if others have run
into the same issue.

http://msdn.microsoft.com/en-us/library/cc721269(SQL.100).aspx

Anyone use this hint with luck, or have alternative ways around a similar
problem they were having?

Thanks,
Robert Towne