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
 General SQL Server Forums
 New to SQL Server Administration
 OS Memory utilization critical

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-30 : 09:26:00
Hi,

I am running ignite trial version and found out two of our servers constantly has this ratio at 95%, and based on their definition, this is critical.

Is that so? And, if it is, how to improve?

Here is the description they offer:

Percent of memory being utilized for the entire system (includes all instances on this machine). If this is high and the Memory Paging Rate metric is high, you may need to increase the amount of physical RAM in the server, reduce the load on the server and/or change the server memory configuration accordingly. Run sp_configure and review settings for "max server memory" and "min server memory" to determine amount of memory SQL Server is allocated.

Here are other related info for these servers:

Memory paging rate(pages/sec) shows short period spike at 8 on one and 26 on another. Are they "high"?

sp_configure results:
server 1
name minimum maximum config_value run_value
max server memory (MB) 16 2147483647 115000 115000
min server memory (MB) 0 2147483647 0 0
server 2
name minimum maximum config_value run_value
max server memory (MB) 16 2147483647 28000 28000
min server memory (MB) 0 2147483647 0 0

Both run wins server 2008 R2 Datacenter, sql 2008 R2, one on sp1, and the other sp2.
server 1: 128 GB RAM for 12 processors.
server 2: 32 GB RAM for 4 processors.

They are both on vm.

Thanks!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-30 : 10:58:46
I think they are OK, i.e. sql server takes as many RAM mem as possible is normal, and it may not be a bad thing.

At least, to certain extent, it shouldn't cause performance issues.

Am I safe here?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-30 : 11:08:00
It seems to me that SQL Server is not the problem if indeed you have 128Gigs. Only 28 Gigs is allocated to SQL Server, and SQL Server (buffer pool) will consume no more than that. The total memory consumed by the SQL Server process might be a little higher than that, but not by much.

Look up perfmon or even task manager to see which processes are consuming the memory.
Do you have other applications/services running on this machine?
Do you have multiple instances of SQL Server on it?
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-30 : 11:11:22
You should configure the max memory setting on your SQL Server instances. Assuming that these are dedicated SQL Servers abd there is one instance per server, I'll leave about 4 GB for the OS on server 2 and maybe 6 to 8 on Server 1.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-30 : 11:23:33
task manager shows
server 1
sqlservr.exe takes 30 gb memory
server 2
sqlservr.exe half gb

No other proccesses standing out, and they are both dedicated sql server with only one default instance each.

quote:
Originally posted by James K

It seems to me that SQL Server is not the problem if indeed you have 128Gigs. Only 28 Gigs is allocated to SQL Server, and SQL Server (buffer pool) will consume no more than that. The total memory consumed by the SQL Server process might be a little higher than that, but not by much.

Look up perfmon or even task manager to see which processes are consuming the memory.
Do you have other applications/services running on this machine?
Do you have multiple instances of SQL Server on it?


Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-30 : 11:29:15
Thanks!

The thing I haven't quite figure out is server2.

How could it has 115 gb as config and run value when it only has 32 gb ram?

---------
name minimum maximum config_value run_value
max server memory (MB) 16 2147483647 115000 115000

server 2: 32 GB RAM for 4 processors.






quote:
Originally posted by russell

You should configure the max memory setting on your SQL Server instances. Assuming that these are dedicated SQL Servers abd there is one instance per server, I'll leave about 4 GB for the OS on server 2 and maybe 6 to 8 on Server 1.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-30 : 12:16:15
Are you sure that you don't have the data accidentally swapped? May SQL sever on the the machine with 128 Gigs is configured to use 115 Gigs and the one with the 32 Gigs is configured to use 28 Gigs?

If that is the case, unless you have other applications running on these servers, you should be fine. Memory usage by SQL server running up to the maximum configured value is perfectly fine and expected behavior.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-05-30 : 14:34:29
James,

You are right! I did copy/paste the wrong data and did not think twice. I have corrected that on the original post.

So, the Ignite monitoring tool by Confio has mislead me.

Any one can recommend me a good third party tool for perforamce tuning and overall monitoring? Any comparison on red-gate, sqlsentry or quest?


quote:
Originally posted by James K

Are you sure that you don't have the data accidentally swapped? May SQL sever on the the machine with 128 Gigs is configured to use 115 Gigs and the one with the 32 Gigs is configured to use 28 Gigs?

If that is the case, unless you have other applications running on these servers, you should be fine. Memory usage by SQL server running up to the maximum configured value is perfectly fine and expected behavior.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-30 : 15:17:12
I don't know about specific tools, (this page has the list of a few), but any tool that you use should ignore memory usage on dedicated SQL Servers when they run up the memory into the high ninetees. I am guessing that every monitoring tool should let you specify alert thresholds. What you should instead measure is the memory pressure on SQL OS itself. There are counters that will let you do that - PLE, for one.

Editing: PLE == Page Life Expectancy.
http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/
http://sqlmag.com/blog/troubleshooting-page-life-expectancy-drops
Go to Top of Page
   

- Advertisement -