SQL Server Performance Forum – Threads Archive

Physical Memory Utilization

Environment: Win2003 SP1, SQL Server 2K5 My server has 16GB RM but it is using only 3GB. And I see my server is using 3GB of Virtual Memory, too. Why my physical memory is not being utilized? How can I increase Physical Memory usage and decrease VM usage?
CanadaDBA

What is your OS and SQL edition? Read the topic "Address Windowing Extensions (AWE) and SQL Server" from the following article… Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
MohammedU.
Moderator
SQL-Server-Performance.com

KBAhttp://support.microsoft.com/kb/274750 too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

When I check the "Use AWE to allocate memory" in "Server Properties" window, get the following error: Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process. (Microsoft SQL Server, Error: 5845) CanadaDBA

Review MSDN article How to: Enable the Lock Pages in Memory Option (Windows)http://msdn2.microsoft.com/en-US/library/ms190730.aspx and restarting the machine typically helps permissions granted to the sql server service account take effect.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Satya, it says: On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Although not required, we recommend locking pages in memory when using 64-bit operating systems. I have 16GB RAM and want to use AWE but I don’t want to impact the server’s performance. Should I go ahead and enable the lock pages in memory option?
CanadaDBA

if this server is dedicated to SQL and properly configured, thats not an issue performance can be degraded because the locked pages cannot be paged out, reducing the memory available for other apps you have no other apps

Inhttp://msdn2.microsoft.com/en-US/library/ms190731.aspx it says run the following to set the max server memory to 6GB but when I ran it, it set my server to 6MB!!!
sp_configure ‘min server memory’, 1024
RECONFIGURE
GO
sp_configure ‘max server memory’, 6144
RECONFIGURE
GO In server properties I see the Maximum server memory (in MB) is set to 2147483647. Note: I stop and start SQL Server but didn’t reboot the machine.

quote:Originally posted by satya Review MSDN article How to: Enable the Lock Pages in Memory Option (Windows)http://msdn2.microsoft.com/en-US/library/ms190730.aspx and restarting the machine typically helps permissions granted to the sql server service account take effect.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

CanadaDBA

Take a look answer from Slava Oks who works for MS on storgae engine… Q and A: Using Lock Pages In memory on 64 bit platform
http://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx
MohammedU.
Moderator
SQL-Server-Performance.com

i think you are confused,
why do you think your server was set to 6MB?

Where do you see 6 MB?
Use perfmon counters TOTAL SERVER MEMORY and TARGET SERVER MEMORY to check the memory … do not use task manager.
MohammedU.
Moderator
SQL-Server-Performance.com

MohammedU, my server is a 32 bit. I tried DBCC memorystatus and it shows 0 for AWE Allocated. I removed the check mark from "Use AWE to allocate memory" in Server Properties but the results for the DBCC was the same. But sp_configure ‘awe enabled’, 1 says: Configuration option ‘awe enabled’ changed from 1 to 1. Run the RECONFIGURE statement to install. What does this mean? CanadaDBA

If run_value is set to 1, AWE is enabled on the server. To view the behavior, examine the SQL Server: Memory Manager/Total Server Memory (KB) counter in System Monitor. On a computer that is running SQL Server Service Pack 3 (SP3), this value may be up to the amount of physical memory that is on the computer. On a computer that is running SQL Server SP4, this value will never be more than 50 percent of the physical memory.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Satya, OS is Windows 2003 SP1
The machine has 16GB RAM.
SQL Server: Memory Manager/Total Server Memory (KB) counter in System Monitor shows 1598208. What this number means? If this number is in KB, then does it mean I have almost 1.6GB? CanadaDBA

Did you enable locking pages? as Satya mentioned…
http://msdn2.microsoft.com/en-US/library/ms190730.aspx What is your SQL build?
If you are on SP4 without cumulative patch or hot fix SQL can’t see all memory…seet the following article… Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4
http://support.microsoft.com/kb/899761
MohammedU.
Moderator
SQL-Server-Performance.com

It means 1560GB (16gb), isn;t it. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Microsoft SQL Server 2005 Enterprise Edition
Version: 9.00.2047.00
Operating System: Microsoft Windows NT 5.2 (3790) Memory status on Tuesday at 10AM:
Physical Memory:
Total RAM: 16 GB
Free: 13.4 GB
Free %: 84% Virtual Memory:
Max Size: 18.8 GB
Free: 15.8 GB
Free %: 84% Why my system is using VM while it has 13.4 GB RAM available?

