query processor ran out of internal resources - extremely long sql query

Question

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.

Answer 1

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.

Answer 2

In support of Daniel Hutmacher's perfectly good answer, I wager if you call Microsoft, they'll tell you what the error message says. And maybe in a few years you'll read that SQL Server 2019 supports 16,000-table joins.

I wouldn't even create a temporary table. I'd create a normal table, Megalog, populate it, and create a daily process to append each day's log when it's cut. I'd base all my queries on Megalog, and delete obsolete rows as and when the associated log tables are dropped. I understand you're under constraints that make that impossible right now, but IMO you won't have a choice. You might as well have a table the supports your business.

From a design point of view, yours is a good example of a classic design error: placing data in the metadata. The data in your metadata is the presence of 1-day log tables, the number of which grows without bound. When a schema grows without bound, it's holding something that should be in rows, a/k/a data.

When it comes time to modify the design -- as it seems will happen soon, given your question -- I suggest you look for cases where the schema is modified dynamically, and look for opportunities to use a static schema instead. The system and your SQL will become simpler and more stable.

Someone will doubtless tell you that querying a 20-million row table is faster than querying a 200-billion row table. That's true, to an extent. But if it's clustered on date, the smaller table avoids only log2(2000) = ~10 seek operations, on average, mostly against the cache. And SQL server has partitioning features that make that figure obsolete, if you're willing to do the work and accept the complexity. A single table, indexed for the purpose, should perform much better than the orangoutang you're currently wrestling. ;-)