Determining Memory Pressure

Question

Determining Memory Pressure

Arsh

SSCertifiable

Points: 6345
More actions
September 13, 2017 at 5:33 am

#332567

Dear Experts,
I'm trying to figure out if my SQL server is facing any memory pressure . Its a 64 GB memory server with one SQL instance with 50 B max memory. Though the buffer usage stats show that all the databases put together use up around 42 GB , the task manager shows a usage of 56 GB . This is server is dedicated for one instance of SQL server with 7 databases , only one of them (it uses 34 GB) is most crucial and much bigger than the others . Not sure what is consuming the additional memory .Any comments on this ? Thank you.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Arsh - Wednesday, September 13, 2017 5:33 AM
Dear Experts,
I'm trying to figure out if my SQL server is facing any memory pressure . Its a 64 GB memory server with one SQL instance with 50 B max memory. Though the buffer usage stats show that all the databases put together use up around 42 GB , the task manager shows a usage of 56 GB . This is server is dedicated for one instance of SQL server with 7 databases , only one of them (it uses 34 GB) is most crucial and much bigger than the others . Not sure what is consuming the additional memory .Any comments on this ? Thank you.

SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use. Its not an indication of memory pressure for it to do so.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Arsh - Wednesday, September 13, 2017 5:33 AM
Dear Experts,
I'm trying to figure out if my SQL server is facing any memory pressure . Its a 64 GB memory server with one SQL instance with 50 B max memory. Though the buffer usage stats show that all the databases put together use up around 42 GB , the task manager shows a usage of 56 GB . This is server is dedicated for one instance of SQL server with 7 databases , only one of them (it uses 34 GB) is most crucial and much bigger than the others . Not sure what is consuming the additional memory .Any comments on this ? Thank you.

Max memory does not limit all memory used by SQL Server - it applies to the buffer pool but does not consider other components using memory such as threads, clr, third party dlls, linked servers, sp_OA and extended stored procedures, CLR, etc. So it's not unusual to see the memory usage more than the max memory setting.
This article runs through some of those - it applies to SQL Server 2008. Not sure if you are 32 or 64 bit but check the section for your architecture:
Basics of SQL Server Memory Architecture

It sounds like you are using sys.dm_os_buffer_descriptors which will tell you the distribution of the memory in the buffer pool but for memory pressure, you may want to look at exploring sys.dm_os_ring_buffers. Jonathan Kehayias has some good articles and sample queries for looking at memory pressure:
Identifying External Memory Pressure with dm_os_ring_buffers and RING_BUFFER_RESOURCE_MONITOR
Wowâ€¦ An online calculator to misconfigure your SQL Server memory!

Sue

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

Jeff Moden - Wednesday, September 13, 2017 6:33 AM
Arsh - Wednesday, September 13, 2017 5:33 AM
Dear Experts,
I'm trying to figure out if my SQL server is facing any memory pressure . Its a 64 GB memory server with one SQL instance with 50 B max memory. Though the buffer usage stats show that all the databases put together use up around 42 GB , the task manager shows a usage of 56 GB . This is server is dedicated for one instance of SQL server with 7 databases , only one of them (it uses 34 GB) is most crucial and much bigger than the others . Not sure what is consuming the additional memory .Any comments on this ? Thank you.
SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use. Its not an indication of memory pressure for it to do so.

I didn't think that was the case. That is, that SQL will acquire additional memory for buffers only when it needs it, not simply because its max memory allows it to.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

ScottPletcher - Wednesday, September 13, 2017 10:27 AM
Jeff Moden - Wednesday, September 13, 2017 6:33 AM
Arsh - Wednesday, September 13, 2017 5:33 AM
Dear Experts,
I'm trying to figure out if my SQL server is facing any memory pressure . Its a 64 GB memory server with one SQL instance with 50 B max memory. Though the buffer usage stats show that all the databases put together use up around 42 GB , the task manager shows a usage of 56 GB . This is server is dedicated for one instance of SQL server with 7 databases , only one of them (it uses 34 GB) is most crucial and much bigger than the others . Not sure what is consuming the additional memory .Any comments on this ? Thank you.
SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use. Its not an indication of memory pressure for it to do so.
I didn't think that was the case. That is, that SQL will acquire additional memory for buffers only when it needs it, not simply because its max memory allows it to.

Ugh! My apologies for the bad info and I stand corrected. I'm not sure why I used the word "pre-allocate" because that's definitely incorrect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 5

ScottPletcher - Wednesday, September 13, 2017 10:27 AM
Jeff Moden - Wednesday, September 13, 2017 6:33 AM
Arsh - Wednesday, September 13, 2017 5:33 AM
Dear Experts,
I'm trying to figure out if my SQL server is facing any memory pressure . Its a 64 GB memory server with one SQL instance with 50 B max memory. Though the buffer usage stats show that all the databases put together use up around 42 GB , the task manager shows a usage of 56 GB . This is server is dedicated for one instance of SQL server with 7 databases , only one of them (it uses 34 GB) is most crucial and much bigger than the others . Not sure what is consuming the additional memory .Any comments on this ? Thank you.
SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use. Its not an indication of memory pressure for it to do so.
I didn't think that was the case. That is, that SQL will acquire additional memory for buffers only when it needs it, not simply because its max memory allows it to.

