"There is insufficient system memory in resource pool 'internal' to run this query" error message

Question

"There is insufficient system memory in resource pool 'internal' to run this query" error message

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

SQLKnowItAll (6/6/2012)
David Benoit (6/6/2012)
Being that you have 128 GB of memory I am assuming that this is 64-Bit but just to be sure, is it?
I believe it must be, that and the forst section of the results from DBCC MEMORYSTATUS shows locked pages instead of AWE.

Yup, good point, see that now. Thanks Jared.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

deep_kkumar (6/6/2012)
GilaMonster (6/6/2012)
Every single query you run fails with that message?
YES.

Even SELECT * FROM (VALUES (1)) sub(a) ?

Select @@version?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jared SSC Guru Points: 61793 More actions · Answer 3

deep_kkumar (6/6/2012)
GilaMonster (6/6/2012)
Every single query you run fails with that message?
YES.

That can't be possible since you were able to run DBCC MEMORYSTATUS

Jared
CE - Microsoft

deep_kkumar Hall of Fame Points: 3243 More actions · Answer 4

GilaMonster (6/6/2012)
deep_kkumar (6/6/2012)
GilaMonster (6/6/2012)
Every single query you run fails with that message?
YES.
Even SELECT * FROM (VALUES (1)) sub(a) ?
Select @@version?

not these queries.............i tried to check what's running on the server at the time we faced this issue by running sp_who2 active but it took 2 mins to turn out the results. So i am not able to run the queries also.

deep_kkumar Hall of Fame Points: 3243 More actions · Answer 5

SQLKnowItAll (6/6/2012)
deep_kkumar (6/6/2012)
GilaMonster (6/6/2012)
Every single query you run fails with that message?
YES.
That can't be possible since you were able to run DBCC MEMORYSTATUS

Sorry. This issue was resolved when I restarted the server. I see these errors in event manager now and then. Just trying to find the exact reason behind it.

Adam Machanic SSCoach Points: 15370 More actions · Answer 6

This error occurs when a query has to wait for memory, the wait timeout expires, and after timing out the server does not have the minimum amount of memory available to run the query.

Most of the information you need to debug is available via sys.dm_exec_query_memory_grants.

In this view you will find:

- The session_id of the queries that have memory (grant_time not NULL)

- The session_id of the queries waiting on memory (grant_time NULL)

- The amount of memory granted (granted_memory_kb)

- The amount required (i.e. the amount that's not available when the query finishes waiting -- required_memory_kb)

- The amount used, which if less than the amount granted will tell you that SQL Server is granting too much

- The timeout interval

- plan and SQL handles that you can use to find out what the actual queries are that are doing all of this

My advice would be to start collecting from this view once every five minutes. Timestamp the collections and correlate what you see to when you get these errors so that you can figure out which queries are causing the problem.

--
Adam Machanic
whoisactive

deep_kkumar Hall of Fame Points: 3243 More actions · Answer 7

Hi Adam,

I don't think it's a memory grant issue. I checked the grant values at that time and the value is zero. However the buffer cache hit ratio decreased from 100 to 93.4 during this time, hight page reads/sec , high disk waits, high memory waits and high network waits, high bookmark lookups.

Thanks,

Adam Machanic SSCoach Points: 15370 More actions · Answer 8

How are you measuring these waits? The only "memory wait" that exists has to do with workspace memory -- same as the view I suggested. So if you saw high memory waits, you should have seen rows in the view, and the other way around. Might want to double check your collection scripts.

--
Adam Machanic
whoisactive

deep_kkumar Hall of Fame Points: 3243 More actions · Answer 9

we have the sql sentry tool.

I just went back to the time we saw this message and it looks like we have zero memory grants at that time.

Adam Machanic SSCoach Points: 15370 More actions · Answer 10

deep_kkumar (6/11/2012)
we have the sql sentry tool.
I just went back to the time we saw this message and it looks like we have zero memory grants at that time.

Well then the tool is giving you incorrect information. The error you've reported ONLY occurs due to the situation I described.

--
Adam Machanic
whoisactive

Dhruva_51 SSChampion Points: 10521 More actions · Answer 11

we also have the same issue. Let me know if you find the solution.

Thanks,

jhall-640702 SSC Enthusiast Points: 104 More actions · Answer 12

I'm at SQL Sentry, and I wanted to get a message to the OP.

If you would like, you can email us at support at sqlsentry.net or give us a call (contact info. in Help -> About).

I would be happy to dig into some reports with you to help troubleshoot the problem encountered on this server. There are a lot of unknowns still given what has already been discussed here.

We can also go over everything in a live web meeting if you can spare the time.

Thanks, and I'll look forward to hearing from you.