August 28, 2012 at 2:54 am
Hi guys, i need to some inputs on how to reduce SQL Server memory usage. Thanks in advance.
August 28, 2012 at 3:00 am
use sp_configure to set the max memory value. Note performance could be affected. Change 4096 to what value in MB you want sql to use the most
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO
August 28, 2012 at 3:02 am
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapter 4 - Memory Management
August 28, 2012 at 3:06 am
And just a note. SQL uses memory in order to cache portions of the database so that it doesn't have to re-read them from disk all the time. It's supposed to use a lot (relatively) of memory.
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
August 28, 2012 at 3:10 am
Thanks guys.. currently the max server memory is 28672 MB.
August 28, 2012 at 3:15 am
How much memory does the server have and why do you want to reduce the memory usage?
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
August 28, 2012 at 3:22 am
just want to ask if we have standard max memory? is 4096 is the standard? The total RAM memory is 35 gb.
August 28, 2012 at 3:25 am
the percentage of SQL memory usage has an average of 95% so I want to reduce it to improve the SQL performance.
August 28, 2012 at 3:35 am
4096 is most certainly NOT a standard. Please read the chapter that Anthony posted.
Reducing memory usage will not improve performance. It'll probably degrade performance. SQL uses its memory to cache data so that it doesn't have to go to the slow disks and to cache plans so that if does not have to recompile them.
The max server memory that you have set there looks good for the memory you have. Unless you have a good reason (like available memory below the recommended amount), don't change it.
If you were to reduce SQL's memory usage to 4GB, SQL would perform terribly and there would be 30 or so GB memory on the server going completely unused. That's just a waste of expensive hardware.
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
August 28, 2012 at 3:41 am
It means the average 95% memory usage is normal?
August 28, 2012 at 4:55 am
How much memory is used is dependant on what you set for max server memory and what else runs on the server. Please read the chapter that Anthony linked.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply