Sql-server – query processor ran out of internal resources – extremely long sql query

sql serversql-server-2012

I have a SQL Server 2012 database with around 2000 tables (daily log files) with around 20 million records each.
Output of select @@Version

Microsoft SQL Server 2012 – 11.0.2100.60 (X64) Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )

Now, in order to generate a yearly or 3-years report, I need to run query on each table and "Union All" the results. The queries are generated dynamically and are usually small in size. However after doing "Union all: with 1000 tables, it become a single huge query.

While processing such queries, I am getting an error as highlighted below.

The application is already built, tested and in live production and hence I cannot use temp tables or hash tables to store intermediate results.

Now, how do I get rid off this error message?

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.

Best Answer

The error message is there for a reason. Using UNION on thousands of tables in a single query is performance suicide. Yes, I understand that your reporting application is already in production, but I would still strongly recommend that you to go back to your development team and update the application or, even better, rebuild its logic in a stored procedure instead.

As you suggest in your question, create a temp table, run each of those queries sequentially and dump the results into the temp table. Then, compile the report from the temp table. All of this can be done in a stored procedure, which will also cover other important aspects like security and performance.

This carries a range of advantages:

  • Temp table can be indexed for performance
  • You won't bloat your server's query plan cache (and in the process evict pretty much any other query plan that has ever been cached)
  • The query won't take forever to "compile" (i.e. turn into an execution plan)

I'm not trying to mess with you. It's my firm conviction that if you don't rebuild the report now, you will most likely have to some day, going forward.

Related Question