A more fair statement might be that SQL Server will grab memory as needed but do it's damnedest never to give it up once taken 😛

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jeff Moden - Wednesday, September 13, 2017 6:33 AM
SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use.

Not in current versions. It used to be the case that min server memory was allocated on startup, but that hasn't been the case for a long time.
SQL will take what it needs, if it needs more and hasn't reached max server memory yet, it will allocate more. It will aggressively ramp up the buffer pool initially, but it won't grab more than it needs.
That said, there's more that needs memory than just the data cache.

The only time it pre-allocates is when large pages are used, as memory allocated that way cannot be resized after allocation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

GilaMonster - Wednesday, September 13, 2017 11:28 AM
Jeff Moden - Wednesday, September 13, 2017 6:33 AM
SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use.
Not in current versions. It used to be the case that min server memory was allocated on startup, but that hasn't been the case for a long time.
SQL will take what it needs, if it needs more and hasn't reached max server memory yet, it will allocate more. It will aggressively ramp up the buffer pool initially, but it won't grab more than it needs.
That said, there's more that needs memory than just the data cache.
The only time it pre-allocates is when large pages are used, as memory allocated that way cannot be resized after allocation.

Thanks and I appreciate the extra info, Gail. Like I said in my post after Scott's, I'm not sure why I used the word "pre-allocate" in my post. Must've been a bad beer popsicle or something. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Arsh SSCertifiable Points: 6345 More actions · Answer 8

GilaMonster - Wednesday, September 13, 2017 11:28 AM
Jeff Moden - Wednesday, September 13, 2017 6:33 AM
SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use.
Not in current versions. It used to be the case that min server memory was allocated on startup, but that hasn't been the case for a long time.
SQL will take what it needs, if it needs more and hasn't reached max server memory yet, it will allocate more. It will aggressively ramp up the buffer pool initially, but it won't grab more than it needs.
That said, there's more that needs memory than just the data cache.
The only time it pre-allocates is when large pages are used, as memory allocated that way cannot be resized after allocation.

Thanks Gail, Jeff and ZZartin for sharing your valuable thoughts on this.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Arsh - Thursday, September 14, 2017 2:44 AM
GilaMonster - Wednesday, September 13, 2017 11:28 AM
Jeff Moden - Wednesday, September 13, 2017 6:33 AM
SQL Server will do its damnedest to pre-allocate all of the memory that it has been allowed to use.
Not in current versions. It used to be the case that min server memory was allocated on startup, but that hasn't been the case for a long time.
SQL will take what it needs, if it needs more and hasn't reached max server memory yet, it will allocate more. It will aggressively ramp up the buffer pool initially, but it won't grab more than it needs.
That said, there's more that needs memory than just the data cache.
The only time it pre-allocates is when large pages are used, as memory allocated that way cannot be resized after allocation.
Thanks Gail, Jeff and ZZartin for sharing your valuable thoughts on this.

Just because you quoted it, let me say again that I shouldn't have used the word "pre-allocate" in my post because that's incorrect. As Gail said, it's pretty aggressive in allocating memory to itself but it's when there's a need. It doesn't do pre-allocation except for the relatively low amount that the instance minimum memory setting is set to. If that's high, that may be the cause of what you're seeing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

roger.plowman SSChampion Points: 10265 More actions · Answer 10

You might want to look into buying Redgate's SQL Monitor program. A little pricy but it lets you monitor your databases and servers, including memory pressure, I/O bottlenecks, and a lot more.

Even better, it explains what each metric is and tells you either what the expected good range of values is, or how to establish a baseline for it.

Gives you pretty graphs too! 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Jeff Moden - Thursday, September 14, 2017 6:52 AM
It doesn't do pre-allocation except for the relatively low amount that the instance minimum memory setting is set to. If that's high, that may be the cause of what you're seeing.

Nope.

SQL has not allocated min server memory automatically since, I think, SQL 2000. Min is the amount, once allocated, that the allocation will not drop below. It is not the amount allocated at startup, or shortly after.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

GilaMonster - Thursday, September 14, 2017 12:09 PM
Jeff Moden - Thursday, September 14, 2017 6:52 AM
It doesn't do pre-allocation except for the relatively low amount that the instance minimum memory setting is set to. If that's high, that may be the cause of what you're seeing.
Nope.
SQL has not allocated min server memory automatically since, I think, SQL 2000. Min is the amount, once allocated, that the allocation will not drop below. It is not the amount allocated at startup, or shortly after.

Yowch. Ok... apparently I've lost some memory so I'm going to make some beer popsicles and call it a day. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)