SQL Memory Leak ?

Question

Post reply

SQL Memory Leak ?

SQLSACT

SSC-Insane

Points: 21838
More actions
September 28, 2012 at 4:43 am

#282136

Hi all
I received the following error in the error logs
There is insufficient system memory in resource pool 'internal' to run this query.
I'm trying to understand what causes this error to come up, is there a query that requires too many pages to be brought from disk into memory?
I am running Windows/SQL 2008 Enterprise 64bit
Locked Pages in Memory is enabled for the SQL Service account.
Total Memory on the server: 16GB
Max memory setting in SQL: 10GB
Any ideas?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Nothing to do with the buffer pool, it's a query that requires more workspace memory than is allowed.

What query is it?

This is not a memory leak.

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

GilaMonster (9/28/2012)
Nothing to do with the buffer pool, it's a query that requires more workspace memory than is allowed.
What query is it?
This is not a memory leak.

This happened a few days ago so I'm not sure if the query is still in cache, if it even made it into the cache.

How is workspace memory allocated? Can we control that or is it managed by SQL Server?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It's not 'allocated' per se. It's requested by queries and whatever this query requested was more than the resource governor defaults are (which are always in place, regardless whether you've enabled resource governor or not)

Go check Adam Machanic's PASS session for last year (it's available online) on workspace memory tuning.

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

GilaMonster (9/28/2012)
It's not 'allocated' per se. It's requested by queries and whatever this query requested was more than the resource governor defaults are (which are always in place, regardless whether you've enabled resource governor or not)
Go check Adam Machanic's PASS session for last year (it's available online) on workspace memory tuning.

Is there a way I can simulate this?

Using a test machine with 3GB of ram, I changed the min memory per query (KB) config setting to 4GB, ran a query and there was no error

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Unless you tested the same query you probably won't trigger any error. Not all queries need workspace memory.

Could it be this:

http://support.microsoft.com/kb/982854

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

GilaMonster (9/28/2012)
Unless you tested the same query you probably won't trigger any error. Not all queries need workspace memory.
Could it be this:
http://support.microsoft.com/kb/982854

I don't think so

Is there a way I can check if my DB use Full text?

andrew gothard SSChampion Points: 12315 More actions · Answer 7

Does it run OK if you add with recompile?

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Sure, see if you have full text enabled for it and full text indexes created.

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

Can you provide more details about your environment and how it is using SQL Server? For example, are there any Linked Servers in use and if so, to what, what drivers are being used and how are they setup? Do you use Service Broker, Fulltext, replication, or other features of SQL Server that have internal components running?

As Gail pointed out, this is an execution memory issue, but for something running in the internal resource pool, so I'd be looking at sys.dm_resource_governor_resource_pools, sys.dm_exec_query_resource_semaphores, and sys.dm_exec_query_memory_grants to determine if the problem is simply transient, or you have something else happening. You are going to have to be actively polling/monitoring these to really be able to see what happens.

What else is in the ERRORLOG at the time that this error occurs? Generally there is more than just this one error.