SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Determining Memory Pressure


Determining Memory Pressure

Author
Message
Arsh
Arsh
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 473
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339781 Visits: 42624
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sue_H
Sue_H
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21687 Visits: 6720
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
ScottPletcher
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30963 Visits: 7636
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339781 Visits: 42624
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ZZartin
ZZartin
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8824 Visits: 11335
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 Tongue

GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370278 Visits: 46953
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
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339781 Visits: 42624
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Arsh
Arsh
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 473
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
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339781 Visits: 42624
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search