Sql-server – Windows Server 2008 Memory Usage with SQL Server 2008

memory usagesql serversql-server-2008windows-server-2008

I have the following kit:

windows server 2008r2
Microsoft SQL Server 2008r2
Dual Quad Core Xeons
52GB Ram

The Ram usage on the server is currently at 98% which is fine, SQL server is happily taking what it needs. What I want to know is how would I gauge how much RAM it actually needs? How do I know when I need to add more or If I can possibly take some away? I have reduced the RAM usage down to 40GB and my disk write/reads are currently at:

Over 5 Minutes of DB Activity
Disk Reads/Sec : Average : 59
Disk Writes/Sec : Average : 95

is this the correct counter to be checking? Im happy with leaving the db server with 40GB of Ram, but I want to know if it still has too much – its a virtual environment, so I can use those resources elsewhere. '

thanks

Best Answer

How do I know when I need to add more or If I can possibly take some away?

CHeck the statistics it publishes on performance counters (how long pages stay in memory, how often it reloads stuff from disc) and check the statistics yhow long it waits for read IO on the MDF files - that simple.

Over 5 Minutes of DB Activity Disk Reads/Sec : Average : 59 Disk Writes/Sec : Average : 95

As useless to gauge anything as the price of the food I ate for lunch today.

Get the Seconds/IO number, that has relevance. When this number increases then - well - your IO subsystem is overloaded. But then - you ahve to be carefull, as RAM is useless to cache transaction log writes for obvious reasons.

Im happy with leaving the db server with 40GB of Ram, but I want to know if it still has too much - its a virtual environment, so I can use those resources elsewhere. '

Pull down to 16gb, use common sense, then go up as IO starts to get slow. Sadly your IO is likely also virtualized so you can not use up the discs proper? Also we have no idea how large your database is (which kind of is an imporatn number) and how large your hot set is (the data activly in permanent use).

I would glo with: * 16gb, watching counters, adjusting as needed.