locked
How does SQL Server Resource Governor handle the buffer cache? RRS feed

  • Question

  • Hi,

    If e.g. I have two resource pools P1 and P2, and P1 has the MIN MEMORY limit set to 50%. Will a query running in P2 be able do remove data pages that is cached for P1?

    My situatiuon is that I have operational queries (oltp) running in P1 and analytical queries running in P2. The operational queries in P1 can reuse a lot of data and can make good use of cached data, but queries in P2 almost never reuse data, and therfore almost always have to read huge amounts from disk. I dont want P2 queries to throw out the data cached for P1 queries.

    Will Resource Governor be able to help me here? Any opinions on this?

    Thursday, October 29, 2009 9:09 AM

Answers

  • > Hmm, that is not what i wanted to hear..

    I understand that.

    Separatin into different instances (possibly using same OS and using sp_configure 'max server memory') is the only way I can think of, I'm afraid.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, October 29, 2009 12:56 PM

All replies

  • RG will not help you here, I'm afraid. A page in BP is not "owned" by a particular user or connection (what in the end can boil down to a resource pool). So RG do not govern the BP, but rather working memory for queries, execution plans and such.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by Robert Varga Thursday, October 29, 2009 2:55 PM
    Thursday, October 29, 2009 11:45 AM
  • Hi

    I am Viswanathan, it is my pleasure to share my knowledge according to your query, I would like to start with Resource Governor is useful when you have different workloads using the same SQL Server instance.The goal of Resource governer is to provide predictable execution of workloads. Resource governer used to control memory and the CPU usage by workloads. It only manages the resources consumption of the database Engine. You can not use Resources Governor to manage workload within SSAS, SSIS or SSRS.

    Coming back to your question

    Resource Pool represents the physical resources of the server,

    1.      Resource Pool does not overlap with other resource pools which provide minimum reservations of resources. It indicates that avaiable resources can go to a specified resource pool or other resources pools.one resources pool are not shared when the resources consumed.

    2.      Resource Pool Shared with other resource Pool which provides maximum possible consumption.it improves resource utilization when there is no request in a given resource pool.

    The defualt resource pool can be modified but not dropped.

    --You can determine the if there is any resource pool that do not have any workload groups assigned them by following

     

    --in metedata

    select p.pool_id,p.name,g.group_id,g.name from sys.resource_governor_resource_pools p left outer join sys.resource_governor_workload_groups g

    on p.pool_id=g.pool_id order by p.pool_id

     

    --In memeory

    select p.pool_id,p.name,g.group_id,g.name from sys.dm_resource_governor_resource_pools p left outer join sys.dm_resource_governor_workload_groups g

    on p.pool_id=g.pool_id order by p.pool_id

     

    Thursday, October 29, 2009 12:12 PM
  • RG will not help you here, I'm afraid. A page in BP is not "owned" by a particular user or connection (what in the end can boil down to a resource pool). So RG do not govern the BP, but rather working memory for queries, execution plans and such.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

    Hmm, that is not what i wanted to hear.. I was afraid this what the situation, but also thought that putting restrictions on memory was a bit meaningless if it didn't apply to the buffer cache.

    However, do you have any suggestion on how to handle this problem? Today I am using two instances on two separate servers and replication between them to solve this issue. But this means a lot of HW and maintenance just to solve this issue.
    Thursday, October 29, 2009 12:28 PM
  • Viswanathan,,

    I don't think you addressed NebelHintens question, which was specifically whether RG governs the Buffer Pool (hashed pages). And the answer for that is "no".
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, October 29, 2009 12:55 PM
  • > Hmm, that is not what i wanted to hear..

    I understand that.

    Separatin into different instances (possibly using same OS and using sp_configure 'max server memory') is the only way I can think of, I'm afraid.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, October 29, 2009 12:56 PM

  • Separatin into different instances (possibly using same OS and using sp_configure 'max server memory') is the only way I can think of, I'm afraid.

    Thanks, I think I will go for this. Will use two instances on one server, and replicate the databases. I need double disk capacity, but at least we can do with one server.
    Thursday, October 29, 2009 1:04 PM