sql server 2008 64 bit performance issue

hi team. yesterday i got a call from developer informing the error below

 There is insufficient system memory in resource pool 'internal' to run this query. 

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: There is insufficient system memory in resource pool 'internal' to run this query.

Source Error:

after some time server application started working there after in the mid night SQL Server, got restarted and now it is fine what might be the problem ,how can this be resolved

more ▼

asked Oct 27, 2010 at 03:45 AM in Default

avatar image

ramesh 1
2.2k 66 69 73

Did you check the windows error log and SQL Server Event Log?

Oct 27, 2010 at 05:27 AM Cyborg

Are there other applications running, or other instances?

Oct 27, 2010 at 06:06 AM Blackhawk-17

the above is the error ,which i have pasted by collecting it from sql serror log

Oct 28, 2010 at 08:50 AM ramesh 1
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

A quick google of this error seems to bring up lots of various causes and resolutions, the common theme being memory pressure outside of the buffer pool caused by third party software or even extended features of SQL (linked servers, XML processing, etc, etc.)

If this is a recurring problem, I would be tempted to open a case with Microsoft Support, as they are best place to diagnose what exactly is eating all the memory. You can find lots of 'solutions' on the web including trace flags, memory config, startup params, but unless you really know what you are doing, you could be harming the server more!

more ▼

answered Oct 27, 2010 at 03:54 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left

64 bit or not, how much memory is on the machine? Do you have other software running on the same machine? SQL Server is very much a memory pig. I would strongly recommend monitoring the system, put some diagnostics in place to see what is using memory and when. Simply relying on the error messages just isn't enough to be able to suggest what's happening on the system. I would also suggest you take a look at wait states and queues to see what things are slowing stuff down.

After that, I agree with @Kev. If you're hitting a recurring issue, it's either a configuration problem or possibly an internal error & you need to get MS involved.

more ▼

answered Oct 27, 2010 at 04:43 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

server installed on windows 2008 server enterprise editon with sql server 2008 sp1 standard edition as Database, with 16 GB as RAM . havina a db size of just 49GB.

Oct 27, 2010 at 05:11 AM ramesh 1

Database size doesn't really have anything to do with it. The issue is memory... How big is the query that the developer was running? Just a normal one or some kind of insane 50 page monster?

Oct 27, 2010 at 05:58 AM Grant Fritchey ♦♦

I am going to bet that it has nothing really to do with internal sql stuff here. It is probably poor use of externals - like @Kev was talking about, or CLR. I have experienced this when using SQLBackup from Redgate - it was stealing memory when doing the daily full backup. Made a change on how SQLBackup worked (less threads I think) and the problem disappeared.

Oct 27, 2010 at 06:04 AM WilliamD

I would look into CLRs as well.

Oct 27, 2010 at 06:05 AM Blackhawk-17

Yeah, makes sense.

Oct 27, 2010 at 06:14 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

To get SQL Server memory usage try querying sys.dm_os_sys_memory with something like

 SELECT  total_physical_memory_kb,
     available_physical_memory_kb ,
     total_page_file_kb ,
 FROM    sys.dm_os_sys_memory

This will give you an idea of the memory used by the SQL instance and what is left.

there are tools that can show the memory usage built in with the OS - others have mentioned perfmon, task manager etc. You can also get 3rd party tools that show the usage too, RedGate do one called SQLMonitor that lists all processes and their memory usage in real time. I believe Idera's product that does the same is called SQL Doctor.

more ▼

answered Nov 03, 2010 at 01:42 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

External memory pressure might be the issue.

more ▼

answered Oct 27, 2010 at 06:31 AM

avatar image

1.4k 3 20 7

i too agree with you, but how to start resolving it

Oct 28, 2010 at 08:52 AM ramesh 1

Install process explorer and identify which other processes are consuming memory other than SQL Server.

Rule of thumb, SQL Server should be the only heavy process running


Oct 28, 2010 at 11:03 AM ozamora

@ramesh re:"but how to start resolving it" You start by looking in Task Manger and determining the other large consumers of RAM. Unless it is a monster query then something else is probably requesting memory and SQL Server and the O/S have been trying to grant it.

Oct 28, 2010 at 12:19 PM Blackhawk-17

already made a eye on the tack manager only sqlservr.exe is the only program,which is a large consumer of memory ,out of 16GB RAM,it consumes 15.* GB. i think memory cap works.page locking is enabled in the server,does this effect in 64 bit server

Oct 28, 2010 at 09:43 PM ramesh 1

make sure you have proper mac memory setup for sql server. In a 16 gb system don't go over 13.

Oct 29, 2010 at 04:21 AM ozamora
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 27, 2010 at 03:45 AM

Seen: 2977 times

Last Updated: Oct 29, 2010 at 12:39 AM

Copyright 2018 Redgate Software. Privacy Policy