40 Go database with 2 Go Ram?

Hi,
I got some performance problems. I have a 40 Go database on a dual core CPU
with 2 Go of RAM.
Sometime it's fast, sometime it's slow. Some request take more than 20
seconds to complete. And if I re-run them right after, it executes in a flash
even if I changed some paramaters.
For instance, I have a 10 Go table with indexes for a total of 15 Go. Other
tables where I run the most queries are 150 Mo with a 64 Mo index.
Since the performance is not always the same, should I conclude this is a
memory problem? How to know what is the right size of RAM? I can't put 40 Go
RAM or I'll go out of business...!
Any idea would be appreciated.
Thanks
StephaneThis may be a sign that you need better indexing. The slow/fast is what
happens when the server needs to read in and cache your new data, which got
flushed because it needed the space for old data. But it's hard to give any
general rule for a reasonable ratio of data to RAM. A ten gb table with 5gb
of indexes, still doesn't say whether 2gb is enough RAM. But remember, since
the OS typically takes half a gb to 1gb, going from 2gb RAM on a machine to
4gb, can almost triple your effective cache space, which is usually a good
thing!
Josh
"Stephane" wrote:
> Hi,
> I got some performance problems. I have a 40 Go database on a dual core CPU
> with 2 Go of RAM.
> Sometime it's fast, sometime it's slow. Some request take more than 20
> seconds to complete. And if I re-run them right after, it executes in a flash
> even if I changed some paramaters.
> For instance, I have a 10 Go table with indexes for a total of 15 Go. Other
> tables where I run the most queries are 150 Mo with a 64 Mo index.
> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40 Go
> RAM or I'll go out of business...!
> Any idea would be appreciated.
> Thanks
> Stephane|||> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40
> Go
> RAM or I'll go out of business...!
I agree with Josh's analysis. It's best to perform index and query tuning
to ensure data access is optimal before throwing hardware at the problem.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:55BD6AB5-E9FB-4F80-B58C-92CB26754A41@.microsoft.com...
> Hi,
> I got some performance problems. I have a 40 Go database on a dual core
> CPU
> with 2 Go of RAM.
> Sometime it's fast, sometime it's slow. Some request take more than 20
> seconds to complete. And if I re-run them right after, it executes in a
> flash
> even if I changed some paramaters.
> For instance, I have a 10 Go table with indexes for a total of 15 Go.
> Other
> tables where I run the most queries are 150 Mo with a 64 Mo index.
> Since the performance is not always the same, should I conclude this is a
> memory problem? How to know what is the right size of RAM? I can't put 40
> Go
> RAM or I'll go out of business...!
> Any idea would be appreciated.
> Thanks
> Stephane