| 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 1name minimum maximum config_value run_valuemax server memory (MB) 16 2147483647 115000 115000min server memory (MB) 0 2147483647 0 0server 2name minimum maximum config_value run_valuemax server memory (MB) 16 2147483647 28000 28000min server memory (MB) 0 2147483647 0 0Both 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? |
 |
|
|
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? |
|
|
|
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. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-05-30 : 11:23:33
|
task manager showsserver 1sqlservr.exe takes 30 gb memoryserver 2sqlservr.exe half gbNo 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?
|
|
|
|
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_valuemax server memory (MB) 16 2147483647 115000 115000server 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.
|
|
|
|
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. |
|
|
|
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.
|
|
|
|
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 |
|
|
|
|
OS Memory utilization critical