I'm wondering if there is away to limit specific database from using server's resources like setting max CPU and RAM for each database [simpler to IIS App pools]?
I'm working with SQL Server 2014 Enterprise & I want to prevent some databases from slowing down the whole server.
Thanks
Best Answer
By the database itself? No. However, you can through the connection (what you should be trying to manage anyways) called the Resource Governor.
There are a number of Concepts which the Docs explain in greater detail:
Resource Pool
Workload Group
The governor must be activated Or configured. A governor specific function is run against the connection's default settings and map to a workload group.
. This workload is attached to a specific resource pool where the limitations are enforced on all subsequent sessions that occur out of the connection.
This function is the most important part and you should keep it simple.
For more reading, visit the Docs:
Main Page -Resource Governor
Resource governor Resource pool
Research governor Workload group
Resource governor classifier function | Docs Microsoft