x

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 '10 at 03:45 AM in Default

ramesh 1 gravatar image

ramesh 1
2.1k 59 67 69

Did you check the windows error log and SQL Server Event Log?
Oct 27 '10 at 05:27 AM Cyborg
Are there other applications running, or other instances?
Oct 27 '10 at 06:06 AM Blackhawk-17
the above is the error ,which i have pasted by collecting it from sql serror log
Oct 28 '10 at 08:50 AM ramesh 1
(comments are locked)
10|1200 characters needed characters left

4 answers: sort oldest

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 '10 at 03:54 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(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 '10 at 04:43 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

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 '10 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 '10 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 '10 at 06:04 AM WilliamD
I would look into CLRs as well.
Oct 27 '10 at 06:05 AM Blackhawk-17
Yeah, makes sense.
Oct 27 '10 at 06:14 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
External memory pressure might be the issue.
more ▼

answered Oct 27 '10 at 06:31 AM

ozamora gravatar image

ozamora
1.4k 2 3 5

i too agree with you, but how to start resolving it
Oct 28 '10 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

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
Oct 28 '10 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 '10 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 '10 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 '10 at 04:21 AM ozamora
(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 ,
    available_page_file_kb,
    system_memory_state_desc 
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 '10 at 01:42 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1816
x55
x53

asked: Oct 27 '10 at 03:45 AM

Seen: 2276 times

Last Updated: Oct 29 '10 at 12:39 AM