0

Suppose I have 8 CPUs, and therefore I will have 576 worker threads.

When multiple queries are executing, then each query can be assigned one or more threads. And each thread executes on a cpu.

On a CPU, each thread will get 4ms to execute, and then there will be context switch to allow another thread to execute. That's the concept of running/runnable.

Suppose I use Resource governor pool's concept to set min/max cpu limit, which is only applicable when there is cpu contention, then what exactly does SQL server treat as CPU contention? That is - when exactly does the pool config (min/max cpu) kick in?

Improve this question

1 Answer 1

Reset to default
3

From the documentation of MIN_CPU_PERCENT and MAX_CPU_PERCENT. Formatting and emphasis mine

These settings are the minimum and maximum guaranteed average CPU bandwidth for all requests in the resource pool when there's CPU contention. You can use these settings to establish predictable CPU resource usage for multiple workloads that is based on the needs of each workload.

For example, assume the Sales and Marketing departments in a company share the same database. The Sales department has a CPU-intensive workload with high-priority queries. The Marketing department also has a CPU-intensive workload, but has lower-priority queries. By creating a separate resource pool for each department, you can assign a minimum CPU percentage of 70 for the Sales resource pool and a maximum CPU percentage of 30 for the Marketing resource pool.

This configuration ensures that the Sales workload receives the CPU resources it requires and the Marketing workload is isolated from the CPU demands of the Sales workload. The maximum CPU percentage is an opportunistic maximum. If there's available CPU capacity the worker threads use all of it, up to 100 percent.

The maximum value only applies when there's contention for CPU resources. In this example, if the Sales workload is switched off, the Marketing workload can use 100 percent of the CPU if needed.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.