4

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.

  • 2
    You are running RTM version. I strongly suggest you to apply SQL Server 2012 SP3. Since message says its rare condition and does not happens very often I believe in SP3 you might not get the issue. Having said this you must also make sure you have query running which is supported by correct index and updated stats. – Shanky Feb 19 '16 at 10:00
  • @Shanky, I tried this on a SQL Server 2014 edition also and I was getting the same error over there also. I think the problem is because the SQL being generated is around 50 pages of Word. – milan m Feb 19 '16 at 10:03
  • Then problem might be with the query you are running to fetch the report. Its is so bad that optimizer is not able to decide what plan to create. – Shanky Feb 19 '16 at 10:13
17

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.

  • Although I completely understand and agree with the solution you proposed, due to some business restrictions, I cannot modify the application. We have plan to modify the application in near future. Is there any way or hints which I may use so that SQL server will split the query for every "union all" statement.? Thanks. – milan m Feb 19 '16 at 12:12
  • 1
    Then I think you're in trouble. If the report is important enough, someone will make the changes you need. Talk to your vendor or software developers. Or management, for that matter. For what it's worth, maybe more server memory might do the trick, but I don't think so. – Daniel Hutmacher Feb 19 '16 at 12:18
5

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. ;-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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