Memory Issues on production sql server

Question

Post reply

Memory Issues on production sql server

nagkarjun1

Ten Centuries

Points: 1020
More actions
July 10, 2013 at 2:21 pm

#296230

Please help me in this situation.
Total memory on box : 10 GB
Single node, non clustered production box
Min memory is set to 0
Max Memory is set to 8 GB
From last month the box Memory is constantly at 95%
CPU usage is very low
here is the screen shot from the task manager
How to find
Is SQL server using the complete 8GB ?
Management is asking to reduce the SQL server memory setting to 7GB
Is this fine to decrease the memory limit. Please advice.

Viewing 6 posts - 1 through 5 (of 5 total)

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

rackerland Old Hand Points: 359 More actions · Answer 1

Hi nagkarjun1,

The Max Server Memory setting only controls the size of the Buffer Pool and not the entire memory used by SQL Server.

Taken from: http://msdn.microsoft.com/en-us/library/ms180797(v=sql.105).aspx

SQL Server as a process acquires more memory than specified by max server memory option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually represents the largest portion of memory consumed by SQL Server.

I would recommend moving Max Memory down to 7GBs to see how it responds.

As a side note, you can also use Glen Berry's DMV a day queries to see what is in your Buffer Pool now and then see what the affects are when it's reduced: http://www.sqlservercentral.com/blogs/glennberry/2010/04/30/a-dmv-a-day-_1320_-day-30/

Hope that helps.

nagkarjun1 Ten Centuries Points: 1020 More actions · Answer 2

Thanks for your suggestion.

Will follow this.

Do we need to restart the server or services after changing the new max memory on the production server.

I guess it dynamically changes. please let me know.

rackerland Old Hand Points: 359 More actions · Answer 3

No need to restart the server, it will take effect immediately. If you have Task Manager up on the Server while you do it, you can see the results almost instantly.

nagkarjun1 Ten Centuries Points: 1020 More actions · Answer 4

It is a 64bit VM server.

AWE option is checked.

Is this is the reason for using more memory?

rackerland Old Hand Points: 359 More actions · Answer 5

I don't believe the AWE option makes any difference on a 64-bit system so that shouldn't be an issue.

See these two articles for more explanation:

http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-530-awe-must-be-enabled-on-64-bit-servers/

Since it's a VMware server, have you checked for memory ballooning? And do you have a memory reservation configured for your VM? Also, is there memory pressure on the physical host system? I would begin to look at these things if you still see high memory allocation even after reducing max server memory.