Post reply

Auto Memory control

  • November 29, 2014 at 9:05 am

    #313416

    Intro- Sql server 2008R2 (Ent) cluster Act/Act, both nodes have memory 128 GB and 110 assigned to each sql server instance. Certainly it’s not good as in case of failure they (instances) will compete for memory.

    Goal- create mechanism (job, etc) that will decrease sql memory to let say 70/40 in case of cluster failover to one node and then increase back to 110/110 when resources will run on dedicated nodes

    Problem- how to catch this event (running both instances on the same node or may be start of 2-nd instance on node #1) programmatically to accommodate sql server max memory value?

    Thanks in advance

  • December 1, 2014 at 4:12 pm

    #1761898

    You'll need to create a start up proc for each instance that checks for the different conditions.

    Since you have to change both instance's memory settings, I'd create a proc in each master db that does that. Then you call those procs from the start up proc when conditions warrant it.

    Btw, you might want to consider capping each instance's RAM at 100GB rather than 110GB to give a little room when the failover initially occurs. Otherwise you might into a memory thrashing situation and performance will absolutely die for both instances.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 1, 2014 at 5:28 pm

    #1761908

    Thanks Scott.

    To resume (if I got you)- I should use Start of new Instance event to catch failover (or other words, both instances running on the same node)? Actually I also thought about this trick 🙂

  • December 2, 2014 at 8:40 am

    #1762114

    SQL allows you to designate a proc(s) as "start up" procs that automatically runs when an instance starts. I was suggesting you create such a proc, or add code to it, to adjust ram dynamically based on what node existing instances are running on when the proc fires as the instance is starting up.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 2, 2014 at 4:05 pm

    #1762244

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply