February 6, 2012 at 10:49 am
Over the weekend, we got quite a few SQL server alert severity- 017 Insufficient resources.
In window event log we get:
There is insufficient system memory in resource pool 'internal' to run this query.
Currently we have 8 GB RAM on this server, from task manager I can see it uses 7.78 gb of the memory, but I guess this is the default setup for SQL server, it looks like it reserves almost all the memory and will release it whenever operating sytem needs it.
This is SQL server, memory setting using default, Max server memory is 2147483647 MB
But why SQL server still complains about it, what we should do?
Thanks
February 6, 2012 at 11:23 am
Something over the weekend took more resources than you have available. You just have to figure out what to start trying to troubleshoot the issue.
Is SQL Server alone on the server or are there other apps? Did you release a new version of the software this weekend? Did you have extra large weekend data loads? I don't know, just guessing here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2012 at 12:35 pm
sqlfriends (2/6/2012)
This is SQL server, memory setting using default, Max server memory is 2147483647 MBBut why SQL server still complains about it, what we should do?
The first thing that you need to do is set the max server memory to a sensible value that will ensure SQL does not try to starve the OS. By leaving the setting at default, you've told SQL it can use up to 2048 TB of memory. Given that, it can and will take all the physical memory on the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 1:15 pm
[The first thing that you need to do is set the max server memory to a sensible value that will ensure SQL does not try to starve the OS. By leaving the setting at default, you've told SQL it can use up to 2048 TB of memory. Given that, it can and will take all the physical memory on the server.?
Thanks, if this is a virtual server, does it make sense to do the same thing?
My plan is to setup max memory to 6 gb, and leave 2 gb to OS.
But here comes the part I don't understand, by default setting, it uses as much as it can grab from the server memory, but the error is "There is insufficient system memory in resource pool 'internal' to run this query."
If I only assign 6 gb memory to SQL server, it will be much less than 2048TB, SQL server will still have even less memory, do I miss something here?
Thanks
February 6, 2012 at 1:34 pm
sqlfriends (2/6/2012)
[The first thing that you need to do is set the max server memory to a sensible value that will ensure SQL does not try to starve the OS. By leaving the setting at default, you've told SQL it can use up to 2048 TB of memory. Given that, it can and will take all the physical memory on the server.?
Thanks, if this is a virtual server, does it make sense to do the same thing?
My plan is to setup max memory to 6 gb, and leave 2 gb to OS.
But here comes the part I don't understand, by default setting, it uses as much as it can grab from the server memory, but the error is "There is insufficient system memory in resource pool 'internal' to run this query."
If I only assign 6 gb memory to SQL server, it will be much less than 2048TB, SQL server will still have even less memory, do I miss something here?
Thanks
Its looking for memory outside of the buffer pool and this is the area which is being starved
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 6, 2012 at 1:38 pm
sqlfriends (2/6/2012)
[The first thing that you need to do is set the max server memory to a sensible value that will ensure SQL does not try to starve the OS. By leaving the setting at default, you've told SQL it can use up to 2048 TB of memory. Given that, it can and will take all the physical memory on the server.?
Thanks, if this is a virtual server, does it make sense to do the same thing?
Doesn't make the slightest difference if it's a virtual or physical server. Allowing the buffer pool (which is what max server memory controls) to grow as large as it wants is a poor idea. It's asking for other areas to be starved, for the OS to be starved. If the OS is starved for memory it means system instability and even server crashes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 1:56 pm
I'd recommend in an 8 GB machine... cap it at 6 GB.
I like to go:
Memory Capacity - 2GB to OS - 1 GB for every 16 GB of memory available there after...
Most systems I've dealt with don't have 16 GB remaining so usually its just capacity - 2 GB to OS...
8 - 2 = 6 GB Max Memory SQL Server...
---
What's happening here is you have a memory intensive statement that you need to identify, evaluate, and see if you can optimize it, break it up, etc.
Setup some perfmon counters and see if you're paging out a lot. You could increase the page file and see if that helps although this is just a work around, not necessarily the right thing to do.
I hope this helps.
February 6, 2012 at 2:20 pm
Thanks all, this is a server that hosts vendor product for network department that manges VDI.
SO this message: There is insufficient system memory in resource pool 'internal' to run this query
Does it mean SQL server itself (buffer pool) has not enough memory, or it means OS doesnot have enough memory?
Because it said to run the query, I guess it means in SQL server buffer that has not enough memory, am I wrong?
February 6, 2012 at 2:37 pm
Just checking, Full text enabled? Full text queries been run?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 2:45 pm
sqlfriends (2/6/2012)
Because it said to run the query, I guess it means in SQL server buffer that has not enough memory, am I wrong?
No, it does not mean that. It's a non-buffer pool allocation that causes this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 2:47 pm
sqlfriends (2/6/2012)
Does it mean SQL server itself (buffer pool) has not enough memory, or it means OS doesnot have enough memory?
No it doesn't
sqlfriends (2/6/2012)
I guess it means in SQL server buffer that has not enough memory, am I wrong?
The buffer pool has all the memory it needs, this is a problem with memory allocation outside of the buffer pool as i stated in my post above.
As already advised, you need to set the max memory for your instance.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 6, 2012 at 2:56 pm
sqlfriends (2/6/2012)
Thanks all, this is a server that hosts vendor product for network department that manges VDI.SO this message: There is insufficient system memory in resource pool 'internal' to run this query
Does it mean SQL server itself (buffer pool) has not enough memory, or it means OS doesnot have enough memory?
Because it said to run the query, I guess it means in SQL server buffer that has not enough memory, am I wrong?
In SQL Server versions prior to 2012, max server memory only sets the buffer pool size which is for single page allocations made through the single_page_allocator. Your problem is with a request being made from the multi_page_allocator which is outside of the buffer pool usage in the SQLOS. By reducing the size of the buffer pool you will leave additional memory available in the environment that can be used to back the multipage allocation for the internal resource pool. Gail and Perry are correct here.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
February 6, 2012 at 3:00 pm
Thanks,
There are hundreds tables in the database, I right click one, and I see Full_text-index grayed out.
I also do a query SELECT FullTextServiceProperty('IsFullTextInstalled')
It returns 0
February 6, 2012 at 3:00 pm
Just to reinforce what others are already telling you, that error means that a SQL process tried to use memory outside of the buffer pool but there wasn't enough memory available.
This means the problem is that there is not enough free memory. The problem is that you are allowing the buffer pool to consume all available memory. This is a universally agreed upon bad practice. You need to limit Max Server Memory as others have already stated to allow enough free memory for the OS, other processes on the server, and SQL processes that do not use the buffer pool.
Considering that you have non-pooled processes that are already failing to get memory, setting Max Memory to 6 GB might not be the right number here. I would suggest starting with a lower number and then adjusting up if you determine you don't need that much free.
SQL Server does not respond well to requests for more memory. you can't rely on it to release enough memory every time it is needed.
February 6, 2012 at 3:05 pm
Jonathan and Gail are correct here. Prior to SQL Server 2012, the Max Server Memory instance level setting only controls how much memory can be used by the Buffer Pool. Setting Max Server Memory to a lower value (such as 5000 on a server with 8GB of RAM) will help free up more memory for other parts of SQL Server besides the Buffer Pool.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply