Eric Cobb diagnoses an ugly issue:
I recently ran into a situation where a new SQL Server would crash hard every time it would get under a load.
Here is a synopsis of what we were seeing:
This is a physical server and has 512GB of RAM installed. We have SQL Server 2016 installed, and fully patched (SP2 CU15 at this time). When load testing the server, it would start throwing errors such as:
“Failed allocate pages: FAIL_PAGE_ALLOCATION”
and
“There is insufficient system memory in resource pool ‘default’ to run this query.”
and
“Failed to allocate BUFs”
It would then write a memory dump to the log, and in most cases the server would become completely unresponsive and would have to be rebooted.
Read on to learn under what conditions this happens as well as the solution to the problem.