asked
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
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!
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.
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?
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.
External memory pressure might be the issue.
@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.
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
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.
