Memory is cheap and often easy to expand in a modern Windows server. But do not forget that OS needs memory to handle the memory for all processes. Therefore you should never use default memory settings. It is Best Practice to configure SQL to not use all the memory in a server.
Good guidelines for memory allocation:
-Reserve 1 GB RAM for OS
-Reserve additional 1 GB to OS for every 4 GB of RAM installed on the server between 4-16 GB
-Reserve additional 1 GB to OS for each 8 GB of RAM installed on the server over 16 GB of RAM.
Sounds complicated?
Below is a script that does exactly this for you.
If you have a large environment you can use Central Management Server to run this script on many servers simultaneously and in a quick and easy way ensure that your SQL Server has a good memory configuration. This configuration works for most of the SQL Server installations but there are of course servers that needs different memory configuration. When you run this command (sp_configure) it will flush the cache. Some users may think the system is a bit slow for a while until the cache is rebuilt.
If you have SSRS or SSIS or other services that require memory, you need to take into consideration this when assigning memory to SQL services.
(Note that there are two different lines depending on which version of SQL to run the script against)
DECLARE @sql varchar ( max ) |
WHEN @curMem < = 1024*2 THEN @curMem - 512 |
WHEN @curMem < = 1024*4 THEN @curMem - 1024 |
WHEN @curMem < = 1024*16 THEN @curMem - 1024 - Ceiling((@curMem-4096) / (4.0*1024))*1024 |
WHEN @curMem > 1024*16 THEN @curMem - 4096 - Ceiling((@curMem-1024*16) / (8.0*1024))*1024 |
EXEC sp_configure ' 'Show Advanced Options' ',1; |
RECONFIGURE WITH OVERRIDE; |
EXEC sp_configure ' 'max server memory' ',' + CONVERT ( VARCHAR (6), @maxMem)+ '; |
RECONFIGURE WITH OVERRIDE;' |
This script and some explanations is found here:
http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/06/02/max-server-memory/
Läs liknande inlägg
Lock pages in memoryWhile investigating a memory related error on a Windows 2008 based SQL 2008 Enteprise edition…
-
-