quote:Originally posted by MohammedU Did you enable locking pages? as Satya mentioned…
http://msdn2.microsoft.com/en-US/library/ms190730.aspx What is your SQL build?
If you are on SP4 without cumulative patch or hot fix SQL can’t see all memory…seet the following article… Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4
http://support.microsoft.com/kb/899761
MohammedU.
Moderator
SQL-Server-Performance.com

CanadaDBA

After I checked the "Use AWE to allocate memory" and set ‘lock pages in memory’ privilege last week, I rebooted SQL Server not the machine. Today, I had the chance to reboot the machine. You can see the memory status in my previous post. It changed to the following after reboot: <pre id="code"> Physical Memory: Total RAM: 16 GB Free: 11.8 GB Free %: 74% Virtual Memory: Max Size: 18.8 GB Free: 13.8 GB Free %: 77% </pre id="code"> It’s great progress. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Now a question raised and that is why the virtual memory usage is increased too? CanadaDBA

do not worry about vm use,
what you do not want is excessive page file use,
page file != vm vm max size should phy mem + page file size look in your C

rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG
or where you put the MSSQL directory
for the ERRORLOG file
open it with notepad look for an entry concerning AWE up top

Joechang, it says "Address Windowing Extensions is enabled." I rebooted my server at 10 am and here is the information at 4:30 pm: <pre id="code"> Physical Memory: Total RAM: 16 GB Free: 10.2 GB Free %: 64% Virtual Memory: Max Size: 18.0 GB Free: 12.2 GB Free %: 68% </pre id="code"> You are right. It shows the max size of VM is 18 GB while I haven’t set my Windows paging files to be 18 GB. My mistake was that I thought this VM is the Windows paging size! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> My server is now utilizing the RAM and thanks to everyone who respond and contributed with this post. CanadaDBA

what do you have max server memory set to?
6140MB ?
is that what you want?
for 16GB, 15360 is probably good

Here is the information from Server Properties window: Server memory options
Minimum server memory (in MB): 37
Maximum server memory (in MB): 2147483647 Other memory options
Index creation memory (in KB, 0 = dynamic memory): 0
Minimum memory per query (in KB): 1024 These number were there when I started this job. With knowledge of my environment, do you think I should change them?

quote:Originally posted by joechang what do you have max server memory set to?
6140MB ?
is that what you want?
for 16GB, 15360 is probably good

CanadaDBA

My server at 9:30 am, a day after reboot:
Physical Memory:
Total RAM: 16 GB
Free: 1.55 GB
Free %: 10% Virtual Memory:
Max Size: 18.0 GB
Free: 3.53 GB
Free %: 20%
Is it good or something is wrong? Referring to my previous post, should I modify any of those numbers? CanadaDBA

lets see you bought 16GB of memory for your system
you are using 14.5 tot, probably 14G for SQL this is a good place to start
with careful observation, potentially you could bump up amount until
free RAM drops to 500MB
but this should be only over time did you want to use the memory for something else?
some adminds want as much free memory as possible
so basically its just there to keep the room warm

SQLServer is the only program on the server. Do you mean it is ok to use the RAM upto 15.5GB? You didn’t mentioned about the following that I sent in previous post:

Here is the information from Server Properties window: Server memory options
Minimum server memory (in MB): 37
Maximum server memory (in MB): 2147483647 Other memory options
Index creation memory (in KB, 0 = dynamic memory): 0
Minimum memory per query (in KB): 1024
CanadaDBA

I believe you should leave 2GB for OS and rest can be configured to sql. Total memory: 16 GB then Set MAX SERVER MEMORY to 14GB. How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech Managing AWE Memory
http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
MohammedU.
Moderator
SQL-Server-Performance.com

the closer you want to get to nearly zero free memory
the more skill it takes to avoid crippling your system in transient operations
i suggest leave alone for now, until you learn more about how the system behaves if you consider that the 1.5G not being used costs about $300,
how much pain are you willing to risk to get at it?

I remember in this line Maximum server memory (in MB): 2147483647 it was (in KB) than MB. I think after I rebooted the server and with the AWE setting and with /PAE switch it was changed to MB. Should I change the 2147483647 number to 14.5GB x 1024 = 14848MB?
CanadaDBA

2147483647 is a default setting
leave it alone some one back at the Microsoft campus was smart enough to figure out how much memory SQL should use
if you are not smarter than he/she is, leave it alone

What do you think there are smart enough? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Read BOL for completed details… SQL Server 2005 Books Online Enabling AWE Memory for SQL Server http://msdn2.microsoft.com/en-us/library/ms190673.aspx</a> MohammedU. Moderator SQL-Server-Performance.com

]]>