Question

You administer an Azure SQL Managed Instance that hosts both a critical OLTP application and an ad-hoc reporting group that runs unpredictable, memory-heavy queries. During peak periods the ad-hoc queries consume large memory grants, causing the OLTP workload to suffer RESOURCE_SEMAPHORE waits. You want to cap the maximum memory grant percentage available to the ad-hoc queries without affecting the OLTP workload. Which approach should you use?

Accepted Answer

Create a Resource Governor resource pool and workload group for the ad-hoc queries, set REQUEST_MAX_MEMORY_GRANT_PERCENT, and use a classifier function to route the ad-hoc sessions.

Answer

Enable the Memory Grant Feedback feature of Intelligent Query Processing on the ad-hoc database.

Answer

Set MAXDOP to 1 at the instance level to reduce parallel memory consumption.

Answer

Lower the instance-level 'max server memory' configuration to limit total memory usage.