kdarty
11/10/2015 - 12:58 PM

If you find that processes running in SQL Server tend to make it peg out your system's available RAM it is likely because the default settin

If you find that processes running in SQL Server tend to make it peg out your system's available RAM it is likely because the default settings for SQL Server say "use all available RAM". While that is nice it is like having 5 children in the house and 4 pieces of candy and telling one of the children they can have all they want. Somebody in this scenario is going to be unhappy and in the case of SQL Server, one process taking up all the system's available resources will result in poor performance and possible errors for other processes. Glenn Berry has a good starting point for adjusting the MaxServerMem setting in SQL Server to help you get started on cleaning things up.

Set-Max-Memory-SQL-Server.sql
content_copyfile_download
/****************************************************************************************************************************/
/* Source Article:                                                                                                          */
/* http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/ */
/****************************************************************************************************************************/

-- Turn on advanced options
EXEC  sp_configure'Show Advanced Options',1;
GO
RECONFIGURE;
GO

-- Current Server has 8GB of RAM allocated
-- Set max server memory = 6400MB for the server
EXEC  sp_configure'max server memory (MB)',6400;
GO
RECONFIGURE;
GO

-- See what the current values are
EXEC sp_configure;