9

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!

Improve this question
5
  • 1
    Is everything running on the same server? Meaning, is the application server, with java, also running on the database server?
    – swasheck
    Apr 3, 2013 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, 2013 at 16:47
  • Have you tried looking at the black box traces? They may point you in the right direction.
    – datagod
    Apr 9, 2013 at 21:58
  • I just hit this thing, and traces I left running show an idle database from the application perspective.
    – Joshua
    Jul 15, 2013 at 15:35
  • Do you use any fulltext searches ? Also, what is the exact build no + edition of sql server you are running on ?
    – Kin Shah
    Aug 28, 2015 at 1:47

2 Answers 2

Reset to default
5

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.

Improve this answer
2
  • 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, 2013 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, 2013 at 8:32
0

Seems you want go for Extended Events configuration using the events query_memory_grant_xxxxx.

This is best option for you to log the information and stored out-size SQL Engine which you can read anytime (you can also watch live data), the stored information would not be wiped-out when server restart occurs unlike DMVs

Quick setup steps..

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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