Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

closed as too broad by Jon Seigel, RolandoMySQLDBA, StanleyJohns, Mark Storey-Smith, dezso Jul 30 '13 at 9:17

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

5  
RAM: 4GB running a 65GB database ... Move to a better hardware ! –  Kin Jul 29 '13 at 14:09
1  
Or just buy some RAM. –  Katherine Villyard Jul 29 '13 at 17:21
    
How much RAM is required for database of 65GB. The SQL Server 2012 enterprise need 2GB RAM(recommended) but I have 4GB –  Aslam Khan Jul 29 '13 at 18:59
1  
Do you happen to have a table definition at hand? Then please insert it into your question. And how about the indexes? Do you have any? Also, the query plan could help a lot. If you tell us these details I am sure that the public will vote for reopening your question. –  dezso Jul 30 '13 at 9:16

1 Answer 1

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)

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.