understanding memory setting | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

understanding memory setting

Hi there, We have SQL server 2000 sp3 Standard Edition, running on win2k sp3. The machine has 6.2GB memory, but only 4 can be used for SQL server since its edition. The server is dedicated to SQL server. Now, the memory setting for the server was "Dynamic": min = min (4MB), max = max, i.e 6.2GB. set working size = 0. In this server, there are a lot of heavy reads from big tables. we changed the memory setting to be fixed on 3GB and set working size = 1. the performance for large extract job was improved significantly. This is, of course, good, but I would like to understand why changing the memory setting to this values makes the improvements. If the machine is dedicated to SQL server why the allocated memory should go down. Also, what is the effect of "set working size " option on this issue. Generally, it seems that when big extracts are involved, setting the memory to a fixed values makes it better. Any explanations and recommendation for the above ? Thanks. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

First of all, SQL 2K standard edition can use only 2GB of memory. Since you have 6.2 GB and the server is dedicated to SQL Server, even your dynamic memory setting should reserve 2GB for SQL Server. And the fixed memory setting of 3GB also uses only 2GB of this memory. So, I don’t see how this change can improve the performance. Were there any other changes along with it ?
You are right. It was set for more than 2GB for nothing because of the edition. but still, once we set the memory to fixed to 3GB (which was actually 2GB), and we set the "set working size" to 1 – I did much better. I have the feeling that what made the differnce is the "set working size". because the system has 6GB memory, and it is Win 2k Advanced, so there is a lot of memory without swapping and the OS can page inside the RAM.
Correct me if I am wrong in this issue. But, beside that, is fixing the memory size, rather than leave it to be dynamic, makes performance better ?
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

If any other applications are sharing the server resources then setting min & max memory on SQL will help, otherwise dynamic usage is ideal to get optimum performance. http://www.sql-server-performance.com/sql_server_configuration_settings.asp &http://www.sql-server-performance.com/sql_server_performance_audit5.asp articles for your reference. 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.
quote:Originally posted by Aviel But, beside that, is fixing the memory size, rather than leave it to be dynamic, makes performance better ?

Yes, but not in your case, where you have plenty of memory and a server dedicated to SQL Server. If you have less memory and if the server is shared with other applications, then fixing the memory to SQL Server may improve performance. So, in your case, it could be the ‘set working set size’ which is improving performance.
Thanks guys. We are going to test this issue, but as ykchakri suggested, it might be the ‘set working set size’. The system has plenty of memory so this makes sense. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Some other question, guys, while trying to understand OS – SQL Server Memory usage. Q1. I have set the memory setting for the SQL server to be fixed at 2GB (Standard Edition on W2k AWE) and set working set = 1.
when I look at the perf monitor I see that the available bytes and the working set bytes are 1.7 GB. where are the other 300MB ?? Q1. the OS system has 6GB memory. What use the OS system does with memory under this circumstances (standard SQL, working size = 1). What is the function of the paging file here. Is the remaining 4GB used at all ? some explanations and links to (understandable) material will be much appreciated.
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

1. Is it standard at all the times? I beleive SQL will reserve that portion of memory for any other process. 2. A region of address space that is currently being used by a process, and the OS has provided either physical RAM and/or paging file space for this allocation. Another restriction is that the memory pool of SQL Server 2000 using AWE cannot be swapped out to the page file. Windows 2000 has to swap out other applications if it needs to use additional physical memory, which may hinder the performance of the other applications. Therefore, you must ensure that there is enough memory outside of the amount used by SQL Server to satisfy the virtual memory needs of other applications running on the computer. You can do so by configuring the max server memory (MB) setting to a value that will leave sufficient memory on the machine for other applications. Do not change set working set size from the default setting. With the default of 0, the Windows NT or Windows 2000 virtual memory manager can determine the working set size of SQL Server. 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.
Thanks Satya. <br /><br />But there are still thing that I don’t understand in all this memory issues.<br /><br />Let’s clarify some things first:<br />The OS is W2k sp4 Advanced (ie has AWE). <br />The RAM of the machine is 6.2GB.<br />The SQL edition in Standard (all the time), ie cannot utilize more the 2GB – even if awe is enabled (which is not). <br />The server is dedicated to SQL.<br /><br />The memory config in SQL is set do fixed on 2GB "working set size" option is set to 1. We have seen performance improvement under this config and it’s probably due to the "working set size" option.<br /><br />Now, this is more about understanding the memory usage that dealing with the performance issues.<br /><br />I would like to understand what is really happens when "working set size" is set to 1. <br />1. Is that true that the OS reserve the exact amount of memory in the RAM as specified in the max value (ie 2GB)? <br />2.If so, for what ? what does it mean that SQL does not page when this option is set ? does it use this additional 2gb in the RAM to do "in-RAM paging".<br />3. given that the system has 6GB and 2GB are for SQL. is that true that OS reserves another 2GB for SQL because of the "working set size" option and then only the remaining 2GB of the 6gb are left ? <br />4. What happens when "working set size" is not set. Does this mean that SQL does page ? If there is plenty of RAM left (GB), could the system use it instead of paging to the file (=disk) ?<br />5. What use can the OS do with the remaining 4GB (after 2 has been taken to SQL) under each case, ie when "working set size" is set to 0 or to 1.<br /><br />I am sorry if it’s too long, but I am bit confused about the whole issue. reading al lot of material from here and from other sources, did not really clarified me this riddle.<br /><br />I would like to get some clear explanations, and also some good links. (nothing to devalue this site resources, there are great and helpful, I use them most of the times – <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> )<br /><br />Thanks.<br /><br />Aviel Iluz<br />Database Administrator<br />Pacific Micromarketing<br />Melbourne Australia<br />www.pacmicro.com.au<br />
I would suggest you to refer to Inside SQL server 2000 by Kalen Delaney for reference on memory architecture. And also SQL Server Technical referene Performance Tuning by Whalen Garcia & Deluca Thompson. Meanwhilehttp://www.sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk for information on Memory. HTH 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.
Thanks Satya. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

]]>