April 13, 2009 at 11:09 am
I have a quick question about SQL Server memory pressure.
My production server is with 32 GB memory and out of which, i have configured 28 for SQL. I have concurrent users of 500 on my system and the total memory counter shows 28 GB of memory in use. target memory shows the same as total server memory. doesnt this mean, sql is actively using 28 GB of memory and given target memory counter is same as total, isnt there a memory pressure on my box?
Appreciated some feeback.
Many thanks,
April 13, 2009 at 1:07 pm
Hi,
No it doesn't mean there is memory pressure; the target memory value is taken from the maximum memory setting. And the server usage means that SQL Server is using that much memory by Buffer Manager. To see if your SQL Server is under memory pressure look at Page Faults for Memory, Page Life Exp and Cache Hit Ratio for SQL Server: Buffer Manager. These are places to look to see if you are having memory pressure.
SQL Server will take much the maximum memory you give it; just how the product is designed. Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 13, 2009 at 4:34 pm
Thanks for the contribution.
I read few articles listing total and target server memory. total server memory is the current memory in use by SQL Server. Target is what it intend to use to function properly. if total server memory = or more than target, then sql is under pressure. sure not to forget the buffer cache and page life expentency and memory grants counter too.
my question was: why do i see total and target memory the same. also process working set is 200 MB where as total server memory was 28 GB. does this make sense?
April 13, 2009 at 7:02 pm
What you're seeing is exactly what I would expect to see.
At a simplified level, when the SQL Server service starts it will ask Windows to allocate whatever the minimum memory defined to it is (sp_configure: min server memory). As users run queries and internal processes do things SQL Server will continue to ask Windows for more memory, never releasing what has already been allocated to it. SQL Server will continue asking for more memory until it has been allocated whatever has been defined as it's maximum (sp_configure: max server memory). At times it can exceed the max server memory temporarily. If there's no restriction (default value for max server memory is (231-1)MB: so high it's not worth considering a limit for another decade or two) then it just keeps asking for more and Windows keeps saying No.
Once SQL Server reaches it's maximum memory (ie. total memory = target memory) it starts managing what it's got. That's when pages start being dropped from the buffer to create new space for new data pages, the size of the various memory "compartments" (eg. procedure cache, connection pool, buffers, etc.) start changing depending on the workload and other internal memory management starts coming into effect. It's at this point that signs of memory pressure will start to be evidenced.
As Mohit suggested, it's the behaviour of the pages once SQL Server has grabbed them that will decide whether your server is experiencing memory pressure. The most common indications of that are low page life expectancy, low cache hit ratio or high page faults, but (as always) it depends very much on the workload as to what constitutes "low" or "high".
April 14, 2009 at 8:04 am
Gert Drapers has some great insight into memory management. You can find the presentation and the examples at the following site:
http://www.sqldev.net/events.htm
SSDB352 Memory Management - Do You Have Enough and Are You Using It Effectively?
April 15, 2009 at 9:59 am
thanks everyone for the contribution. I see we covered quiet few areas concerning memory. but quiet frankly - i still have one simple question. is there a way to know how much memory is in use by sql server at any given time? If its total server memory coutner - then we will start from where we left.
April 15, 2009 at 10:43 am
If you're seeing the total memory counter at 28GB, then SQL Server is actually using 28GB of RAM. One of the main features of SQL Server is that it will try to cache as much as it possibly can, using RAM to do so; as a result, SQL Server will eventually allocate all the RAM that it is allowed to. If you were to watch that counter while you start the SQL service, you would see it start at the minimum memory allocation amount, and then gradually increase to the maximum it can allow.
So, does that mean that you are experiencing memory pressure? The answer is that you cannot use that counter to get to your answer. There are some counters that you can use, to start looking at to see if you're experiencing memory pressure:
(1) Page Life Expectancy. If you watch this over time, and see a number that is consistently under 300, then you are experiencing memory pressure. Microsoft defines this counter as the amount of time that an un-referenced page will remain in cache, before being flushed out of the cache.
(2) Buffer Cache Hit Ratio. Microsoft usually says that anything under 99% indicates memory pressure. I'm not sure if that's exactly right or not, but for sure if you're seeing consistent readings at 90% or lower you're definitely experiencing memory pressure.
(3) Page Faults for Memory. The number of times that SQL Server is having to use disk space for RAM. If you're seeing this happen consistently, SQL Server is having to swap information back and forth from the disk, and that's not a good thing.
To give you some examples, I'm supporting a web-based application that is *starving* for memory; hopefully we'll be upgraded sometime in the next month or so. Page Life Expectancy is usually less than 40, Buffer cache hit ratio in the 90-91% range, and averaging about 75 page faults per second. Like I said, starving for memory.
Also remember that any of the counters that you monitor for SQL Server should be looked at in the context of other counters. Sometimes processor-starvation can mask itself as memory pressure; sometimes memory pressure can mask itself as disk pressure. I've been supporting this application now for about 8 weeks; when I first started looking at the counters, some of the disk counters were high enough that it suggested there was pressure there; the real culprit, as it turned out, was memory, which was causing the pages to get flushed and loaded from the disk so frequently that it sort of looked like disk pressure.
Hope some of this helps.
David
April 15, 2009 at 11:39 am
David,
Thanks for the detailed answer and analysis. indeed it was very helpful.
with what i learned, it seems total server memory itself can not justify a memory pressure. but with what other articles i have read so far, they were reaching out to justify, if total server memory = target server memory - then we have memory pressure. looks like thats not relivant.
i will go and do further analysis on my servers with the new proposed counters. lets see, what i encounter.
will keep posted.
again thank you guys - you people are best.
Cali
April 15, 2009 at 1:06 pm
lest we not forget:
DBCC MEMORYSTATUS
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 15, 2009 at 2:41 pm
ok here's my findings:
On most of production SQL Servers (SQL 2005-64 bit) the value reads as follows:
Page life expentecy: 27,000
Buffer Cache: 100%
Page faults / Sec: 10.90k (Average)
The first two coutners pretty looks fine. page fault over 10.90k - does that sound normal?
April 15, 2009 at 2:57 pm
The answer you're looking for here is, no, you're not under memory pressure. The Page Life Expectancy number could be interpreted as: if a page is loaded into the cache, and it's not being referenced by anything, SQL Server won't need to flush it out of the cache (to make room for something that is being referenced) for 27,000 seconds.
How exactly did you get the Page Faults number? There is a mechanism (Process / Page Faults per Second) that will allow you to see just the page faults caused by SQL Server; did you select the Total (default) or limit it to just SQL Server? That number seems a little high, but someone else here may have better information on that topic.
You're making me jealous, btw.
April 15, 2009 at 3:19 pm
I think page faults are bit high but doesn't mean SQL Server is under memory pressure; it is your OS is complaining "a bit" or other processes running on the server. But I don't think its a thing you have to concern about on my servers I average 2-5K with 4GB memory. and I don't have anyone screaming at me so I am assuming it's good ;-).
But your other stats are excellent :).
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 15, 2009 at 4:22 pm
Are there any other applications than SQL Server running on the server (eg. IIS, Reporting Services, Analysis Services, etc.)? SQL Server itself is fine but from the page faults value it looks like everything else might be struggling. Normally I'd say get the other things off your database server onto a separate server, but given the (very blinkered) view of SQL Server's memory stats I'd almost be tempted to lower the maxmem setting for SQL Server, say down to 24GB. If you can isolate the process that's using all the external (to SQL Server) memory that would help make that decision, and you'd need to look at stats other than just the memory.
One thing that hasn't been mentioned is whether the server is running 32-bit or x64. I've seen AWE on 32-bit machines cause the OS to work harder while SQL Server thinks everything is OK, because the OS is continually swapping AWE "pages", which is invisible to SQL Server.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply