January 12, 2009 at 11:39 am
Hi,
We have a/a/p cluster setup. Node 1 has 1 instance and node 3 has 4 instances. we have
Windows 2003 EE x64 and sql server 2005 EE x64. These are dedicated sql servers with antivirus softare with 16 GB RAM and 2046MB page file.
My question is Considering windows 2003 EE and sql server EE x64 having 16 GB RAM is less?
What would be the initial memory to start with if every thing is 64 bit?
January 12, 2009 at 12:58 pm
I think I figure what you're asking:- with multiple instances you must fix the memory each instance will use, for x64 I'd leave 8Gb for the o/s so i'd set max memory for each instance to 2GB.
It is vitally important with x64 that you always set the max memory that sql server can use - you will have problems if you don't. You might want to lock pages in memory, but if nothing else is likely to use the memory it's not essential. I've tested memory use and if you don't set memory sql will use everything except 256MB - which si not good.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 12, 2009 at 1:00 pm
I'd personally increase memory on the boxes, but it depends upon each instance use I guess. Monitor your page file for usuage to see if 2gb is adequate. Sorry couldn't remember whole question.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 12, 2009 at 5:04 pm
Thanks Colin,
I setup performance counters for all the instances in all the 3 nodes. Below are the results, based on these results could you plz advice that do we really need to increase the page file to 1.5 times physical RAM?Give me your suggestions
On node1:default instance
Paging File(\??\C:\pagefile.sys)\% Usage =27.31262983
Paging File(\??\C:\pagefile.sys)\% Usage Peak =80.45805841
SQLServer:Memory Manager\Total Server Memory (KB)=14463392
Memory\Available MBytes =273MB
On node3:
1st instance:
Paging File(\??\C:\pagefile.sys)\% Usage =2.189103739
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.979372862
SQLServer:Memory Manager\Total Server Memory (KB)=4377856
Memory\Available MBytes =6095MB
2nd instance:
Paging File(\??\C:\pagefile.sys)\% Usage =2.18929466
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.979372862
SQLServer:Memory Manager\Total Server Memory (KB) =2915328
Memory\Available MBytes =6095MB
3rd instance:
Paging File(\??\C:\pagefile.sys)\% Usage =2.18929466
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.979372862
SQLServer:Memory Manager\Total Server Memory (KB)=408320
Memory\Available MBytes =6095MB
4th instance:
Paging File(\??\C:\pagefile.sys)\% Usage =2.18929466
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.979372862
SQLServer:Memory Manager\Total Server Memory (KB)=279808
Memory\Available MBytes =6095MB
And we also have virtual memory problem. from the monitoring tool we are using:
Vertual memory:
Max size =18GB
Free =1.84GB
free% =10%
toatal amount of used virtual memory is 16.2GB. This means do we need to increase the page file size?or need to increase the physical memory(RAM)?
Thanks
Madhu
January 13, 2009 at 3:06 pm
Hi Colin,
could you please give some idea based on these performance counters readings.I will appreciate all your help.
Thanks
January 13, 2009 at 4:41 pm
well I wouldn't worry too much about what your monitoring tool says!! If you've set the memory for sql server correctly then memory should be good. You ideally don't want to use any virtual memory at all - it's very slow. Best recommendation for a page file is to put it on fast disks/array if it is going to be used. I'd probably increase the size of the page file on the node which is reporting peak 80% usage, maybe increase it to 3GB.
You might like to get hold of Process explorer which will help you see which processes are using memory and which are using the page file.
http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
You might also like to read this
http://support.microsoft.com/kb/918483
and this
It goes without saying you should also stop any services which are not needed on your server - if you scan my blog I've posted a couple of posts about services and drivers too.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 13, 2009 at 5:41 pm
Thank You,
On node1:default instance
Paging File(\??\C:\pagefile.sys)\% Usage =27.31262983
Paging File(\??\C:\pagefile.sys)\% Usage Peak =80.45805841
SQLServer:Memory Manager\Total Server Memory (KB)=14463392
Memory\Available MBytes =273MB
Here We took the average of total server memory at peak hours and It is equal to 14463392.
One of the document for memory configuration best practices(Created by some expert dba's) which says that:
MinSM
Will be calculated by subtracting 10% from ASM
?Average sever memory was a statistic derived from the average memory usage of the SQL instance during its peak time. By removing 10% from that average, it gives us a ‘best guess’ to configure the lowest amount of memory we would want running for that particular instance.
MaxSM
Will be calculated by adding 10% on to ASM
Average sever memory was a statistic derived from the average memory usage of the SQL instance during its peak time. By adding 10% from that average, it gives us a ‘best guess’ to configure the most amount of memory we would want running for that particular instance
So, I would like follow this and set the Max and Min values accordingly. Could you plz tell me whether this settings will help.
and Paging File(\??\C:\pagefile.sys)\% Usage Peak =80.45805841 means is using page file much?
Thanks
January 14, 2009 at 9:46 pm
Hi,
I just tried to set the MAX memory to 12GB out of 16GB RAM and restarted the service.As soon as I set this MAX memory, Iam getting the procedure cache below 68%. Is this mean that we need to increase the memory for SQL Server? So I again changed to default value.
The performance counter values showing big difference after restarting the service:
On node1:Paging File(\??\C:\pagefile.sys)\% Usage =27.31262983
Paging File(\??\C:\pagefile.sys)\% Usage Peak =80.45805841
SQLServer:Memory Manager\Total Server Memory (KB) =1215744
Memory\Available MBytes =13561MB
why this big difference in memory usage after restarting the sql service?
January 18, 2009 at 11:21 pm
yeah Madhu did you resolve this issue with setting up Max memory and page file size? We are also in confusion in setting these values.any help would be appreciated
January 19, 2009 at 12:44 am
Hi,
From BOL,
Failover clustering performance benefits from generous memory allocation. The theoretical maximum memory is the total memory available, less memory for the operating system and the other cluster and local-to-node installed resources, divided by the instances of Microsoft SQL Server installed. Determine and set your maximum memory size using this formula. We recommend that you set your working maximum memory to 80 to 90 percent of this amount, and then gather performance baseline statistics to further optimize the setting
SO, in our case we have 1 instance node1 and 4 instances on node3.
for node3:
Max memory=[Total memory(16GB)+OS(4GB)+other resources(1GB)like Antivirus]/4 is that correct formula??
January 19, 2009 at 8:18 am
I wonder if this post is getting somewhat lost somewhere?
1st point - there is no relation between the page file and sql server or any sql server settings what so ever.
How you set your page file is largely one of choice; with servers having lots of memory compared to the days of NT4 the 1.5 times physical memory is a bit out of touch. Generally you make a choice and then monitor the usuage - you MUST look at the peaks not a day average; for 16GB I'd probably choose 4-6GB for page file, set it fixed and then monitor to see how it is used ( very little I hope ) If it was then critical I'd adjust. If you get a lot of paging on s SQL Server then that is bad news - if your concern is because of a setting in a piece of monitoring software, either fix the threshold or get a better monitoring solution.
2nd point: SQL Memory on a cluster
Each node in a cluster muct be able to support the total memory allocations of all instances, if you don't and get failovers your servers will die. If each node has 16GB of ram then the TOTAL memory used by ALL the instances must be less than 12GB. Clusters and instances are configured the same for memory - there is absolutley no performance gain or otherwise using a cluster - it's merely a failover for hardware ( excluding storage )
3rd point x64 memory
Set max memory, if you don't you will have servers die under load. You may or may not want to lock pages in memory. Setting max memory is more critical.
4th point cache hit ratio(s)
These will stabilise after a period of time - a low proc cache hit ratio is usually indicitive of poor code / plan re-use rather than memory pressures. More memory always helps of course, if you don't have sp2 + at least CU6 applied you may have memory problems.
Finally
4 instances on x64 with 16GB of memory - unless they are small low utilisation databases/instances then I'd say you are around 16GB per node too light on memory.
Hope that clarifies - forget page file, it's a red herring.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 19, 2009 at 10:57 am
thank you very much.....now Iam confident to set all the configuration settings.
I really appreciate your detailed explanation.
January 30, 2009 at 1:21 pm
Hi,
We have a/a/p cluster setup of sql server 2005 ee x64 with SP3 on windows 2003 EE x64.
on Node1 we have one instance and node3 we have 4 instances.
For the instance on node1, we set the max memory as 12 Gb out 16Gb as per the experts suggestions and now everything is fine.
On Node3 we have 4 instances, but we did not set the Min and max memory yet.
from performance counter readings:
Total server memory Instances
229376 (0.256GB) Ins1
1044992(1.05GB) Ins2
353024(0.392GB) ins3
3509248(3.37GB) ins4.
Total memory(for 4 instanecs) =5.068
frequently Iam getting the procedure cache below 78% for all these 4 instances from quest monitoring tool.
So could you please advice what will be the Min and Max values I can set for these instances. The server is dedicated to sql server application only, which stores biztalk application databases in 3 instances and k2 application databases in 4th instance.
Thank You
February 1, 2009 at 7:56 pm
could you please give me some guidelines on setting this Min and Max values for multiple instances on one node
plz advice me
February 3, 2009 at 3:25 pm
Hi,
For the instance on node1, we set the max memory as 12 Gb out 16Gb and now everything is fine. But after a week Iam seeing the message free virtual memory is down to 20% in the monitoring tool. My question is where can I check in sql server if there is really a virtual memory problem?
and also in performance counter readings Iam seeing that total server memory as 12288000KB(12GB).
Paging File(\??\C:\pagefile.sys)\% Usage =2.168675159
Paging File(\??\C:\pagefile.sys)\% Usage Peak =3.518106977
Memory\Available MBytes =2398 MB
SQLServer:Memory Manager\Total Server Memory (KB)=12288000KB
How should we know if SQL Server need more memory because its using all allocated memory(12GB)?
How to get rid of this virtual memory issue?
How to set the 16GB RAM for the 4 instances on Node3?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply