Physical Memory Utilization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Physical Memory Utilization

Author

Topic

CanadaDBA

583 Posts

Posted - 2007-02-12 : 13:14:55

Environment: Win2003 SP1, 32 bit, 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?

Canada DBA

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-02-12 : 14:05:16

Is this a 64-bit system or a 32-bit system? If it's 32-bit you'll need to enable AWE memory (http://msdn2.microsoft.com/en-US/library/ms190673.aspx). If they hardware supports 64-bit I'd seriously consider installing the 64-bit SQL Server.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

CanadaDBA

583 Posts

Posted - 2007-02-12 : 14:10:18

I edited my post. It is 32 bit.
So, you say enable AWE memory. This is a SQL Server option. Is there anything I do in Windows level?

Canada DBA

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-02-12 : 14:14:20

There's a link from that article to this one: http://msdn2.microsoft.com/en-US/library/ms175581.aspx that has a little more information

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Kristen
Test

22859 Posts

Posted - 2007-02-13 : 02:15:53

In case it provides any extra info:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=boot.ini+on+Win2003+standard,SQL+memory,AWE

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 03:00:00

What happened to the checkbox "Use only physical memory" that was there in SQL Server 2000?

Peter Larsson
Helsingborg, Sweden

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-13 : 03:18:15

quote:
Originally posted by Peso

What happened to the checkbox "Use only physical memory" that was there in SQL Server 2000?

I thought the "Reserve Physical Memory for SQL Server" checkbox prevented sql server from releasing memory to other apps. I think it also may prevent SQL From getting paged out to disk.

-ec

CanadaDBA

583 Posts

Posted - 2007-02-13 : 15:03:47

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)

Canada DBA

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-02-13 : 15:10:35

And entering that error message into Google gives this page: http://msdn2.microsoft.com/en-US/library/ms190730.aspx

Which explains how to set that option.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

CanadaDBA

583 Posts

Posted - 2007-02-13 : 15:40:15

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?

Canada DBA

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-02-13 : 15:49:34

But you're going to be using AWE aren't you? It says it only causes problems if you're not using AWE.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

CanadaDBA

583 Posts

Posted - 2007-02-15 : 12:43:30

In http://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.

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-02-15 : 15:34:38

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?

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-02-16 : 08:52:48

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?!!

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-02-20 : 10:43:40

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.


                	Befor Reboot	After Reboot
Physical Memory:
Total RAM: 		16 GB		16 GB
     Free: 		13.4 GB		11.8 GB
   Free %: 		84%		74%

Virtual Memory: 
Max Size: 		18.8 GB		18.8 GB
    Free: 		15.8 GB		13.8 GB
  Free %: 		84%		77%

It's great progress. :)

Now a question raised and that is why the virtual memory usage is increased too?

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-02-20 : 15:57:53

I rebooted my server at 10 am and here is the information at 4:30 pm:


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%

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! :)

My server is now utilizing the RAM and thanks to everyone who responded and contributed with this post.

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-02-21 : 08:27:34

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?

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-02-21 : 08:47:11

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?

Canada DBA

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 11:10:08

"Is it good"

My reading is that since the reboot yesterday SQL Server has progressively helped-itself to pretty much all the available memory. I reckon that is Good!

Kristen

CanadaDBA

583 Posts

Posted - 2007-02-21 : 11:17:26

How about my 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

quote:
Originally posted by Kristen

"Is it good"

My reading is that since the reboot yesterday SQL Server has progressively helped-itself to pretty much all the available memory. I reckon that is Good!

Kristen

Canada DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources