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

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.


Not sure if your licensing would allow this, but you would get some degree of fine-tuned control by install a second SQL instance on the same host.

With that you can set the max memory of each instance individually, and you even have some ability to adjust CPU settings (processor affinity) for each (although you should really understand this setting before messing with it).

The problem is that even in this scenario, if management asked me: "can you guarantee this won't impact the production database on the primary instance", I'd have to answer "no, I can't guarantee that." For that you'd want to run on an entirely separate VM.

Tags:

Sql Server