When working with SQL Server, it is not uncommon to encounter errors related to memory. These errors can be quite challenging to troubleshoot, but understanding the underlying concepts can help in resolving them effectively.

Recently, I received a query from a customer DBA who was facing memory-related errors while trying to configure columnstore indexes on a large table. The error messages he shared were as follows:

Error: 8657, Severity: 17, Could not get the memory grant of %I64d KB because it exceeds the maximum configuration limit in workload group ‘%ls’ (%ld) and resource pool ‘%ls’ (%ld). Contact the server administrator to increase the memory usage limit.

Error: 8658, Severity: 17, Cannot start the columnstore index build because it requires at least %I64d KB, while the maximum memory grant is limited to %I64d KB per query in workload group ‘%ls’ (%ld) and resource pool ‘%ls’ (%ld). Retry after modifying columnstore index to contain

Initially, I asked for more information about the resource pool and other values, but the DBA mentioned that he had not configured any Resource Governor on his server. This piqued my interest, as I realized I hadn’t covered this topic on my blog before.

Upon further investigation, I discovered that the default value for a query is limited to 25% of the available memory on a server. To view this setting, you can check the resource governor settings using the following query:

SELECT request_max_memory_grant_percent, * 
FROM sys.dm_resource_governor_workload_groups 
WHERE name = 'default'

As you can see, the default value is set to 25% as part of the standard setup. This explained why the columnstore index creation was failing. If the initial memory grant fails, error 8657 or 8658 is encountered. Subsequently, errors 701 or 802 may occur if memory runs out during execution.

To resolve out-of-memory errors 8657 or 8658 during columnstore index creation, you can modify the resource governor settings to allow the create index statement to access more memory. Here’s an example of how to increase the memory grant to 50%:

-- Increase the value of GRANT to 50% from the default of 25%
ALTER WORKLOAD GROUP [DEFAULT] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 50)
GO

-- RECONFIGURE to make the setting take effect
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

I shared this script with my friend, and he reported back that the query now ran without any problems, and he was no longer encountering errors during index creation.

Have you ever encountered errors 8657 or 8658 in your SQL Server environments? How did you mitigate these errors? I would love to learn from your experiences as well.

Thank you for reading!