2

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

migrated from stackoverflow.com Aug 13 '17 at 14:11

This question came from our site for professional and enthusiast programmers.

3

Edited for clarity and consistency:

By the database itself? No. However, you can through the connection (what you should be trying to manage anyways) called the Resource Governor.

  • CPU
  • MEMORY
  • Physical IO, depending on the version.

There are a number of Concepts which the Docs explain in greater detail:

Resource Pool

  • a group of default, internal (cannot be changed), and custom made groups that have a set value on the amount of CPU, Memory, and Physical IO that a session may consume.

Workload Group

  • a class of session requests that have similar characteristics. These are mapped to The appropriate resource pool and are set at the time of connection settings. Meaning the default database connect is used not the first USE statement or any statement afterwards.

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

  • Is this possible to configure at Database level as the guy is asking for? "limit specific database" – Daniel Björk Aug 13 '17 at 14:18
  • @Daniel.Björk This IS how you do it. There are no sub governors. The critical point is the connection settings. I.e. SUSER_SNAME() DB_NAME() – clifton_h Aug 13 '17 at 14:34
  • So the answer to his question is: you can't do it. – Daniel Björk Aug 13 '17 at 14:35
  • 1
    @Daniel.Björk yes you can. By dictating the types/classes of connections. I know....however, consider that merely limiting a database is not really managing your server. The connections are doing something so Microsoft thought it best to manage the connections who are the ones running queries that are destroying the server usage. – clifton_h Aug 13 '17 at 14:38
  • Modified the answer to clarify I we are talking about Server level – clifton_h Aug 13 '17 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.