1

In Microsoft SQL Server Management Studio 12.0.4100.1 I have a table with about 12.600.000 records. If I try to show the TOP 1000 of these records with an ORDER BY [bigint column], the query runs for a few minutes, then fails with a message of "There is insufficient memory available in the buffer pool."

However, if I look in the task manager, I see that the SQL Server Windows NT - 64 Bit process only uses a small part of the 32 GB that the computer has. If I run this query after a reset of the local server, I first see the proces using a fair amount of CPU and Disk capacity, while the memory usage steadily climbs. But after a few seconds the memory usage stops growing. Exactly at what level it stops changes every time I try it. Sometimes it's at 1.5 GB, sometimes around 3.5 GB. In either case, the memory usage no long changes, the Disk and CPU usage drop to 0, and the SQL query will keep executing for 2-4 more minutes before failing.

I checked the Max Memory of the local server in SQL Managment Studio, and it was set to the highest value. I actually tried lowering it to 16 GB, but the query still doesn't use nearly what I think it should be allowed to.

Am I missing something? Is this a limitation of SQL Server Express, which I am using at the moment?

0

I believe memory usage in Express edition is limited to 1Gb per instance: http://sqlknowledge.com/2014/04/sql-2014-express-edition-limitations/

So setting Max Memory any higher than that will be ignored by an Express instance. With that in mind I'm a bit surprised that you are seeing memory usage hitting 3.5Gb

You mention the ORDER BY in your question - does the query run ok without it?

  • It does indeed appear to have been an express issue. My company provided me with a SQL Server licence, and after having copied the data to the new server the issue has been resolved. It now claims all the memory it needs, and the query succeeds. And just for reference, yes, a SELECT TOP 1000 without an ORDER BY ran just fine. And the 3.5GB usage was usually only when the SQL Management Studio had been open a while. After a reset of the server, it generally stuck around 1.5 GB. So perhaps more instances were open. – user2527902 Dec 15 '16 at 14:38
0

Am I missing something? Is this a limitation of SQL Server Express, which I am using at the moment?

Yep their is limitation in SQL Server express regarding memory it can use and don't refer to books online about documentation that SQL Server express instance is limited to 1 GB this is bit misleading. I quote from this support article

Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). The rest of the caches in the SQL Server memory manager can use much more memory than is specified by these edition limits. For example, a SQL Server 2012 Express edition can use only a maximum size of 1.4 GB for its database cache. Other caches (such as the procedure cache, the metadata cache, and so on) can consume memory up to the size specified by the "max server memory" configuration.

This is why the memory utilization in task manager shows more than 1 G. So 1 GB limitation is only for database cache other caches like procedure,plan cache can still add to SQL Server memory utilization.

Ideally you should use DMV sys.dm_os_process_memory for looking at memory utilized by SQL Server instance, I always advise users not to use Task Manager.

Regarding OOM error are you getting this error when running query in SSMS, this can also be limitation from SSMS side which gives OOM error when user forces it to return large result set. Instead you can set SQL Server to post result in file and save it on drive to check if you still get the OOM error.

Can you also post information from sp_readerrorlog about this OOM error

  • Thank you for the information. I've managed to resolve the problem by obtaining a SQL Server licence, which indeed does not have the memory issue. – user2527902 Dec 15 '16 at 14:49
  • Yep standard and enterprise have much more memory capacity as compared to express edition but before that make sure it was actually the memory limitation causing the issue not the query itself – Shanky Dec 15 '16 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.