ROBUST PLAN

ROBUST PLAN

Post by JMNUS » Wed, 03 Mar 2004 02:19:58



In SS 7.0, I am getting this error and I cannot find any
syntactical reference to show me how to add to my query.
Does anyone know what I can do to implement this to see
why my query is failing?

Server: Msg 510, Level 16, State 2, Line 60
Cannot create a worktable row larger than allowable
maximum. Resubmit your query with the ROBUST PLAN hint.

TIA, Jordan

 
 
 

ROBUST PLAN

Post by Kalen Delane » Wed, 03 Mar 2004 02:54:06


Hi Jordan

Did you try searching for ROBUST PLAN in the Books Online? That's the first
place you should always look.

Hints like this one are added as part of the OPTION clause at the end of the
query:

SELECT
FROM
WHERE
OPTION (ROBUST PLAN)

The BOL goes on to describe ROBUST PLAN:

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.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


Quote:> In SS 7.0, I am getting this error and I cannot find any
> syntactical reference to show me how to add to my query.
> Does anyone know what I can do to implement this to see
> why my query is failing?

> Server: Msg 510, Level 16, State 2, Line 60
> Cannot create a worktable row larger than allowable
> maximum. Resubmit your query with the ROBUST PLAN hint.

> TIA, Jordan


 
 
 

ROBUST PLAN

Post by JMNUS » Wed, 03 Mar 2004 03:01:52


Kalen,

Thank you for the help.  I did look in BOL but I was
unaware of the OPTION() syntax.

Thanks again.

Quote:>-----Original Message-----
>Hi Jordan

>Did you try searching for ROBUST PLAN in the Books

Online? That's the first
Quote:>place you should always look.

>Hints like this one are added as part of the OPTION

clause at the end of the
Quote:>query:

>SELECT
>FROM
>WHERE
>OPTION (ROBUST PLAN)

>The BOL goes on to describe ROBUST PLAN:

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

>--
>HTH
>----------------
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com


message

>> In SS 7.0, I am getting this error and I cannot find any
>> syntactical reference to show me how to add to my query.
>> Does anyone know what I can do to implement this to see
>> why my query is failing?

>> Server: Msg 510, Level 16, State 2, Line 60
>> Cannot create a worktable row larger than allowable
>> maximum. Resubmit your query with the ROBUST PLAN hint.

>> TIA, Jordan

>.

 
 
 

1. Robust Plan execution error?

I am running a select against a View that exceeds the max allowable
row size and added OPTION (ROBUST PLAN) to the select to enable the
select to return a result when the row size is a problem. The original
view does a number of joins.

One of our users has reported that some data values they expect to see
in some of the columns for specific rows are missing or incorrect.
Could using the ROBUST PLAN hint cause this to occur?

Thanks,

Chris

2. 4721-CO-COLORADO SPRGS-ORACLE Case Tools-ORACLE Forms-ORACLE Reports 2.X-ORACLE

3. Why is Stored Proc plan slower than query plan

4. Please help -- urgent MDB recovery problem

5. Execution plan,Query plan?

6. perl: $sth->{TYPE} ...?

7. Locking Plan part of Query Plan?

8. D3/Linux UPD/RET locks at the account level

9. Is Replication robust ???

10. Adding more robust error messages to a custom task

11. Less leaky and more robust ODBC driver wanted

12. Panorama NovaView and AS2K - robust solution !

13. How robust is access