I have a SQL Server 2012 database with around 2000 tables (daily log files) with around 20 million records each.
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.