February 8, 2012 at 11:26 pm
We have set max memory up to 3.2GB for a 32 bit sql server running on 32 bit OS but it has no changes after reboot the server.
OS physical memory is 4.0 Gb and MSQLSERVER uses upto 1.6 Gb of RAM as we have seen in task manager.
Then, what changes are required to take effect of same....reply please...
February 9, 2012 at 12:09 am
That setting specifies the maximum amount.
It does not garantuee that SQL Server will effictively take that amount of RAM.
Try a combination with the minimum amount of memory.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2012 at 1:50 am
32 bit SQL on 32 bit OS is limited to 2GB of memory. Some (around 300 MB) is reserved (called mem_to_leave), the remainder is exactly that 1.6GB that you see.
Solutions:
Upgrade to 64-bit OS and 64-bit SQL
or
add the /3GB switch to boot.ini which will allow SQL to use up to 2.7GB memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2012 at 3:34 am
GilaMonster (2/9/2012)
32 bit SQL on 32 bit OS is limited to 2GB of memory. Some (around 300 MB) is reserved (called mem_to_leave), the remainder is exactly that 1.6GB that you see.
2GB for user mode and 2 GB for kernal mode.
Solutions:
Upgrade to 64-bit OS and 64-bit SQL
or
add the /3GB switch to boot.ini which will allow SQL to use up to 2.7GB memory.
/3GB switch is always recommandable? Please correct me if am wrong. Will there be a situation for kernal memory pressure?
February 9, 2012 at 5:17 am
sqlzealot-81 (2/9/2012)
GilaMonster (2/9/2012)
32 bit SQL on 32 bit OS is limited to 2GB of memory. Some (around 300 MB) is reserved (called mem_to_leave), the remainder is exactly that 1.6GB that you see.2GB for user mode and 2 GB for kernal mode.
Solutions:
Upgrade to 64-bit OS and 64-bit SQL
or
add the /3GB switch to boot.ini which will allow SQL to use up to 2.7GB memory.
/3GB switch is always recommandable? Please correct me if am wrong. Will there be a situation for kernal memory pressure?
That depends on the environment like what else you are running on the same OS except SQL Server (like IIS ?).
February 9, 2012 at 5:27 am
Divine Flame (2/9/2012)
sqlzealot-81 (2/9/2012)
GilaMonster (2/9/2012)
32 bit SQL on 32 bit OS is limited to 2GB of memory. Some (around 300 MB) is reserved (called mem_to_leave), the remainder is exactly that 1.6GB that you see.2GB for user mode and 2 GB for kernal mode.
Solutions:
Upgrade to 64-bit OS and 64-bit SQL
or
add the /3GB switch to boot.ini which will allow SQL to use up to 2.7GB memory.
/3GB switch is always recommandable? Please correct me if am wrong. Will there be a situation for kernal memory pressure?
That depends on the environment like what else you are running on the same OS except SQL Server (like IIS ?).
To my understanding not only outside SQL, even within SQL also, for an example: Extended procedures, CLR functions etc...right?
February 9, 2012 at 5:37 am
If you are using them in excess then yes, they will also make the impact.
February 9, 2012 at 8:10 am
Divine Flame (2/9/2012)
That depends on the environment like what else you are running on the same OS except SQL Server (like IIS ?).
No, it does not.
The 2GB/2GB division is VAS and is per-process, it is NOT overall memory. Way too many people don't understand the difference between VAS (which on 32-bit is 2GB for user segment, 2GB for kernal segment per process regardless of how much physical memory exists) and physical memory (where no such divisions exist, though of course if the process has only 2GB of User mode VAS, then it can't allocate more than 2GB of memory)
Chapter 4 - http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ (and this is not just a suggestion for the OP)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2012 at 8:12 am
sqlzealot-81 (2/9/2012)
To my understanding not only outside SQL, even within SQL also, for an example: Extended procedures, CLR functions etc...right?
Nope, those are all part of the user-mode SQL Server process and are subject to the same user-mode VAS restrictions as the rest of SQL Server is.
Chapter 4 - http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2012 at 8:12 am
sqlzealot-81 (2/9/2012)
Will there be a situation for kernal memory pressure?
Yes, as an example when there's more than 8GB physical RAM and /PAE is on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2012 at 9:49 am
I should also add that in the cases where there's other apps on the server (SSIS, IIS, etc) you'd probably want to reduce the max server memory.
In cases where there's extended procedures or lots of CLR then on 32-bit you'd increase the mem-to-leave reservation with -g, on 64-bit you'd just reduce max server memory.
Both of those are about reducing physical memory allocations to the buffer pool to allow physical memory for other processes/apps, not changing the VAS settings.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2012 at 5:42 pm
GilaMonster (2/9/2012)
sqlzealot-81 (2/9/2012)
Will there be a situation for kernal memory pressure?Yes, as an example when there's more than 8GB physical RAM and /PAE is on.
I dont understand this point, say if we have 8 GB of RAM.
2GB Kernal mode+ 2 GB for USer Mode.If the system has manyextended procedures and CLR functions.And also /PAE switch is enabled for the OS.Also the MAX SERVER MEMORY has set as 4 GB.
How does thesystem will have a memory pressure due to 8GB Physical RAM and /PAE enabled ? Could you please explain...
February 9, 2012 at 5:43 pm
sqlzealot-81 (2/9/2012)
GilaMonster (2/9/2012)
sqlzealot-81 (2/9/2012)
Will there be a situation for kernal memory pressure?Yes, as an example when there's more than 8GB physical RAM and /PAE is on.
I dont understand this point, say if we have 8 GB of RAM.
2GB Kernal mode+ 2 GB for USer Mode.If the system has manyextended procedures and CLR functions.And also /PAE switch is enabled for the OS.Also the MAX SERVER MEMORY has set as 4 GB.
How does thesystem will have a memory pressure due to 8GB Physical RAM and /PAE enabled ? Could you please explain...
Could you pleasealso let me know the perfmon counter for memory to look the presure also...
February 9, 2012 at 5:51 pm
GilaMonster (2/9/2012)
I should also add that in the cases where there's other apps on the server (SSIS, IIS, etc) you'd probably want to reduce the max server memory..
Okei, if only /PAEis eanbled right?
In cases where there's extended procedures or lots of CLR then on 32-bit you'd increase the mem-to-leave reservation with -g, on 64-bit you'd just reduce max server memory.
Both of those are about reducing physical memory allocations to the buffer pool to allow physical memory for other processes/apps, not changing the VAS settings.
[/quote]
Could you please explain why do we need to do this and how do we do this?Any handy link would help me.Cant we just set the MAX MEMORYto address the issue?
February 10, 2012 at 4:18 am
sqlzealot-81 (2/9/2012)
GilaMonster (2/9/2012)
I should also add that in the cases where there's other apps on the server (SSIS, IIS, etc) you'd probably want to reduce the max server memory..Okei, if only /PAEis eanbled right?
No.
sqlzealot-81 (2/9/2012)
sqlzealot-81 (2/9/2012)
GilaMonster (2/9/2012)
sqlzealot-81 (2/9/2012)
Will there be a situation for kernal memory pressure?Yes, as an example when there's more than 8GB physical RAM and /PAE is on.
I dont understand this point, say if we have 8 GB of RAM.
2GB Kernal mode+ 2 GB for USer Mode.If the system has manyextended procedures and CLR functions.And also /PAE switch is enabled for the OS.Also the MAX SERVER MEMORY has set as 4 GB.
How does thesystem will have a memory pressure due to 8GB Physical RAM and /PAE enabled ? Could you please explain...
Could you pleasealso let me know the perfmon counter for memory to look the presure also...
In cases where there's extended procedures or lots of CLR then on 32-bit you'd increase the mem-to-leave reservation with -g, on 64-bit you'd just reduce max server memory.
Both of those are about reducing physical memory allocations to the buffer pool to allow physical memory for other processes/apps, not changing the VAS settings.
Could you please explain why do we need to do this and how do we do this?Any handy link would help me.Cant we just set the MAX MEMORYto address the issue?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply