TOP 2 faster than TOP 1 in Sql Server?

Question

Hi,

we have a table with +- 500k rows in Sql Server 2005 database and one of its columns has a full-text index.

We were doing some tests and found that SELECT TOP 1 ... WHERE CONTAINS(fullTextColumn, 'anyValue') was taking more than two minutes to return. However the same select, but with TOP 2 instead of 1 return in a few seconds.

Any idea on why this happens?

Thanks!

Edit: Some more information about the problem. On TOP 1 query plan Sql use a Remote Scan while in TOP 2 it uses Index Seek. Very odd.

Edit 2: These are both Execution Plans from Sql:

TOP 1

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((1)))
       |--Nested Loops(Left Semi Join, WHERE:([ECRReload].[dbo].[TECR_PRODUTO_COMPLETO_VENDAVEIS].[idProduto] = [Full-text Search Engine].[KEY]))
            |--Index Scan(OBJECT:([ECRReload].[dbo].[TECR_PRODUTO_COMPLETO_VENDAVEIS].[ix_tecr_produto_completo_vendaveis01]))
            |--Remote Scan(OBJECT:(CONTAINS))

TOP 2

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((2)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Full-text Search Engine].[KEY]))
            |--Remote Scan(OBJECT:(CONTAINS))
            |--Index Seek(OBJECT:([ECRReload].[dbo].[TECR_PRODUTO_COMPLETO_VENDAVEIS].[ix_tecr_produto_completo_vendaveis01]), SEEK:([ECRReload].[dbo].[TECR_PRODUTO_COMPLETO_VENDAVEIS].[idProduto]=[Full-text Search Engine].[KEY]) ORDERED FORWARD)

Answer 1

I´m not 100% sure if I can say that this is fixed, but it seems to be. Last night our DBA rebuilt the FullText catalog and reorganized and it started to work. Both queries now run with less than a second.

I can´t explain why, but it is working.