Resource Governor – Memory Allocations

Microsoft in the News

Fabian Bolin is looking to bring the power of Microsoft Azure to address a bottleneck in the area of cancer research. It turns out that almost half of clinical trials dealing with the study and treatment of tumors (oncology) are delayed due to a lack of qualified patients.

Fabian is looking to launch an app this year that will share and track data generated in the area of oncology research. It is hoped that by bringing this data together under one roof, researchers will be better equipped to spot what works and what doesn’t.

Because cancer patients tend to be a willing lot when it comes to sharing their story and volunteering for clinical trials, Fabian intends for his app to become a matchmaking service that will remove the previously mentioned bottleneck in cancer research.

Resource Pool – Memory Allocations

In my last blog I finished looking at allocating CPU resources through SQL Server Resource Governor. In this installment I will look at allocating memory.


Resource Governor allows you to allocate minimum and maximum amounts of query execution grant memory. Other forms of memory such as buffer pool memory are not affected by these allocations.

Unlike with CPU allocation, setting a minimum value for memory will form hard limits that may affect other pools. That minimum allocated memory cannot be borrowed. Even if there is additional unused memory available, Resource Governor will not allow a Group to opportunistically take advantage of unused memory from a different pool. If a defined group is idle and the resource pool associated with that group is unused, the minimum memory allocated to that pool will remain idle even if a different pool is maxing out its memory and could use more.

Likewise, if you set a maximum value for memory within a pool, that pool will never exceed that limit, even if additional memory is available.

Because memory allocation within Resource Governor is inflexible, it is important that you carefully plan and test your allocations.


Memory Grant % is a sub-grant of memory within a defined pool. If, for example, you defined a pool to have a MAX_MEMORY_PERCENT of 20%, using the Memory Grant % will further restrict the use of that 20% allocation within the pool. With this function, you can place a maximum percentage of this pool’s allocated memory that can be utilized by any single request. There may be occasions when there are a lot of concurrent queries. Some of those queries can be resource intense and if left alone could usurp all memory available for queries. By defining a Memory Grant % you prevent any one query from monopolizing the entire pool.

In my next blog I will look at allocating physical IO operations.

Leave a Reply

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