Error - Cannot create a work table row...

[jianhua]

Hi,

Does anyone know what caused the following error?

"Cannot create a work table row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint."

Thanks in advance...

 

[redlam]

Are you using a temp table or table variable in your query? Is it possible that you are trying to create a record length beyond the maximum allowable? SQL only allows 8060 bytes per row.

 

[redlam]

Here's some info on the ROBUST PLAN option from BOL:

ROBUST PLAN

Forces the query optimizer to attempt a plan that works for the maximum potential row size, possibly at the expense of performance. When the query is processed, intermediate tables and operators may need to store and process rows that are wider than any of the input rows. The rows may be so wide that, in some cases, the particular operator cannot process the row. If this happens, SQL Server produces an error during query execution. By using ROBUST PLAN, you instruct the query optimizer not to consider any query plans that may encounter this problem.

 

[jianhua]

Hi redlam,

Thanks for your response. It's true the row length of the table is over 8060. But what I don't understand is that it worked for a year and suddenly one day it didn't and got this error. Would you please tell me how to do the ROBUST PLAN as I'm not a database admin? Thanks in advance...

jianhua

 

[redlam]

Hi Jianhua.
I have never used Robust Plan and I should mention this warning from SQL BOL:

&quot;Caution: Because the query optimizer usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint> (Robust Plan is a query hint), and <table_hint> be used only as a last resort by experienced database administrators.&quot;

It might be better to change your query so that it does not produce such a large row size. Look for cursors, temp tables and group by clauses that may be producing this record size in the intermediate work table. If this is not an option, I believe you can avoid that error by using Robust Plan like this:

select *
from table1
where myid = 1
option (robust plan)

If you need futher assistance, it might be helpful if you copy and paste your query into this post.

Good luck!

 

[jianhua]

Hi redlam,

Thanks a lot for your help. I have worked out now. Although the row length is still larger than 8060, I split the big query into small ones, querying what I need each time instead of querying everything.

Thanks again,
jianhua

 

[moonshadow]

It's possible that only recently you've had data (for example in varchar fields), that only just pushed the row size above 8060, whereas previously you were just below it.