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.
| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2009-05-19 : 11:29:48
|
I read Brad McGehee's article on Tips for Using Performance Monitor Memory Counters and thought I had a decent understanding of these, but now I'm not so sure.I have a Windows Server 2003 Enterprise machine with 8 GB of RAM and has the /3GB and /PAE switches set. It has four instances of SQL Server 2000 Enterprise installed (call them D, P, M, and S). After setting AWE Enabled to 1 on each of the instances, I configured Max Server Memory as follows:D: 1500P: 3000M: 1000S: 1500Then I monitored Total Server Memory and Target Server Memory for each instance for a 24-hour period. The maximum values look like this: Target TotalD: 1,548,216 101,424P: 2,726,768 2,726,768M: 1,031,152 61,296S: 1,548,336 1,548,336 Some questions:1) I thought that Target meant how much SQL Server wanted to have, so why don't the totals for D and M go higher? This is a dedicated SQL Server machine, and it looks like the RAM is available. In fact, Available Mbytes never falls below 1748 while running. Does this mean that those two instances are under such light load that they don't really need the Target value? If so, then why is the Target value so high?2) It looks like there is enough RAM for P to go higher (max setting = 3000) but it hasn't gone there yet, so am I correct in interpreting this to suggest that P has sufficient RAM allocated? Also note that Page Life Expectancy on P currently hovers around 5,200 average.3) It looks like S has maxed out and would benefit from more RAM. Agreed?4) I have seen other posts/articles suggest that I should see the phrase "Address Windowing Extensions is enabled." in the server log, but I do not. However, sp_configure shows that the RUNNING value for AWE Enabled is 1. Any concern with that?Thanks in advance for any guidance!--------------------------------------------Brand yourself at EmeraldCityDomains.com |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-05-19 : 12:10:56
|
| i'm assuming this is a 32 bit system.1) Target Server memory means how much memory is available to this instance. you'll see that the numbers are close to your max mem setting. if total and target are near each other you might have a memory pressure problem.Total Server memory means how much memory is currently in use not counting AWE extended memory.since you have AWE enabled a part of the 3 GB memory will be allocated for mapping tables. that's probably where the difference between your P target and P max setting lies.2) since you have awe enabled the extra ram will be used to store data pages. that's why the page life expectancy is high and probably the buffer cache hit ratio too.3) both S and P have maxed. probably because of too many ad-hoc plans stored.4) you're concerned that you have awe enabled? why?___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2009-05-19 : 17:19:31
|
| Hey Mladen, thanks for the feedback!You are correct, this is a 32-bit system. I should have stated that in my original post.So, to be sure I understand, are you implying that if Total Server Memory never approaches the value for Target Server Memory that the Max Memory allocation on that instance could be reduced and another instance could be increased? I originally set my Max Memory values based on Target Server Memory values that were tracked before AWE was turned on, because I thought that was SQL Server telling me it wished it had that much memory available.And you say that Total Server Memory means how much memory is currently in use not counting AWE extended memory. I thought Total really meant all-inclusive, but it doesn't? Is there some other counter that will tell me how much memory it is actually using, all-together?Regarding #4, I'm not concerned that I have AWE enabled, I was just wondering why I don't see that phrase in the log after startup when so many posts I had read said that I would. Assuming that its absence from the log is meaningless and that I can trust the values returned from sp_configure to be true. I did a RECONFIGURE after making the change, and later a reboot.By the way, is memory usage influenced by the number of distinct databases on an instance, or is it all based on the total load? P has about 30 databases, but none of them are huge. Would, theoretically, fewer databases that added up to the same volume of data and activity, require the same memory or is there a baseline overhead just for having another database?--------------------------------------------Brand yourself at EmeraldCityDomains.com |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-05-19 : 17:59:01
|
quote:
So, to be sure I understand, are you implying that if Total Server Memory never approaches the value for Target Server Memory that the Max Memory allocation on that instance could be reduced and another instance could be increased? I originally set my Max Memory values based on Target Server Memory values that were tracked before AWE was turned on, because I thought that was SQL Server telling me it wished it had that much memory available.
yes  however be sure that the sum of max memory setting for all awe enabled instances doesn't go above total RAM.quote:
And you say that Total Server Memory means how much memory is currently in use not counting AWE extended memory. I thought Total really meant all-inclusive, but it doesn't? Is there some other counter that will tell me how much memory it is actually using, all-together?
my bad here. Total Server Memory counter shows AWE too. Task manager doesn't show it. quote:
Regarding #4, I'm not concerned that I have AWE enabled, I was just wondering why I don't see that phrase in the log after startup when so many posts I had read said that I would. Assuming that its absence from the log is meaningless and that I can trust the values returned from sp_configure to be true. I did a RECONFIGURE after making the change, and later a reboot.
no clue why it doesn't show in error log.quote:
By the way, is memory usage influenced by the number of distinct databases on an instance, or is it all based on the total load? P has about 30 databases, but none of them are huge. Would, theoretically, fewer databases that added up to the same volume of data and activity, require the same memory or is there a baseline overhead just for having another database?
not quite sure what you mean here.memory is taken by loaded datapages, plan cache, internal memory objects (locks, latches, etc).so more databases will influence memory only if they are accessed and data is loaded into memory.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2009-05-19 : 19:07:51
|
| That all sounds good. Maybe my last question is proceeding from a false premise. Let me put it this way... I believe (though, I may be wrong) that each new instance of SQL Server requires some minimum amount of RAM, even if it is otherwise sitting idle. So I was wondering whether the same was true for a database...does just the existence of an online database have an impact on memory consumption?Mostly a philosophical/trivia question, but just curious.--------------------------------------------Brand yourself at EmeraldCityDomains.com |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-05-20 : 05:00:22
|
| no. database has no impact on memory if it's not used. it probably uses some internal memory but that's negligible.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2009-05-20 : 12:42:14
|
| You're the best, Mladen! Thanks for your help!--------------------------------------------Brand yourself at EmeraldCityDomains.com |
|
|
|
|
|
|
|
|
PerfMon Memory Counters