April 26, 2016 at 7:42 am
Thanks for your answer, one last question i would like to know is, if SQL Server like to consume whole available memory then why it is not consuming whole RAM at Server Start up/restart, it increases day after day by 4-6 GB and then it stays at 63 GB, why it can't allocate to buffer at server startup.
Think about it this way. Suppose you only had a 10GB database on this server and that's it. Wouldn't it be a total waste to grab the max RAM when it couldn't possibly actually be used for anything?? So it just puts pages into RAM as (or right before in the case of read-aheads) they are needed. Then it uses algorithms to try to keep the proper pages in memory for optimal access performance. Trust me when I say that some REALLY smart people have spent a LOT of effort working on the SQL Server relational engine. It isn't perfect, but it is exceptionally good, especially considering the nearly infinite array of things it needs to deal with.
Your box looks fine from a memory perspective. Now go fix the umpteen other things you need to deal with, like that SQL 2012 RTM thing we keep hammering at, and the BAD SQL Server defaults you may not have changed, etc. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 26, 2016 at 8:33 am
GilaMonster (4/26/2016)
Grant Fritchey (4/26/2016)
I do not recommend this because it will seriously negatively impact your startup time, but if you really want it to immediately use the memory you allocate for it, you can set the minimum to the higher value. Again, this is not recommended, but it is how you would do it..Sorry, but that's not what will happen.
Minimum isn't what's allocated at startup. It's the value that, once allocated, SQL will not drop memory usage below. It'll still ramp memory up the same way it does with a low min server memory.
The only way to get memory allocated at startup is to use large pages, which I absolutely do not recommend doing without a damn good reason and a lot of testing.
Then why does setting a stupid minimum slow down startup? I thought it was because it allocated the memory.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2016 at 8:53 am
Then why does setting a stupid minimum slow down startup? I thought it was because it allocated the memory.
The engine has a WAITFOR DELAY '00:02:23' built into it that only fires when you set the "stupid minimum" setting!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 26, 2016 at 8:53 am
Grant Fritchey (4/26/2016)
GilaMonster (4/26/2016)
Grant Fritchey (4/26/2016)
I do not recommend this because it will seriously negatively impact your startup time, but if you really want it to immediately use the memory you allocate for it, you can set the minimum to the higher value. Again, this is not recommended, but it is how you would do it..Sorry, but that's not what will happen.
Minimum isn't what's allocated at startup. It's the value that, once allocated, SQL will not drop memory usage below. It'll still ramp memory up the same way it does with a low min server memory.
The only way to get memory allocated at startup is to use large pages, which I absolutely do not recommend doing without a damn good reason and a lot of testing.
Then why does setting a stupid minimum slow down startup? I thought it was because it allocated the memory.
Shouldn't allocating memory be an incredibly cheap operation assuming it's available?
April 26, 2016 at 9:41 am
info.sqldbamail (4/26/2016)
I have a CMEMThread poison wait is that a problem of NUMA configuration/Memory Configuration.
I have had this issue and it turned out to be user defined scalar functions operating on a datawarehouse during a data load, but only when a colleague ran a trace.
The solution was Table valued functions and built in functions and the CMEMTHREAD waits disappeared.
April 26, 2016 at 10:26 am
Grant Fritchey (4/26/2016)
GilaMonster (4/26/2016)
Grant Fritchey (4/26/2016)
I do not recommend this because it will seriously negatively impact your startup time, but if you really want it to immediately use the memory you allocate for it, you can set the minimum to the higher value. Again, this is not recommended, but it is how you would do it..Sorry, but that's not what will happen.
Minimum isn't what's allocated at startup. It's the value that, once allocated, SQL will not drop memory usage below. It'll still ramp memory up the same way it does with a low min server memory.
The only way to get memory allocated at startup is to use large pages, which I absolutely do not recommend doing without a damn good reason and a lot of testing.
Then why does setting a stupid minimum slow down startup?
No idea. It shouldn't.
Haven't got a machine with enough memory to test it out on.
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
April 26, 2016 at 11:21 am
GilaMonster (4/26/2016)
Grant Fritchey (4/26/2016)
GilaMonster (4/26/2016)
Grant Fritchey (4/26/2016)
I do not recommend this because it will seriously negatively impact your startup time, but if you really want it to immediately use the memory you allocate for it, you can set the minimum to the higher value. Again, this is not recommended, but it is how you would do it..Sorry, but that's not what will happen.
Minimum isn't what's allocated at startup. It's the value that, once allocated, SQL will not drop memory usage below. It'll still ramp memory up the same way it does with a low min server memory.
The only way to get memory allocated at startup is to use large pages, which I absolutely do not recommend doing without a damn good reason and a lot of testing.
Then why does setting a stupid minimum slow down startup?
No idea. It shouldn't.
Haven't got a machine with enough memory to test it out on.
Yeah, it's been a while since I ignorantly did this on a large scale machine. There may have been other issues at play as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply