Resource Pool
You distribute the amount of memory, CPU, and IO available to SQL Server among resource pools as a means of reducing contention between workloads.
In simple words, the CPU usage is the things which a computer is doing which doesn't requires any type of Input or output…. Intuitively, your CPU usage is the percentage of cycles used in the last second to the maximum cycles allowed (for example, a 2.6 GHz CPU allows a maximum of 2,600,000,000 clock cycles each second)
For the I/O usage, the computer is connected with number of different Input and output devices like for example you monitor (Output) your keyboard (input) so to handle all these input and output devices computer need to reserve some memory that is I/O usage or Memory… Therefore, IO usage is the hard drive. These are very slow, and usually more programs want to use it then it can handle, which is why Windows normally shows it as 100%. Suppose your drive supports 110 MB/so and 50 IOPS. Your programs are accessing a 400 MB file which is in 121 fragments, a 1 kB file which is not fragmented, and a 80 MB file which is in 54 fragments. It totals to 176 disk lookups for the files, + [assumed] 5 for the file tables telling where the files are. 181 / 50 per second = 4 seconds. It would take 5 seconds for the 480 MBs to be read at 110 MB/s, so the total disk lookup would be between 5-9 seconds, hence 100% usage.
Memory bound means the rate at which a process progresses is limited by the amount memory available and the speed of that memory access. A task that processes large amounts of in memory data, for example multiplying large matrices, is likely to be Memory Bound. RAM usage is the amount of RAM filled to the maximum RAM allowed. RAM is basically fast memory. I/O Bound would be slower than Memory Bound would be slower than Cache Bound would be slower than CPU Bound.
A program is CPU bound if it would go faster if the CPU were faster, i.e. it spends the majority of its time simply using the CPU (doing calculations). A program that computes new digits of π will typically be CPU-bound, it's just crunching numbers.
A program is I/O bound if it would go faster if the I/O subsystem was faster. Which exact I/O system is meant can vary; I typically associate it with disk, but of course networking or communication in general is common too. A program that looks through a huge file for some data might become I/O bound, since the bottleneck is then the reading of the data from disk (actually, this example is perhaps kind of old-fashioned these days with hundreds of MB/s coming in from SSDs).
The solution to being I/O bound isn't necessarily to get more Memory. Cache bound means the rate at which a process progress is limited by the amount and speed of the cache available. A task that simply processes more data than fits in the cache will be cache bound. In some situations, the access algorithm could be designed around the I/O, Memory or Cache limitations.
In SQL server, Each resource pool is configured with the following settings (except the external resource pool as described later in this section): Minimum CPU%, Maximum CPU%, Minimum Memory %, and Maximum Memory %.
The sum of Minimum CPU% and of Minimum Memory % for all resources pools cannot be more than 100. These values represent the guaranteed average amount of that resource that each resource pool can use to respond to requests. The Maximum CPU% and Maximum Memory % reflect the maximum average amount for the respective resources.
SQL Server can use more than the maximum percentage defined for are source if it is available. To prevent this behavior, you can configure a hard cap on the resource available to the resource pool.
You can use the Resource Governor node in Object Explorer to open a dialog box and add or configure resource pools as needed, although this interface does not include all settings available to configure by using T-SQL. When you create a resource pool by using T-SQL, you specify any or all arguments for CPU, the scheduler, memory, and I/O operations per second (IOPS).
CREATE RESOURCE POOL poolDaytime WITH ( MIN_CPU_PERCENT = 50, MAX_CPU_PERCENT = 80, CAP_CPU_PERCENT = 90, AFFINITY SCHEDULER = (0 TO 3), MIN_MEMORY_PERCENT = 50, MAX_MEMORY_PERCENT = 100, MIN_IOPS_PER_VOLUME = 20, MAX_IOPS_PER_VOLUME = 100 ); GO
CREATE RESOURCE POOL poolNighttime WITH ( MIN_CPU_PERCENT = 0, MAX_CPU_PERCENT = 50, CAP_CPU_PERCENT = 50, AFFINITY SCHEDULER = (0 TO 3), MIN_MEMORY_PERCENT = 5, MAX_MEMORY_PERCENT = 15, MIN_IOPS_PER_VOLUME = 45, MAX_IOPS_PER_VOLUME = 100 ); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO
The above resource pool configure CPU, Memory, and I/O for poolDaytime and poolNighttime, respectively.
It is worth noting that AFFINITY would limit the number of CPUs a resources pool could use, but it would not set a hard cap limit on the CPU bandwidth recources. However, CAP_CPU_PERCENT does. By conparation, MAX_CPU_PERCENT would allow requests above the maximum CPU resource request if available.
Example:
Answer:
