Sql-server – How to trace SQL queries which are crashing SQL Server

crashsql serversql-server-2008

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!

Best Answer

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.

Related Question