4

I have SQL server 2014 with resources 90G of RAM for the whole database engine, engine contain around 15 database instance, I want to make databases X to have at max 5G of RAM while other databases still share resources. please advise how to make it if its applicable .

  • Which edition of SQL Server are you using? Resource Governor is only available in Enterprise. – Jonathan Fite Jan 4 '16 at 16:01
  • Developer Edition (64-bit) – Ahmad Abuhasna Jan 4 '16 at 16:15
  • 1
    No you cannot use RG to Limit Buffer pool memory that can be utilized by particular database. Please read this thread. So you don't have option to limit RAM for particular database you can limit it for certain workload. – Shanky Jan 4 '16 at 16:19
  • 1
    Might not be a perfect solution, but if you don't have cross-database references, and can tie a certain application to the database, you can use Soft Numa to control your buffer node size, then map a port to that node. So for example, you could use soft numa to create one node of 5g, and a second node with the rest, map port 1500 to node 0 and 1433 to node 1, and have the application that uses database x connect to 1500 to limit its memory usage. Make sense? – Nabil Becker Jan 4 '16 at 16:43
  • Would it be acceptable to setup a new instance just for this database, that is the only way to make certain that it would be limited. – Spörri Jan 4 '16 at 16:59
4

Since you are using Developer Edition (and is cheap as compared Standard or Enterprise), you are better off installing another instance with 5GB Max Memory. Put the databases that you need less memory on this instance.

The rest of databases you can put on the instance with more memory.

Read up Tibor's blog post on Restricting memory using Resource Governor which references the link that @shanky referenced.

A misconception I see from time to time is that the memory setting is to restrict the Buffer Pool, BP, (mainly used for caching pages from disk). This seems difficult since a page in BP has a cost, but isn't owned by whoever brought it into cache or who referenced it last time. So, it seems difficult with current implementation of cache handling and ageing to govern the BP memory for RG. What RG does govern is "working memory" for a query. Think memory used for sorting, locks, hashing and such.

Read up on : Performance issues from wasted buffer pool memory from Paul Randal.

  • If willing to live with 1 GB Express is free – paparazzo Jan 4 '16 at 18:41
  • 2
    @Frisbee so is an extra instance on a machine already licensed with any edition. – Aaron Bertrand Jan 4 '16 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.