none
Allocate memory resources by database, or pin database/tables?

    Pregunta

  • We have a server which has about 10 databases on it. All are fairly large. There is one in particular, which is several hundred GB, and another which is about 30GB. The very large one is used mostly at night, where the smaller one is used during the day.

    The problem is that at night, we run some jobs which use the very large DB extensively, and of course SQL server pulls as much of that database into memory as possible, pushing out the 10GB which is used during the day.

    We have 128GB of memory, and what we would like to do is either limit the large database so that it only will use up to 60GB of memory, and/or pin the 30GB database and leave the rest of the memory for all the rest to swap in and out.

    It appears that up until 2005 you used to be able to pin databases, so that once they were loaded, they would not be removed, but now in 2005 this is not possible.

    Are there other ways of managing memory resources per database?

    viernes, 15 de abril de 2011 15:10

Todas las respuestas

  • Perhaps you could create a separate SQL Server Instance for the two databases and allocate each it's own set of dedicated RAM (using the MAX memory setting). Of course not an efficient use of memory overall.

    Alternatively in SQL Server 2008 you could use Resource Governor to achieve memory allocation based on workload.


    John Sansom | SQL Server DBA Blog | Twitter
    • Propuesto como respuesta WeiLin QiaoModerator lunes, 18 de abril de 2011 8:05
    • Votado como útil bpeikes lunes, 18 de abril de 2011 15:11
    viernes, 15 de abril de 2011 15:33
  • You have no control over how SQL Server uses it buffer cache, other than size.  You cannot change the current functionality. 

    However, in addition to Resource Governor, you could create a job to do a SELECT * FROM 30GB database table to get it into cache at 6am or something.

     

    viernes, 15 de abril de 2011 21:26
  • We have a server which has about 10 databases on it. All are fairly large. There is one in particular, which is several hundred GB, and another which is about 30GB. The very large one is used mostly at night, where the smaller one is used during the day.

    The problem is that at night, we run some jobs which use the very large DB extensively, and of course SQL server pulls as much of that database into memory as possible, pushing out the 10GB which is used during the day.

    We have 128GB of memory, and what we would like to do is either limit the large database so that it only will use up to 60GB of memory, and/or pin the 30GB database and leave the rest of the memory for all the rest to swap in and out.

    It appears that up until 2005 you used to be able to pin databases, so that once they were loaded, they would not be removed, but now in 2005 this is not possible.

    Are there other ways of managing memory resources per database?


    In SQL Server 2008 there is Resource governor that will be a 100% fit in this scenario . If you need me to send you the triage PPT that I took in the past , I can do that ...you can use Resource governor to limit memory and CPU by creating workloads ..
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    sábado, 16 de abril de 2011 7:18
  • This doesn't help because we need to do queries which perform joins between the two databases.
    lunes, 18 de abril de 2011 15:11
  • This looks interesting, too bad we're on SQL server 2005. Anyone know why Microsoft removed the ability to pin a table from 2005?
    lunes, 18 de abril de 2011 15:12
  • Resource governor would not be useful for your scenario since it doesn't govern the buffer pool. http://www.sqlskills.com/blogs/paul/category/Resource-Governor.aspx

    I would have used several instances as my first choice. But since you are doing cross db queries, you would use cross-instance queries, which I would want to avoid if possible. That leads us to the suggestion to through a job execute some queries which reads data from the smaller database(s) and get the data back in cache again.


    Tibor Karaszi, SQL Server MVP | web | blog
    lunes, 18 de abril de 2011 17:09
  • Resource governor would not be useful for your scenario since it doesn't govern the buffer pool. http://www.sqlskills.com/blogs/paul/category/Resource-Governor.aspx

    I would have used several instances as my first choice. But since you are doing cross db queries, you would use cross-instance queries, which I would want to avoid if possible. That leads us to the suggestion to through a job execute some queries which reads data from the smaller database(s) and get the data back in cache again.


    Tibor Karaszi, SQL Server MVP | web | blog

    I think its the old entry by Paul ....if you look at the BOL it says "applies to CPU bandwidth and memory managed by SQL Server" . in this article http://msdn.microsoft.com/en-us/library/bb895232(v=SQL.100).aspx

    In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.

    Just test it on your sand box and you will see that it actually restricts the memory for a particular connection in the workload .. 


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

     


    • Editado Abhay_78 lunes, 18 de abril de 2011 17:30 edit
    lunes, 18 de abril de 2011 17:27
  • Resource governor would not be useful for your scenario since it doesn't govern the buffer pool. http://www.sqlskills.com/blogs/paul/category/Resource-Governor.aspx

    I would have used several instances as my first choice. But since you are doing cross db queries, you would use cross-instance queries, which I would want to avoid if possible. That leads us to the suggestion to through a job execute some queries which reads data from the smaller database(s) and get the data back in cache again.


    Tibor Karaszi, SQL Server MVP | web | blog

    I think its the old entry by Paul ....if you look at the BOL it says "applies to CPU bandwidth and memory managed by SQL Server" . in this article http://msdn.microsoft.com/en-us/library/bb895232(v=SQL.100).aspx

    In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.

    Just test it on your sand box and you will see that it actually restricts the memory for a particular connection in the workload .. 


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

     



    I don't see a conflict in how Paul states things and BOL. However, I do trust Paul's comment on RG not governing the BP, and based on that I insist that RG would not be helpful the the scenario of this thread. Whether Paul is incorrect or not in his claim about RG not governing the BP is best checked by doing a test, I agree. That is outside the scope of time I have now. I welcome results from others doint such tests, though.

    Remember that a run-away query can easily be using bunch of "worker memory" which isn't the BP. Such memory is what is governed by RG. Think memory needed for hash joins, as one example.

    From a reasoning standpoint, I find it difficult for RG to govern BP. A page in BP isn't "owned" bu the connection that brought it into cache. To correctly govern pages in cache, I htink MS would have to drastically change the cache aging algorithms and data structures. Possibly in a way to instead of just tick up and down, you would have several reference counters, one for each RG pool. Probably doable, but AFAIK, not yet done.


    Tibor Karaszi, SQL Server MVP | web | blog
    lunes, 18 de abril de 2011 17:43
  • Resource governor would not be useful for your scenario since it doesn't govern the buffer pool. http://www.sqlskills.com/blogs/paul/category/Resource-Governor.aspx

    I would have used several instances as my first choice. But since you are doing cross db queries, you would use cross-instance queries, which I would want to avoid if possible. That leads us to the suggestion to through a job execute some queries which reads data from the smaller database(s) and get the data back in cache again.


    Tibor Karaszi, SQL Server MVP | web | blog

    I think its the old entry by Paul ....if you look at the BOL it says "applies to CPU bandwidth and memory managed by SQL Server" . in this article http://msdn.microsoft.com/en-us/library/bb895232(v=SQL.100).aspx

    In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.

    Just test it on your sand box and you will see that it actually restricts the memory for a particular connection in the workload .. 


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

     



    RG limits memory usage which is different in SQLOS than Buffer Pool usage.  Tibor is correct that you can't limit the amount of Buffer Pool a resource group can use because pages in memory are not owned by anything.  Lock Memory, sort memory, and optimization workspace memory can be limited because they are connection/session and therefore resource pool specific.

    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    lunes, 18 de abril de 2011 19:51
  • Hi Jon and Tibor ,there is nothing personal and its all in good taste .Basically I was not able to believe (and still cannot)that resource governor will not restrict the memory allocation in the form of buffers to the connection which is coming from a pool of restricted memory .we have implemented the same successfully in our environment around a year back and I took the triage on the same for my cluster as a part of knowledge sharing.Now after this discsussion I feel that might have to understand RG a bit more (becasue earlier it worked as per the expectations and is still working ).I believe you are the one of the knowledgable persons to take advice from .Yesterday I did some tests ..You can try the same and the owner of this thread as well :

    <Note that I kept the max server memory to 300 MB before this test>

    CREATE LOGIN MEM_TEST WITH PASSWORD = 'MEM_TEST', CHECK_POLICY = OFF
    go
    CREATE RESOURCE POOL MyPool with (MAX_MEMORY_PERCENT=10, MAX_CPU_PERCENT=50)
    go
    CREATE WORKLOAD GROUP MyPoolGroup with
    (IMPORTANCE = HIGH, MAX_DOP=4,REQUEST_MAX_MEMORY_GRANT_PERCENT=9)USING MyPool
    go
    CREATE FUNCTION dbo.Demo_clsf_fun ()
    RETURNS SYSNAME WITH SCHEMABINDING
    BEGIN
     DECLARE @val varchar(32)
     SET @val = 'default'
     if  SUSER_SNAME() = 'MEM_TEST'
      SET @val = 'MypoolGroup'
     return @val;
    END
    GO

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Demo_clsf_fun)
    GO
    SELECT * FROM sys.resource_governor_resource_pools
    SELECT * FROM sys.dm_resource_governor_resource_pools
    SELECT * FROM sys.resource_governor_workload_groups
    SELECT * FROM sys.dm_resource_governor_workload_groups
    go
    ALTER RESOURCE GOVERNOR RECONFIGURE
    go
    --SELECT * FROM sys.resource_governor_resource_pools
    SELECT cache_memory_kb,compile_memory_kb,max_memory_kb,used_memory_kb,out_of_memory_count,* FROM sys.dm_resource_governor_resource_pools where pool_id >2
    --SELECT * FROM sys.resource_governor_workload_groups
    SELECT queued_request_count,blocked_task_count,total_query_optimization_count,max_request_cpu_time_ms,request_memory_grant_timeout_sec, * FROM sys.dm_resource_governor_workload_groups where pool_id >2
    go
    select * from sys.dm_exec_cached_plans where pool_id >2
    go
    select * from sys.dm_os_memory_brokers where pool_id >2
    go
    select * from sys.dm_exec_query_memory_grants
    go

    --Start test after connecting through the login thats is created above
    dbcc dropcleanbuffers
    go
    dbcc freeproccache
    go
    --<create a table with 20000000 rows >

    What I did was Insert into db2migration..FOO1 select * from db2migration..FOO
    and ran the below 2 queries :

    SELECT cache_memory_kb,compile_memory_kb,max_memory_kb,used_memory_kb,out_of_memory_count,* FROM sys.dm_resource_governor_resource_pools where pool_id >2
    --SELECT * FROM sys.resource_governor_workload_groups
    SELECT queued_request_count,blocked_task_count,total_query_optimization_count,max_request_cpu_time_ms,request_memory_grant_timeout_sec, * FROM sys.dm_resource_governor_workload_groups where pool_id >2

    Results : for few seconds the query was running fine ..and then I got the 701 error

    Msg 701, Level 17, State 130, Line 1
    There is insufficient system memory in resource pool 'MyPool' to run this query.
     

    Notice the out put of the DMVs ...does that not mean that the memory is coming from bufferpool and is restricted as per RG specifications ....how does it all work then if BPool is not playing a role here ...

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    martes, 19 de abril de 2011 4:44
  • Give me a couple of days to build a repro that will show what Tibor and I am saying, and then to run that repro by a couple of people that know Resource Governor Internals better than I do.  I will also ask some of the Microsoft forums members to reply to this thread and explain what is actually happening here in the mean time.  This is a good debate, and I'd like to see a definitive repro come out of it that demonstrates the functionality of Resource Governor with regards to the buffer pool one way or another, even it if means that Tibor and I are wrong, which I don't believe is the case.


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    martes, 19 de abril de 2011 5:19
  • Thanks Jon but I think I am getting it now (will still wait for your explanation).

     

    this is what I think : Pages in the Buffer Pool are brought to read or to be dirtied .This means that the pages related to table rows data will be in the Buffer pool and will not be flushed off to the disk until buffer pool\cache is reaching its limit and have to flush the old pages.

    Now, I think RG will restrict the physical memory consumption of the query during the run time and hence either the query will succeed in getting run time memory with in the range set by RG or fail and throw 701 due to RG restriction.This will not restrict the Bufferpool to remove the pages of the query from the Buffer pool\cache in case there are other new pages to be accomodated .
    And hence this is where the RG can-not do anything ...So I think Tibor and Jon are correct ....

    Moving forward , I think having a separate instance would help but only if the account under which SQL Server is running is a part of LIPM and Max server memory is set ...

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Editado Abhay_78 martes, 19 de abril de 2011 8:46 grammar
    martes, 19 de abril de 2011 8:41
  • RG throttling governs only stolen memory requested by that particular request. The buffers in BPool associated with data pages do not belong to any particular work-request and are not part of stolen memory. Which is why RG throttling will not help control buffer pool data pages. Additionally, RG only helps in assigning pools based on CPU and memory resources that are managed by the SQL instance in question and not the physical server on which SQL is installed.

    Substantiating the above from the whitepaper on RG by Aaron and Boris (http://msdn.microsoft.com/en-us/library/ee151608.aspx):

    In addition, the Resource Governor does not manage memory used by the buffer
    pool for data pages; these mechanisms remain the same as in SQL Server 2005.

    I would be interested in a Repro for this scenario [just for kicks!! :)] just to demonstrate this above mentioned behavior with numbers but the above mentioned is quite clear.

    HTH


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://www.troubleshootingsql.com
    Twitter: @banerjeeamit
    MSDN Blog: http://blogs.msdn.com/sqlserverfaq
    martes, 19 de abril de 2011 10:16
  • Abhay,

    (Replying here, even though there has been other posts since...)

    Discussions are fine :-). I decided to do a repro, after all, and make it into a blog post. http://sqlblog.com/blogs/tibor_karaszi/archive/2011/04/19/restricting-memory-using-resource-governor.aspx

    The script could be more polished, to have some more random caracteristics over the read load (several client connections, more simulaneious connections, more random distribution of what data is accesses etc). But even if executing the loop several times, we see how the amount of memory in the BP is about the same for both databases, and not how the Resource Pools has requested.

    I should add that this doesn't mean that RG isn't useful for allocating memory. I'm sure it can be helpful. What we do want to watch out for is this very clear statement (db a using tops x% and DB b using tops y %).


    Tibor Karaszi, SQL Server MVP | web | blog
    martes, 19 de abril de 2011 11:55
  • Anyone know why Microsoft removed the ability to pin a table from 2005?


    The feature was removed prior to SQL 2000 because it did more harm than good.  When BP memory was dedicated for certain objects, that memory couldn't be used for more pressing needs and often resulted in reduced overall performance.  SQL Server will keep data in memory forever until it the service is restarted or the memory is needed by other activity.

    In your case, I would think the smaller database would get loaded into RAM lazily on demand as it is used after nightly batch and remain in memory throughout the day.  If you have a large spike in activity against the smaller database at the start of the day and want to avoid the associated IO, you could load the smaller databases into cache by scheduling a CHECKDB after your nightly batch.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    martes, 19 de abril de 2011 12:38