title: Sql Server 2008 FullText Optimization
body: <p>I am playing a little with fulltext indexes in Sql Server 2008. I created the index, catalog and full populate it.
  My table has almost 400,000 records. My full text index is defined to a varchar(Max) column (lets call it Text for now). I am executing the follow query:</p>
  
  <p>select * from MyTable
  where contains(Text, 'house').</p>
  
  <p>This query returns almost 20,000 records in 14 seconds. I think thats very slow. But, when i execute this query:</p>
  
  <p>select Count(*) from MyTable
  where contains(Text, 'house')</p>
  
  <p>It takes only 1 second to show the result.</p>
  
  <p>I've been looking and as far as I can see, the execution plan is the same for both queries.
  Why is Sql Server taking that much to show the fist query result?</p>
  
  <p>What I have already did:</p>
  
  <p>I bought a SSD and put both mdf and ldf on this disk. But when I execute the first query, i can see that logs and a tmp database are being created at my HDD (not in the SSD). My SSD is D: and all temp files are being created at C:.</p>
  
  <p>Is this taking so long because sql needs those files to populate the enterprise manager result grid? is there anything i can do to optimize the Database? I really need the queries to run in less than 2 seconds.</p>