locked
limit CPU and RAM for Database RRS feed

  • Question

  • Dear Sir,

    I have 2 SQL 2008 R2 instance; Default SQL instance Hold 3 Databases, Second instnace: hold 2 databases.

    for performance issue, I need to Limit CPU and Memory Use per database like following:

    Instance 1 DB01 CPU Use: 15%, RAM Use: 1 GB

    Instance 1 DB02 CPU Use: 15%, RAM Use: 1 GB

    Instance 1 DB03 CPU Use: 25%, RAM Use: 2 GB

    Instance 2 DB01 CPU Use: 30%, RAM Use: 2 GB

    Instance 2 DB02 CPU Use: 30%, RAM Use: 2 GB

     

    Please help me to know how can I do this configurations.

    Thanks

    • Moved by Naomi N Tuesday, January 31, 2012 10:16 PM Better answer can be here (From:Transact-SQL)
    Tuesday, January 31, 2012 10:04 PM

Answers

  • Hi Jean,

    Strictly speaking that's impossible. As Kevin and Ajay mentioned, with Enterprise edition you can use resource governor and throttle CPU usage and Memory grants for queries. That's not done by databases but technically you can write classifier function in the way that takes it into consideration Although resource governor does not throttle buffer pool memory - just the query memory.

    Another problem with Resource Governor is that it would not do you any good with 2 instances of SQL Server. Of course, you can set affinity masks and separate them but at the end you could easily end up with performance worse than it used to be.

    If you don't have Enterprise edition you can try to implement "poor man resource governor" with Soft-Numa and TCP affinity. This will allow you to separate connections to different TCP ports and "link" those ports to specific CPU(s). But that's also far from ideal..

    If you need to keep multiple instances, only thing that can move you somewhere close is virtualization. You can technically move your instances to separate VMs and throttle CPU and memory based on VMs. Next, you can use Resource Governor within the VM/instance to throttle CPU.

     


    Thank you!

    My blog: http://aboutsqlserver.com


    Wednesday, February 1, 2012 12:14 AM

All replies

  • Plesae refer below link for the same.

    http://msdn.microsoft.com/en-us/library/bb933866.aspx

    Thanks

    Ajay Rengunthwar

     

    Tuesday, January 31, 2012 10:29 PM
  • As far as I'm aware, this exact request is not possible.


    If you have SQL Server 2008 R2 Enterprise Edition you could look at configuring Resource Governor in each instance to allocate set CPU and Memory thresholds. http://msdn.microsoft.com/en-us/library/bb895232.aspx


    Resource Governor is instance level only so you couldn't set resource consumption at the server level.


    You would also have to limit memory at the instance level.


    You could not stop an instance from using more CPU if CPU resource was available.


    If you don't have Enterprise Edition then you will likely be left with allocating a maximum memory of 4GB to Instance 1 and 4GB to Instance 2.

     

    Tuesday, January 31, 2012 10:35 PM
  • Hi Jean,

    Strictly speaking that's impossible. As Kevin and Ajay mentioned, with Enterprise edition you can use resource governor and throttle CPU usage and Memory grants for queries. That's not done by databases but technically you can write classifier function in the way that takes it into consideration Although resource governor does not throttle buffer pool memory - just the query memory.

    Another problem with Resource Governor is that it would not do you any good with 2 instances of SQL Server. Of course, you can set affinity masks and separate them but at the end you could easily end up with performance worse than it used to be.

    If you don't have Enterprise edition you can try to implement "poor man resource governor" with Soft-Numa and TCP affinity. This will allow you to separate connections to different TCP ports and "link" those ports to specific CPU(s). But that's also far from ideal..

    If you need to keep multiple instances, only thing that can move you somewhere close is virtualization. You can technically move your instances to separate VMs and throttle CPU and memory based on VMs. Next, you can use Resource Governor within the VM/instance to throttle CPU.

     


    Thank you!

    My blog: http://aboutsqlserver.com


    Wednesday, February 1, 2012 12:14 AM
  • Hi Jean:

    SQL Server supports NUMA...

    I think it will helps you.. please refer the below links

    http://msdn.microsoft.com/en-us/library/ms345345.aspx

    http://msdn.microsoft.com/en-us/library/ms345357.aspx

    Experts: what you think about this....

    Thanks, 


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)
    Wednesday, February 1, 2012 12:29 AM
  • This is technically possible, but I don't recommend it, you can't do this INSIDE of SQL Server, but you can do it at the WINDOWS SERVER level. Test your server and instances before you use this as it may have unwanted side effects.

     

    Windows Server Resource Manager

    http://technet.microsoft.com/en-us/library/cc755056.aspx

     

    -Sean

    Wednesday, February 1, 2012 2:29 AM
    Answerer
  • Can you please provide the detail of OS and RAM?

    It is not possible in percentage but if you have more than 1 CPU you can limit the particular instance to use the limited number of CPUs.

    RAM configuration can be set by MAX MEMORY option.


    Regards Madan Agrawal Please vote if you find my post valuable for you.
    • Proposed as answer by Madan.Agrawal Wednesday, February 1, 2012 9:38 AM
    Wednesday, February 1, 2012 9:38 AM
  • Hi,

     

    You can use the below query on the individual Instances what ever you want to set their max memory to be.

    Use <Database name>

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'max server memory', 4096;
    GO
    RECONFIGURE;
    GO

     

    Replace 4096 with your memory required.

    Regards JakDBA
    • Proposed as answer by ZtBoy Wednesday, September 14, 2016 10:14 PM
    Wednesday, February 1, 2012 3:58 PM
  • Can you please provide the detail of OS and RAM?

    It is not possible in percentage but if you have more than 1 CPU you can limit the particular instance to use the limited number of CPUs.

    RAM configuration can be set by MAX MEMORY option.


    Regards Madan Agrawal Please vote if you find my post valuable for you.

    By setting MAX MEMORY option, you are limiting only the size of buffer pages.  Just and FYI...


    SQL Server DBA

    Friday, August 31, 2012 4:39 PM