SQL SERVER – How to fix the SQL Server Error 8657 or 8658?

It is sometimes very difficult to troubleshoot error which are relating to memory. In a recent query from one of my customer DBA, he sent me an error message which read like the ones below:

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

The first instinct for me was to ask, hey you are sending me the template. Can you give me more information on the Resource Pool and other values? He said, he had not configured any Resource Governor on his server and was just trying to configure few columnstore indexes on a very large table when some of these errors were coming. Since he was part of a banking domain, he was unable to send me the exact values as per organizations security reasons. With very less information, I thought I need to dig more into these errors to understand why these happen. I was surprised, I had not written about this yet on this blog.

On quizzing few friends, I learnt that the default value for a query is limited to 25% of available memory on a server. This got me interested to find out how can this be viewed. The tip was part of the error message – check the resource governor settings.

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

As you can see, the default value is 25% preconfigured and part of standard setup. Now to answer why the columnstore index creation was erroring.

It is possible for creation of a columnstore index to fail either at the very beginning of execution if it can’t get the necessary initial memory grant, or later during execution if supplemental grants can’t be obtained. If the initial grant fails, we will see error 8657 or 8658. We may get error 701 or 802 if memory runs out later during execution.

Resolution: If out-of-memory error 8657 or 8658 occur at the beginning of columnstore index creation, first, check the resource governor settings. The default setting for resource governor limits a query in the default pool to 25% of available memory even if the server is otherwise inactive. This is true even if we have not enabled resource governor. Consider changing the resource governor settings to allow the create index statement to access more memory in such cases. The TSQL would look like:

-- Increase the value of GRANT to 50% from 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 sent this script to my friend and told him to see the effect. I got a response last week that the query now ran without any problem and he was no longer getting the errors during index creation. Post this email conversation, I thought of uploading this script back into this blog for future reference.

I am curious to know, have you ever encountered 8657 or 8658 errors in your environments? What did you do to mitigate the errors? Would love to learn from you too.

Reference: Pinal Dave (https://blog.sqlauthority.com)