December 29, 2008 at 1:21 pm
Hi,
We have SQL Server 2005 EE x64 a\a\p cluster setup on windows 2003 EE x64.
Node1 has i insatnce
Node2 passive
Node3 Has 4 insatnces.
We 16 GB RAM on all nodes.
For setting up Min and Max memory, I found one document. Plz see the document and advice me.
and also recommend me the size of pagefile?
Min and Max Sever Memory:
The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine. The buffer pool does not immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only the memory required to initialize. As the Database Engine workload increases, it keeps acquiring the memory required to support the workload. The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.
Lock Pages in Memory:
A windows security setting. Having this setting set for SQL basically means that SQL’s resource allocation will only increase until it reaches the Max Server Memory setting. SQL will never release RAM back to the operating system. SQL Server 2005 does respond to memory pressure both when lock pages in memory is enabled and disabled. However there is a caveat. When operating system hits memory pressure, depending on the state of the machine and applications, it could be really slow - attempting to free up the resources. This means that SQL Server might not get enough CPU cycles to respond to memory pressure quickly and hence causing other applications and possibly operating system to return out of memory errors. In case when lock pages in memory is not enabled it is possible that operating system will page out SQL Server. This situation might result in inappropriate SQL Server performance.
Average Server Memory:
Average server memory will be calculated by taking the average of Total Server Memory statistic for the peak time of the recorded period.
-MinSM = Min Server Memory
-MaxSM = Max Server Memory
-ASM = Average Server Memory
-LPIM = Lock Pages In Memory
There are several factors which need to be taken into consideration before any configuration or restrictions can be put into place. The total amount of memory available to the applications needs to be known (this will usually be total server memory minus 2GB for the operating system). The purpose of the server needs to be known and analyzed. The implementation and use of LPIM will be based on several factors (discussed below). If there are multiple applications and or multiple instances of SQL server then SLA’s, importance and priority of the application and or instance need to be known as well as proper monitoring needs to be put into place to derive the numbers that will help configure this application.
To review, the configuration and memory allocation for a production server:
-Total amount of physical memory on server
-The purpose of the server
-Determining factors for memory allocation:
oResults from Analysis
oSLA for each application on the server
oPriority/Importance of application/instance
oLPIM
Server is running only SQL:
Server is only running one instance of SQL server, LPIM will be enabled
•Server is running multiple instances of SQL, LPIM can be enabled - only after proper settings have been monitored and configured
-Server is running SQL and other applications
•Other application has memory management, LPIM can be enabled - only after proper settings have been monitored and configured
•Other application does not have memory management, LPIM will not be enabled
Analysis
In order to determine proper settings for MinSM, MaxSM and LPIM, the following needs to occur:
-System Monitor (perfmon) will record data no less than 48 hours to retrieve the following statistics:
oSQLInstance:Memory Manager: Total Server Memory (KB)
To retrieve the amount of memory used by the SQL instance
•A counter log will be recording this statistic between the peak hours defined
oMemory: Available Mbytes
To retrieve the total available memory
oPaging File: % Usage: _Total
To retrieve the percent of page file used
oPaging File: % Peak: _Total
SQL configuration
SQL configuration is the implementation of memory restrictions based on server configuration.
MinSM
Will be calculated by subtracting 10% from ASM
-.
MaxSM
Will be calculated by adding 10% on to ASM
Thanks
December 29, 2008 at 3:10 pm
From the analysis, I got following values for Average Server Memory and page file usage as below
On node1:
INS1
Average Server Memory = 14442632 KB
Paging File(\??\C:\pagefile.sys)\% Usage = 26.87484726
Paging File(\??\C:\pagefile.sys)\% Usage Peak =80.45805841
On node3:
INS2
Average Server Memory =4321536
Paging File(\??\C:\pagefile.sys)\% Usage =2.183948864
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.979372862
INS3
Average Server Memory =2652160
Paging File(\??\C:\pagefile.sys)\% Usage =2.371051747
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.979372862
INS4
Average Server Memory =376064
Paging File(\??\C:\pagefile.sys)\% Usage =2.370860826
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.979372862
INS5
Average Server Memory =255744
Paging File(\??\C:\pagefile.sys)\% Usage =2.369142534
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.979372862
Thank You
December 29, 2008 at 3:41 pm
is it absolutely necessary that node 3 has 4 instances of SQL. This will affect the RAM configuration on that node
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 29, 2008 at 4:03 pm
Yes, it is necessary to keep 4 insatnces on node3 as it is already in production...
plz advice me from the above analysis,what will be the best values for min max memory values for all of the 5 insatnces
Thank You
December 29, 2008 at 7:24 pm
Firstly split two of the instances off to the passive node, why waste all those resources? That way you can tweak the memory upwards on the others.
Start there and see how everything looks from that point and reassess.
December 30, 2008 at 10:40 am
Hi,
could you plz explain me the what is mean by the Paging File(\??\C:\pagefile.sys)\% Usage = 26.87484726 and Paging File(\??\C:\pagefile.sys)\% Usage Peak =80.45805841 and based on these valuse what will be the page file size? Our current page file size is 2046MB.
and also Page File Bytes Peak shows 17Gb.
"(Pacific Standard Time)(480)Process(_Total)\Page File Bytes Peak"
"12/23/2008 09:07:47.288""17152446464"
"12/23/2008 09:08:02.303""17138184192"
"12/23/2008 09:08:17.304""17138184192"
"12/23/2008 09:08:32.319""17141444608"
"12/23/2008 09:08:47.320""17142468608"
"12/23/2008 09:09:02.335""17142468608"
"12/23/2008 09:09:17.336""17138184192"
"12/23/2008 09:09:32.336""17149186048"
"12/23/2008 09:09:47.336""17139036160"
"12/23/2008 09:10:02.336""17139163136"
"12/23/2008 09:10:17.336""17096925184"
"12/23/2008 09:10:32.337""17096925184"
"12/23/2008 09:10:47.352""17096925184"
Average Server Memory = 14442632 KB
Paging File(\??\C:\pagefile.sys)\% Usage = 26.87484726
Paging File(\??\C:\pagefile.sys)\% Usage Peak =80.45805841
Process(_Total)\Page File Bytes Peak =17139163136
Thank You
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply