SQL Server 2016: How to restrict memory and CPU for specific database

SQL Server 2016 Standard Ed on Windows 2016

We have an 8 core, 128GB machine (a VM, actually). We max this out for our prod db. (122GB for the instance, 8 cores for the instance)

We are setting up a 2nd db, to be on its own spindle set (slower disks), and do not want it to impact the prod db. This will be on the same VM (as we understand it, we can use the existing SQL license this way).

Must this 2nd db be served by its own SQL instance in order to:

• Restrict RAM consumed (to very little, just 4 or 8 GB)

• Restrict to 2 CPU cores

Or can we restrict this DB within the main instance?

(Note, Express Edition is not an option, our db will be over 10 GB)

You can't. There's no such thing as per-database restrictions because a query can cross multiple databases (think joins and fully qualified objects.)

SQL Server's Resource Governor in theory exists to support this, but it only works on things like CPU count, query workspace memory, and disk IO. It doesn't work on the buffer pool, so it still won't meet your needs.

StackExchange Database Administrators Q#187568, answer score: 11