SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Managing MaxMemory Values on an Active-Active Two Node Cluster


Managing MaxMemory Values on an Active-Active Two Node Cluster

Author
Message
RML51
RML51
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1701 Visits: 1612
Comments posted to this topic are about the item Managing MaxMemory Values on an Active-Active Two Node Cluster



Prudhviraj
Prudhviraj
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 102
That's a good article :-)

Can add one more line - set schedule for jobs 1,2 and 3 to one time(run on demand).
RML51
RML51
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1701 Visits: 1612
Prudhviraj (10/23/2013)
That's a good article :-)

Can add one more line - set schedule for jobs 1,2 and 3 to one time(run on demand).

Hi Prudhviraj!

Glad to hear you liked the article.

You certainly could create a job schedule (on demand) for those jobs, but I don't think it is necessary. They run when called by the "Alert and set MaxMemory on Restart" job. Would you please elaborate and state why you want to add an "on demand" schedule for those jobs?

Mike



B's-Data
B's-Data
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 544
Interesting article. I'm questioning the comment regarding "wasting memory" when the max memory setting is split between the two instances. Technically you would NOT want to split that memory evenly as you could choke the OS. Agreed that if the second instance is not running on the other node, then it be construed that the memory is being wasted, however; SQL will only pull the memory that it needs. It does not automatically assign that max memory to the server and reserve it. Actually if the OS needs some, SQL will release the memory back to the OS. I have seen an instance not start because there was insufficient memory but that is not that easily reproducible. The min memory setting does however have the ability to cause a problem as you are referring to.
krystian
krystian
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 77
Excellent article
colin.s.allen
colin.s.allen
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 398
I would expect most instances to reach the MAXMEM setting at some point unless the DB is smaller than the memory available and SQL Server will not always give the memory back to the OS quickly enough, causing paging. Being cautious, I would be concerned about expecting two instances to run in half the memory they normally run in and would probably prefer them to run at the smaller setting permanently, with the option to increase it manually if you are doing something memory intensive as a special case.
SQLVoila
SQLVoila
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 185
Very interesting and great idea to assign max memory dynamically! Thanks for sharing.

We currently have a cluster failover alert job runs every 1 minute to check whats the current physical node of each instance. A simple record is kept in a DBA database table. This way we do not have to deal with enable agent start-up job or using _xp to reach file system. I guess I could easily expand our routine to incorporate this procedure to assign max memory settings upon failover.
lptech
lptech
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 3418
A couple of things caught my eye:

1) The jobs would run if the cluster was actually running active/active with the instances running on the opposite nodes.

2) After the failback, the job has to be manually run. This would add an extra thing to keep track of during routine maintenance such as applying security hotfixes.

3) Keeping everything up-to-date on more than a few servers is a pain, and not likely to happen 100% of the time in a medium to large size shop

In an ideal world, these jobs would be scripts that run off of alerts from the monitoring tool. The node and memory information could be kept in a Configuration management Database (CMDB), with blackouts in effect during maintenance windows. Of course, we don't always live in the ideal world.
RML51
RML51
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1701 Visits: 1612
lptech (10/24/2013)
A couple of things caught my eye:

1) The jobs would run if the cluster was actually running active/active with the instances running on the opposite nodes.

2) After the failback, the job has to be manually run. This would add an extra thing to keep track of during routine maintenance such as applying security hotfixes.

3) Keeping everything up-to-date on more than a few servers is a pain, and not likely to happen 100% of the time in a medium to large size shop

In an ideal world, these jobs would be scripts that run off of alerts from the monitoring tool. The node and memory information could be kept in a Configuration management Database (CMDB), with blackouts in effect during maintenance windows. Of course, we don't always live in the ideal world.


The job “Alert and Set MaxMemory on Restart” should be scheduled to “Start automatically whenever the SQL Server Agent starts”. That will fire off the jobs to set MaxMem whenever a fail-over occurs, or if you manually move an instance to a node. You should not have to manually run any of the jobs.



qiyuef
qiyuef
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 103
There should be a logic in the job to check if the fail over happened. otherwise , it will keep reset the MAXMemory setting to the same value.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search