Question

You administer a SQL Server 2022 instance hosting both an OLTP application and an ad-hoc reporting group. Analysts frequently run heavy queries that consume most of the CPU, causing the OLTP transactions to slow down during business hours. You need to guarantee that the reporting queries can never consume more than 25% of CPU when the server is under contention, while still allowing them to use idle CPU when the OLTP workload is quiet. Which feature and configuration should you implement?

Accepted Answer

Configure Resource Governor with a resource pool that sets MAX_CPU_PERCENT to 25 for the reporting workload group

Answer

Configure Resource Governor with a resource pool that sets MIN_CPU_PERCENT to 25 for the reporting workload group

Answer

Enable the Query Governor Cost Limit and set it to 25 for reporting sessions

Answer

Set MAXDOP to 25 at the database scope for the reporting workload