SQL Server Performance Forum – Threads Archive

RAM ratio for SQL Server and OS

Hi, Is there any recommendation for the memory ratio for sql server and the os?
I currently have one running on windows 2000 adv server with 8GB of ram.
I’ve set the max memory for sql server at 7.5 GB.
It’s a high volume database, and i get intermittent performance problems, so i’m wondering if the ratio for the memory could be the cause of these performance problems, among others… recommendation, anyone?
thanks! Raymond

The following equation provides a way to estimate what the initial SQL Server memory setting should be on a computer that is dedicated solely to SQL Server. y=(.95x-22)*512
In the equation above, ‘y’ is the sp_configure memory setting in 2-KB pages and ‘x’ is the amount of RAM on the computer in MB. You can effectively use this equation on computers that have an amount of RAM ranging from 64 MB to 2 GB. What is the current SQL edition installed?
Its better to leave memory settings as dynamic if there are no other applications sharing the memory & h/w resources. Also enable PERFMON to collect counters of memory by looking at two counters,
Memory: Available Bytes
Memory: Pages Faults/sec For information review thishttp://www.sql-server-performance.com/sg_sql_server_performance_article.asp article. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya, based on the equation you gave me and with the 8GB ram i have installed,
the value i get is:
y = ((0.95 x 8000)-22) * 512
= 3879936 So the amount of RAM i should reserve for sql server is about 4 GB?
I’ve also done a perfmon, the working set for the process sql server is 121344000 bytes, which is essentially 121 MBs.
The sql server:memory manager otal server memory counter however, shows 7.5 GB.
How do i relate the working set counter with the total server memory counter? Does it mean that my sql server is not using all the memory that is allocated to it, since the working set value is so low?
Raymond

Oh, i’m using sql server enterprise edition with service pack 3a. it is running on a dedicated server. i remember reading somewhere that the server will not be able to dynamically manage memory once AWE is turned on? is this true? Thanks! Raymond

Is the server stressed currently, if not watch closely for the counters defined. http://www.sql-server-performance.com/sg_sql_server_performance_article.asp a good artilce about setting up h/w tuning and performance. As I said earlier you can experiment by leaving the memory settings and setting min & max values in realtime environment. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>