May 3, 2002 at 5:43 pm
We have a Sql 7 server with 912MB of RAM. Sql 7 uses 830MB of this RAM with, on average 25 page faults/sec. (Numbers from Perf Mon > Process > Working Set). Add in the other processes running on the machine and PerfMon shows 6MB free RAM! Should I restrict Sql from gobbling up all the memory? Say to 775MB? Impact on performance?
TIA,
Bill
May 3, 2002 at 7:14 pm
Only if you have to do it to keep your other processes running. I'd recommend adding more RAM.
Andy
May 3, 2002 at 11:31 pm
Andy,
How will adding more RAM help the situation? It's my understanding that Sql will gobble up, if not restricted, most of the available RAM on a server. So putting more RAM on a box gives Sql a larger meal and we may even end up with the same amount of available RAM on the box after adding RAM.
Bill
May 4, 2002 at 5:45 am
SQL Server is going to try and dynamically grab enough memory to load databases in memory. It'll also need to reserve some memory for execution plans and user connection settings, among other things. How much memory really depends on the size of your databases and the complexity of the stored procedures.
Restricting the memory will keep SQL Server from gobbling up the memory and you'll probably see the Pages/sec drop below 20 if it is that high. However, this will impact performance on the SQL Server, but it's hard to say how much with what you've given here.
Also, Page Faults/sec tracks both hard faults (paging to disk) and soft faults (paging in memory). Having page faults/sec > 25 is not necessarily a sign of a bottleneck. Having pages/sec (which only tracks hard faults - paging to disk) > 20 is. However, I'll give you the 6 MB free is a sign that memory is your bottleneck. Microsoft indicates having < 4 MB free is a sign of a bottleneck in the Windows 2000 Resource Kit (Server), but most think that's pushing it a little too close.
Is this SQL Server box sharing time with other applications on the system?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
May 4, 2002 at 7:43 am
I agree with Brian and would add that adding memory to SQL does not always give a large performance gain as it depends on what the bottleneck is. However the big way to determine if RAM is needed is to look at the cache hits for SQL Server, if the number is low then it has to exchange data in memory for data from the hard drive which increases disk reads.
See "Identifying Bottlenecks" in BOL.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 4, 2002 at 7:47 am
Just to follow up - adding memory would let you preserve current performance levels or even enhance and still let you set aside more memory for server processes. I rarely find trying to use less memory to be a good answer and conversely adding memory never hurts anything. When it was expensive, you had to work it hard. With current prices I'd say add more. Not scientific, just practical (to me anyway!).
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply