sql server 2005 sp3 insufficient memory to run query problem

Question

I am trying to execute a humongous query with close to 200 inner joins on a database. It gives me the following error

Msg 701, Level 17, State 123 Line 1

I am running the database on a Dual core 2.7 GHz machine with 2GB of RAM. Is there any way I can get this query to execute?

Answer 1

This sounds like a table design issue, what on earth are you doing joining in 200 tables? that is most likely the problem, which can only be cured with a redesign.

Maximum Capacity Specifications for SQL Server

Columns per SELECT statement   4,096
REFERENCES per table           253
Tables per SELECT statement    Limited only by available resources

Answer 2

200 joins is actually very common if you fall into the EAV trap. If you have one entity with 200 columns, there's 200 joins for you!

Of course, SQL Server has no problem with 200 joins, but quite possibly it's miscalculating the amount of memory needed. This is especially likely for hash joins, which trade memory for better performance. So a first step would be to replace all joins with loop joins, for example inner loop join. A loop join requires very little memory.

If that doesn't work out, look at the execution plan. The real plan will probably not make it past a memory error, but you can see the estimated execution plan:

SET SHOWPLAN_ALL ON

From the documentation:

When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed

This could give a clue about what SQL is planning to do.

Answer 3

Just out of curiosity are all your FK fields indexed?

Is there some way you can split this up into mutiple queries with fewer joins? For instance if you are doing an insert can you insert just the main fields that ensure uniqueness of the record and then do separate updates to get the rest of the information?