Full text query slow on first run I have SQL Server database which has size of 65GB and 6 million rows. We have essays in tables and the average size of an essay is 450 words. When I run a query for the first time, it takes 3-10 seconds to show me the results. On running same query again, it is in less than 1 second (very fast).

Please let me know how to optimize it so I can get queries in less than 1 sec.

Query example:  

SELECT top 1 * FROM tblContent WHERE CONTAINS(pagecontent,'"increasing the likelihood"')


Here is detail of server(VPS):


OS: Windows Server 2012
SQL Server 2012 Enterprise (Trial)
Processor: set to 0
Memory: set to 0
RAM: 4GB
Processor: QUAD 4Ghz
When I run a query for the first time, it takes 3-10 seconds to show me the results. On running same query again, it is in less than 1 second (very fast)


The second time you run the query, it is likely that data and index pages for the relational tables are coming from SQL Server's cache rather than being read in from disk. Likewise, the full-text portion of the processing will also benefit from caching.

As the comments on the question indicate, 4GB does not seem like very much memory for a SQL Server managing a 65GB database. More physical memory will allow SQL Server to cache a greater proportion of your relational tables and full-text indexed data.

The exact amount you need depends on the nature of your workload, and the response times your users expect. I would imagine that expanding the memory even modestly (say to 8 or 16GB) would result in a very noticeable performance improvement, and more predictable response times overall.

You would also want to configure the maximum memory for SQL Server to ensure there is enough for Windows as well. See the following links for more information on memory (and other important details):

How much memory does my SQL Server actually need?

Troubleshooting SQL Server: A Guide for the Accidental DBA (free eBook)