asked
How the server Memory configuration works at windows level and at Sql server level?
I read a bit about Max memory, it make sense to me that the Maximum servery memory makes the sql server Up and run properly and i looked at few examples in MSDN, but I would like to learn bit more about This, In What kind of situations we could Manually change the Memory Value,How does this effect the sql server and, i mean what determines us to change the memory value? What are the advantages and disadvantages of changing/re configuring Memory Values?
The quick answer is the old standby of "it depends" as no one can be sure of the situation until some baselines/observations are taken. I believe you should always set it at install to at least 2-4 GB less than total RAM to ensure the O/S cannot be starved out - based on your installed RAM. If you're running other necessary applications on the same server then reduce MAXMEM to accommodate the other applications' memory requirements. The advantages are that everything will run with the RAM they require and your system will run smooth. Disadvantages are that you will set it wrong and either 1) SQL Server won't have enough RAM to cache stuff in and queries will hit the disks and SQL Server will seem slow or 2) you'll have set it the other way and other apps will be RAM-starved and their performance will suffer. By the way... other applications can include other instances of SQL Server on the server - keep that in mind and add all your memory requirements when setting MAXMEM. Other than that SQL Server does play fairly nicely and will "give up" RAM it has but I prefer to have some control and expectations :)
I think Jonathan Kehayias has some of the best information on why and how you should set up your maximum memory. His suggestions are very conservative and safe as well as being backed by tons of knowledge and very solid experimentation. I'd suggest going and [reading this article][1] on his blog to fully understand managing SQL Server memory. [1]:
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
16 People are following this question.
