May 21, 2015 at 7:17 pm
HI all ,
I am curious about maximum memory setting .
Should we set maximum memory setting for each SQL server? For example a server has 6 GB memory then should we set maximum memory setting = 3.5 GB ?
cheers
May 21, 2015 at 8:40 pm
Yes, you should set max memory on each instance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2015 at 9:08 pm
Thanks for the reply 🙂
but based on this article from technet
https://technet.microsoft.com/en-us/library/ms178067%28v=sql.105%29.aspx
"Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. "
so does it depend on the server environment? if it is dedicated server and no extra application then we don't need to set maximum setting ?
I am confused ....
May 21, 2015 at 9:15 pm
That article is wrong.
Best Practice is to set max memory.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2015 at 10:14 pm
How do you prove it wrong ?
any article about it ?
May 22, 2015 at 1:00 am
Always set max server memory. Always. Unless you like SQL and the OS to be constantly fighting over memory, which is what they will do if you don't configure it.
http://www.red-gate.com/community/books/accidental-dba Chapter 4
Microsoft articles aren't gospel truth, there are a number of articles on the MS site which are either outright wrong (eg tables are always physically sorted by their clustered index key) or whose recommendations are outdated or plain wrong (eg this one and the tempDB files = core count)
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
May 22, 2015 at 1:24 am
Great . I will read it soon ! Thanks a lot
May 22, 2015 at 4:05 am
If you don't set a maximum you may have the fun of seeing your server flatline because SQL Server has grabbed all of the RAM, and is starving the OS :-D.
There is a thread here which may help:
http://www.sqlservercentral.com/Forums/Topic1520296-391-1.aspx#bm1520756
May 24, 2015 at 7:49 pm
The min server memory option specifies the minimum size to which SQL Server can
shrink the buffer pool when under memory pressure; it does not specify the minimum
amount of memory that SQL Server will initially allocate. During memory ramp up,
the memory usage of an instance slowly increases and the buffer pool is grown to meet
the needs of the requests being executed. The max server memory option specifies
the maximum amount of memory that SQL Server can use for the buffer pool, which is
primarily used for caching data pages in memory.
144
Chapter 4: Memory Management
The appropriate setting for each of these configuration options depends on the SQL
Server implementation. It is common to find recommendations online that state
something along the lines of:
"If a SQL Server is dedicated to running only the SQL Server database engine then, generally, each of these
options can be configured to have the same value, so that the SQL Server allocates memory for the buffer pool
and then does not shrink its memory usage once that level has been met."
I found this from the book that you recommend . ( Please correct me if I am Wrong) In summary , Even if a SQL server is dedicated ( no other application ) we still need to set the maximum memory setting ?
My question is : What if I want to adjust maximum memory Setting ONLY and let the Minimum memory as default ? Is it OK ? and what is the impact ?
May 24, 2015 at 7:54 pm
My team said that even if We don’t set up the maximum memory setting in DEDICATED SQL server , SQL will adjust memory usage dynamically and always leave 1 or 2 GB to OS ..
What do you think guys ?
May 24, 2015 at 9:44 pm
Always set it. It's trivial to grab one of the formulas and just do it (and possibly go back and fine tune it later).
If you don't set it properly you'll get complaints from IT all the time that the server is consuming all of the memory and triggering alerts. "But that's okay for SQL Server" is perfectly true but they'll get grumpy about it.
Setting it properly also gives you the advantage of monitoring the ring buffer for memory events (to know when Windows is oddly requesting that SQL release memory, or when SQL is running out of memory internally) and you'll know it's legit.
May 24, 2015 at 10:14 pm
WhiteLotus (5/24/2015)
My team said that even if We don’t set up the maximum memory setting in DEDICATED SQL server , SQL will adjust memory usage dynamically and always leave 1 or 2 GB to OS ..What do you think guys ?
That is not true and also why leave it to chance? Technet states the memory requirements for Windows Server 2008 R2 as: Minimum: 512 MB RAM, Recommended: 2 GB RAM or greater. This depends on the activities on the server, it will vary and in my experience this figure is too low. Set the Maximum Memory for the SQL Server no higher than (Total Memory - SQL Max Mem) >= (Minimum OS Memory), then monitor the server and adjust accordingly.
😎
May 25, 2015 at 1:11 am
WhiteLotus (5/24/2015)
My team said that even if We don’t set up the maximum memory setting in DEDICATED SQL server , SQL will adjust memory usage dynamically and always leave 1 or 2 GB to OS ..
I'm pretty sure I already told you what I think
GilaMonster (5/22/2015)
Always set max server memory. Always. Unless you like SQL and the OS to be constantly fighting over memory, which is what they will do if you don't configure it.http://www.red-gate.com/community/books/accidental-dba Chapter 4
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
May 25, 2015 at 1:12 am
WhiteLotus (5/24/2015)
My question is : What if I want to adjust maximum memory Setting ONLY and let the Minimum memory as default ? Is it OK ? and what is the impact ?
That's the setting I prefer, Jonathan and I disagreed over the 'set them to the same value', we had a long discussion about it, the book said as you quoted and I still disagree with him on this point. 🙂
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
May 26, 2015 at 8:35 am
WhiteLotus (5/24/2015)
My team said that even if We don’t set up the maximum memory setting in DEDICATED SQL server , SQL will adjust memory usage dynamically and always leave 1 or 2 GB to OS ..What do you think guys ?
That's not true. SQL will grab as much as it can and cause memory contention issues.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply