December 29, 2011 at 10:36 pm
Hi All,
Currently we have following configuration on two node Active\Active cluster , each running two instances each
Environment Details
Windows 64 bit 2003
SQL 2008 32 bit
SQL server MAX setting for each instance 14336GB
RAM 32GB
CPU 16 2266 MHZ
Observation
- The availbale memory has always been less 2GB for whole week.
- Commit charge always equal to 32-34GB
- My collegue observed the instances fail when full back up's happen on both instances at same time.Also they occur during OFF PEAK hours.
- So he tweaked the AFFINITY MASK to 65345 for two instances.
-
My Understanding
- Since these server's have less Available memory and Paging is high , this change would not make any difference
- This week owing to vacation plans the number of user's hitting the databases are less, so for now instances have not failed.
- AFFINITY MASK setting may not be right one to resolve this, may be AFFINITY IO MASK may help since backups are IO intensive operations but memeory issues have to be resolved before going this path.
Please do suggest
Cheers
Satish
Cheer Satish 🙂
January 4, 2012 at 3:02 am
are there any memory related errors on SQL error log?
What is the SQL Patch version?
Do you have perfmon or memory monitoring enabled. Does it always failover when the memory is low
Regards,
Raj
January 4, 2012 at 7:30 am
SQL 2008 r2
The tool which looks at memory also showed low Free memory Less than 1GB
Also Paging errors reported with Commited Bytes > Available memory.
Cheers
Satish
Cheer Satish 🙂
January 9, 2012 at 4:18 am
It looks like you are over-committing memory. Wit ha 64-bit machine ther is no performance advantage in trying to use more memory than exists on the box. Although Windows needs a paging file in order to operate at peak performance, you should set memory requirements so that nothing much needs to be written to it.
As part of this, you need to set SQL Server maximum memory value. The value you use must allow enough space for the operating system and any applications you run outside of SQL Server (this includes backups). Typically you need to allow a minimum of 10% for Windows, and between 10% to 20% for non-SQL applications.
A good starting point for a non-clustered SQL instance on a 32GB box is to set SQL memory to 25GB, and fine-tune so you maximise SQL memory without overcommitting total server memory. On your A/A cluster you need to make a decision about what you want to do if both instances end it on the same node. If you want to do nothing, then you need to set the max memory for each instance to that the total is not greater than 25GB. If you are prepared to accept a performance penalty at failover then write a script that runs at failover time to adjust server memory - then you can have each instance running normally with 25GB (each on its own node) and cut it down if a failover happens.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply