You need to set up the Resource Group and Workgroup Pools on the instance

DRAG DROP
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second
process executes, the disk IO can reach 7000IOPS, and CPU use can average 30% over
the eight processors.
The first process summarizes the day’s activity executed by a login of
[SummaryReportLogin]. The second process submits transactions executed by a login of
[ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for
connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
Both processes must never use more than 50 percent of the CPU at any one time.
The number of active queries that these processes can execute simultaneously should be
limited to a maximum of 10.
The SummaryReportLogin process must always achieve the minimum IOPS required to be
minimally affected during executing the ETLLogin processes.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

DRAG DROP
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second
process executes, the disk IO can reach 7000IOPS, and CPU use can average 30% over
the eight processors.
The first process summarizes the day’s activity executed by a login of
[SummaryReportLogin]. The second process submits transactions executed by a login of
[ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for
connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
Both processes must never use more than 50 percent of the CPU at any one time.
The number of active queries that these processes can execute simultaneously should be
limited to a maximum of 10.
The SummaryReportLogin process must always achieve the minimum IOPS required to be
minimally affected during executing the ETLLogin processes.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

Answer: See the explanation.

Explanation:

Box 1:

Box 2:

Box 3:

Note:
CREATE WORKLOAD RESOURCE POOL
* Resource pools. A resource pool, represents the physical resources of the server. You can
think of a pool as a virtual SQL Server instance inside of a SQL Server instance.
* Workload groups. A workload group serves as a container for session requests that have
similar classification criteri
a. A workload allows for aggregate monitoring of the sessions, and defines policies for the
sessions. Each workload group is in a resource pool.
* CAP_CPU_PERCENT =value
Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will
receive. Limits the maximum CPU bandwidth level to be the same as the specified value.
value is an integer with a default setting of 100. The allowed range for value is from 1
through 100.
* MIN_IOPS_PER_VOLUME =value
Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the
resource pool.
* GROUP_MAX_REQUESTS =value
Specifies the maximum number of simultaneous requests that are allowed to execute in the
workload group. value must be a 0 or a positive integer.

Show Hint

Leave a Reply 1

Your email address will not be published. Required fields are marked *

malakosa

malakosa

HINT 🙂

1. CPU use can AVERAGE 30% over the eight processors.
2. Both processes must NEVER USE MORE than 50 percent of the CPU at any one time
3. The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10

1. MAX_CPU_PERCENT = value ->30
Specifies the maximum AVERAGE CPU bandwidth that all requests in the resource pool will receive

2. CAP_CPU_PERCENT = value ->50
Specifies the TARGET MAXIMUM CPU capacity for requests in the resource pool.

3. GROUP_MAX_REQUESTS = value ->10
Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group.

Reply