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)
Best Answer
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.