Sql-server – SQL Server Resource governor user-defined resource pools

resource-governorsql server

In the context of the SQL Server resource governor, why would I create a user-defined resource pool? It seems like you could get almost the same result by using the default resource pool for everything, and controlling resources instead using the workload group settings.

I've heard that there can be issues if too many resource pools are created, so I'm wondering what the advantages are over the workload group settings?

Best Answer

Think of the resource pool as physically dividing the CPU, memory, and IO of the server. This can be useful when you need to limit resources used by overlapping workloads. The workload groups further limit resources used at a query level within a pool. This can be useful when you need to limit resources used within a workload, perhaps to improve throughput.

The resource governor overview provides a few use cases that can only be satisfied with resource pools as opposed to resource groups. For example:

Throttle IO resources for operations such as DBCC CHECKDB that can saturate the IO subsystem and negatively impact other workloads.

Resource pools have the MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME options to limit IO. Workload groups do not have any options to limit IO.

Workload groups are also limited in how they can control CPU usage. The REQUEST_MAX_CPU_TIME_SEC option doesn't actually enforce a hard limit on CPU by query:

Resource Governor will not prevent a request from continuing if the maximum time is exceeded. However, an event will be generated. For more information, see CPU Threshold Exceeded Event Class.

To properly limit CPU through a workload group you would need to use the MAXDOP option for workload groups but that may not be granular enough. Resource pools offer much better options for preventing runaway queries from using all of the CPU available on a server.

It may seem that workload groups could be used to balance query memory grants for competing workloads with the GROUP_MAX_REQUESTS and REQUEST_MAX_MEMORY_GRANT_PERCENT options. However, consider a workload that sends thousands of queries of various sizes to run against the server with a concurrency of up to 40 queries at once. A small percentage of the queries are memory hogs and if left unchecked would use all available memory for query grants. You find through performance testing that limiting REQUEST_MAX_MEMORY_GRANT_PERCENT to 4% gives reasonable overall throughput. If you only configured this through a workload group you'd end up with:

GROUP_MAX_REQUESTS = 40
REQUEST_MAX_MEMORY_GRANT_PERCENT = 4

That could lead to good performance for that workload but could use all of the memory on the server. If you need another workload to run at the same time then you need to do configuration at the resource pool level. Suppose that you determine that the original workload can get by with 50% of the memory on the server. At the pool level you could set the following:

MAX_MEMORY_PERCENT = 50

And at the workload group level you could set the following:

GROUP_MAX_REQUESTS = 40
REQUEST_MAX_MEMORY_GRANT_PERCENT = 8

That would give you reasonable throughput for the first workload but also allow the second workload to run at the same time. The resource pool is used to divide resources between workloads and the workload group is used to divide resources between the queries that belong to the pool.

Related Question