patternsqlMinor
SQL Server 2014 Buffer Pool Extension - There is insufficient memory available in the buffer pool
Viewed 0 times
theavailablesqlinsufficientextensionmemoryserverpoolthere2014
Problem
I am running Microsoft SQL Server 2014 Enterprise Edition SP1 (build 12.0.4213.0 (X64)), with 288GB of RAM, and max memory set to 280GB (I arrived at that number based on many recommended suggestions from SQL Server MVPs).
Twice in the last month I have received:
There is insufficient memory available in the buffer pool
...followed by other errors, most notably:
The log for database 'db' is not available
The database goes into suspect mode. If I restart the SQL service, it comes back fine. I have an SSD that is 1,863GB in size and buffer pool extension on it with a size of 1,859GB in size (I left almost a 5 GB cushion on the SSD).
The error seems to indicate RAM memory pressure. At the time it errors, the server is under high pressure. But this is not uncommon, however, the errors appearing are new. Should I be targeting my RAM modules for testing? Or is somehow the Buffer Pool Extension causing the error?
We monitor this box with SolarWinds DPA, plus I use DMVs to get realtime info, so I have a real good idea of what was running at the time. Most waits were memory related.
The buffer pool extension data was around 1TB when it went down, so there was plenty of room there. This is a production server, with a very small maintenance window one day a week. Db is in full recovery model, with 1 full backup a day, and trans logs backed up every 30 minutes.
No hardware or disk related errors in Event Viewer. The only error in the SQL Server error log before it dumps is:
04/04/2016 07:42:32,spid77,Unknown,Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 24
`sys.dm_os_loaded_modul
Twice in the last month I have received:
There is insufficient memory available in the buffer pool
...followed by other errors, most notably:
The log for database 'db' is not available
The database goes into suspect mode. If I restart the SQL service, it comes back fine. I have an SSD that is 1,863GB in size and buffer pool extension on it with a size of 1,859GB in size (I left almost a 5 GB cushion on the SSD).
The error seems to indicate RAM memory pressure. At the time it errors, the server is under high pressure. But this is not uncommon, however, the errors appearing are new. Should I be targeting my RAM modules for testing? Or is somehow the Buffer Pool Extension causing the error?
We monitor this box with SolarWinds DPA, plus I use DMVs to get realtime info, so I have a real good idea of what was running at the time. Most waits were memory related.
The buffer pool extension data was around 1TB when it went down, so there was plenty of room there. This is a production server, with a very small maintenance window one day a week. Db is in full recovery model, with 1 full backup a day, and trans logs backed up every 30 minutes.
- This is a dedicated SQL Server box.
- The first error that shows is the insufficient memory error.
- The database that errored is 3.22 TB on an iSCSI mapped drive, set to auto grow by 100GB. There is 800GB free currently. Log file is 646GB in size, set to autogrow by 30GB. There is 154GB free. All dbs together is 4TB. All mdf's and ldf's are on their own iSCSI mapped drive.
No hardware or disk related errors in Event Viewer. The only error in the SQL Server error log before it dumps is:
04/04/2016 07:42:32,spid77,Unknown,Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 24
`sys.dm_os_loaded_modul
Solution
The best answer I can come up with is that Sql really doesn't have any room in the buffer pool to give up. Meaning, we have too many queries running, or the queries running have plans that are taking up too much space. This link was somewhat helpful No Free Buffers. My solution at this point is to evaluate the queries that were running when the error occurs, so see if I can tune them.
Context
StackExchange Database Administrators Q#134293, answer score: 2
Revisions (0)
No revisions yet.