Memory Utilization

Question

Memory Utilization

viswanath09

Mr or Mrs. 500

Points: 512
More actions
July 8, 2010 at 12:30 am

#223624

Hi all,
I am using SQL SERVER 2008. When ever i see Physical Memory usage its nearly going to 7.4GB. I have only 8GB Ram.
How to tackle this one. Please guide me how to set max and min memory to sql server.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

SQL Server by default will attempt to use all (except for a few hundred MB) of the memory on a server. Given that it's up to 7.4 GB, we can assume you're on x64.

To set the maximum and minimum server memory values, you can use Management Studio (right click on the server in Object Explorer, go to Properties, then to Memory. I would suggest setting Max Server Memory to 6.5 GB (6656 MB), allowing 1.5 GB for the operating system and other critical processes (this assumes a dedicated server). Minimum Server Memory isn't as critical when there's only a single instance, but consider putting it to 3000 MB.

To do this via T-SQL:

EXEC sp_configure 'Show Advanced Options', 1

RECONFIGURE

EXEC sp_configure N'min server memory (MB)', N'3000'

GO

EXEC sp_configure N'max server memory (MB)', N'6656'

GO

RECONFIGURE WITH OVERRIDE

GO

Rajesh M S Say Hey Kid Points: 668 More actions · Answer 2

If you are on 32bit need to enable 3Gb switch in boot.ini file and AWE on SQL Server.max 6.5 GB and minimum 1.5 GB server memory is OK.

viswanath09 Mr or Mrs. 500 Points: 512 More actions · Answer 3

Hi,

Thanks for giving valuble points.

I exeuted that T-Sql query. the values has changed. But Its still showing 7.35 GB.

Any other ways to solve this. Please let me know.

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 4

It's not something I've tested in a while, but it's likely that SQL Server will remain high until the buffer pool drops below the 6.5 GB mark, and then won't grow any larger again. It will only drop due to memory pressure, or a restart of SQL Server.

Have you tried restarting already?

viswanath09 Mr or Mrs. 500 Points: 512 More actions · Answer 5

viswanath09

Mr or Mrs. 500

Points: 512

July 9, 2010 at 3:11 am

#1190667

Hi,

I have done. Its working fine now.

Thanks

viswanath09 Mr or Mrs. 500 Points: 512 More actions · Answer 6

Hi All,

As per the above task. I have done the memory utilization.

After 5 days again its taking max of 5.95 GB.

Is their any other way to reduce this issue.

I have given 3000 gb to min and 6565 gb to max. Now its taking the max of 5.95 GB.

please updated the same.

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 7

What exactly is the problem? Are you concerned that SQL Server is caching your data? Do you need the memory spare for another application on this server? How much memory do you want SQL Server to use?

viswanath09 Mr or Mrs. 500 Points: 512 More actions · Answer 8

Hi,

Probelm is Memory Utilization. Right Now i have 8 GB Ram

I have given min 3000 mb and 6565 mb to max.

Now again my sql server is using nearly 5.95 gb. only two applications are running in this server.

i did't enable the awe.

is their any other resource to fix issue.

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 9

As mentioned earlier, SQL Server will use as much memory as you allow it to. If, in your case, you need to have more than 1.5 GB available, then you should drop the Max Server Memory down. However, it's worth noting that if SQL Server is now using 5.95 GB after 5 days, then 5.95 GB is required. If you drop this figure down lower, more queries will have to access data from disk, instead of memory, and your queries may run slower.

If your other applications need 4 GB of memory, then you should set the Max Server Memory of SQL Server appropriately. Dropping it to 4 GB will leave 4 GB to be shared among Windows and the other applications.

If SQL Server is using more than 3 GB of memory, then you're on a 64bit server, and AWE will do nothing.