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 .
1 Answer 1
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 freepaparazzo– paparazzo2016年01月04日 18:41:46 +00:00Commented Jan 4, 2016 at 18:41
-
2@Frisbee so is an extra instance on a machine already licensed with any edition.Aaron Bertrand– Aaron Bertrand2016年01月04日 18:45:33 +00:00Commented Jan 4, 2016 at 18:45
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.