Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a SQL Server 2008 database server (it happens to be running under MS Failover Clustering, but I don't think that is relevant here).

Our application runs Hibernate for DB access, and since we upgraded recently from v3.1 to 3.6, we have been experiencing SQL Server crashing regularly (every 24-48 hours, but sometimes more frequently).

The specific problem in question seems to be memory related. Just before the server crashes (and is then auto restarted by the failover cluster manager it seems), we get a load of these errors:

Error: 701, Severity: 17, State: 130.
There is insufficient system memory in resource pool 'internal' to run this query.

also occasional (but regular) messages of

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

I'm also getting some app level errors such as

java.sql.SQLException: A time out occurred while waiting to optimize the query. Rerun the query.

and then the exciting and possibly instructive error:

The query processor ran out of internal resources and could not produce a query plan. 
This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. 
Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

The load on the server hasn't changed so there's no reason it should now be running out of memory when it previously wasn't indicating a problem with queries being sent to it.

Now to the question - how do I trace the queries that are causing this error (and thus presumably all of the problems)? It seems like since our Hibernate upgrade, it's been firing some huge queries at SQL Server, and that's broken it. As it happens, I have some ideas as to what they might be, but it would be good to be able to trace them.

I can of course run SQL Server profiler, but once this is done (and produced an enormous amount of data - it's a busy OLTP database), how do I filter to find the problematic queries?

Thanks!

share|improve this question

migrated from stackoverflow.com Apr 3 '13 at 14:53

This question came from our site for professional and enthusiast programmers.

1  
Is everything running on the same server? Meaning, is the application server, with java, also running on the database server? –  swasheck Apr 3 '13 at 15:17
1  
In association with @swasheck's question: Do you have an explicit value set for SQL Server max memory? Have you ruled out external memory pressure? –  Mike Fal Apr 3 '13 at 16:47
    
Have you tried looking at the black box traces? They may point you in the right direction. –  datagod Apr 9 '13 at 21:58
    
I just hit this thing, and traces I left running show an idle database from the application perspective. –  Joshua Jul 15 '13 at 15:35

1 Answer 1

Follow the steps outlines in How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server. The remedy action will depend on your findings. You can also read How to Identify Microsoft SQL Server Memory Bottlenecks which is more accessible.

One word of caution though: is unlikely you will find individual queries to blame. Tracking downs memory problems is more subtle than that. Keep in mind that when you're running out of resources and a query throws an out-of-memory error it may well be that the query that throws the error is just the victim, not the culprit.

share|improve this answer
    
Thanks - I've looked at those already, but the problem is that the server appears to work fine and then suddenly goes bang, it doesn't gradually run out of memory. It's also not clear from anything I can find online what the error "There is insufficient system memory in resource pool 'internal' to run this query." actually means - what is the internal resource pool as regards the results of DBCC MEMORYSTATUS? –  Pete Storey Apr 3 '13 at 14:47
    
Is this a development server? If so, could you downgrade to Hibernate 3.1 to verify the problem goes away? You have two initial lines of enquiry and you must try to eliminate one of them, either SQL Server has memory limits set and is exceeding them or some other part of the system is consuming memory and SQL Server is being squeezed. Profile the system around the times of the crash to determine which is happening. –  epo Apr 4 '13 at 8:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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