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?