Memory settings in MS SQL server and performance

12 Feb

Suggested memory settings:

Physical RAM                  MaxServerMem Setting
2GB                                           1500
4GB                                          3200
6GB                                          4800
8GB                                          6400
12GB                                        10000
16GB                                        13500
24GB                                       21500
32GB                                       29000
48GB                                       44000
64GB                                       60000
72GB                                       68000
96GB                                       92000
128GB                                     124000

Turn on advanced options:
EXEC  sp_configure‘Show Advanced Options’,1;
GO
RECONFIGURE;
GO

Set max server memory = 3500MB for the server:
EXEC  sp_configure‘max server memory (MB)’,3500;
GO
RECONFIGURE;
GO

See what the current values are:
EXEC sp_configure;

 

Read more

http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm

http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

http://sportstoday.us/technology/sql-server-2012—optimizing-sql-server-memory-configuration—min-and-max-server-memory,-optimize-for-ad-hoc-workloads-.aspx

http://dba.stackexchange.com/questions/47472/is-there-a-standard-formula-to-calculate-the-optimal-resource-required-by-sql-se/47475#47475

http://dba.stackexchange.com/questions/49420/sql-server-how-to-determine-ideal-memory-for-instance

http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm

http://technet.microsoft.com/en-us/library/ms188787.aspx

http://msdn.microsoft.com/en-us/library/bb510747.aspx

http://dba.stackexchange.com/questions/35418/why-is-sql-server-memory-showing-in-task-manager

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: