SQL SERVER – Query Optimizer Hint ROBUST PLAN – Question to You

While cleaning up my bookmarks this week, I stumbled upon a very small interesting thing. I can proudly call myself a pro at finding stuffs, but after continuously hunting online I could not gather comprehensive information about this topic. I was actually looking for a practical example for Query Optimizer Hint “ROBUST PLAN”. Before I seek help from you, let us first try to understand what query optimizer hints is and then we will move on to the concept of “ROBUST PLAN”.

To put it simply, Query hints is a T-SQL clause which on running directs T-SQL query to run in a particular manner using suggested index, joins or other logic. Query hints can force the kind of execution plan SQL Server needs. In general, SQL Server query optimizer selects the best execution plan itself to run any query; it is a good idea to just keep it at its default setting.

“ROBUST PLAN” is a kind of query hint which works quite differently than other hints. It does not improve join or force any indexes to use; it just makes sure that a query does not crash due to over the limit size of row. Let me elaborate upon it. In a table, there is an outsized row that crashes any operation going over it. T-SQL has specific query hint where query engine ignores such an outsized row which will potentially crash it and stop from working. Using ROBUST PLAN, query optimizer can be forced to ignore such errors that are generated because of outsized rows. This usually does not happen with SQL Server 2005 because of its tremendous capacity of row size (2 GB). However, here we are not talking about row size beyond the limit; we are talking about SQL operation that considers the given row to be too large for this operation, even if the row is in MB or less.

Now, after having thoroughly gone through the above two explanations, I request you all to find out which operation can generate this error and compel us to use ROBUST PLAN hint. I am disappointed after my extensive online search. I could not find a single example or information by anyone where they have suggested that they needed this hint. According to me, this is a useful feature which can be used wisely, as this hint reduces the performance as well in some cases. For queries where the entire execution is not working, this is at least a better option where you can expect to obtain some answer.

I am looking for a query which runs fine with option “ROBUST PLAN” but without it the query fails to produce any result and generates error.

If you know the answer, then please share it with me. I will post your answer here with due credit.

Reference : Pinal Dave (http://blog.SQLAuthority.com)


While cleaning up my bookmarks this week, I stumbled upon a very small interesting thing. I can proudly call myself a pro at finding stuffs, but after continuously hunting online I could not gather comprehensive information about this topic. I was actually looking for a practical example for Query Optimizer Hint “ROBUST PLAN”. Before I seek help from you, let us first try to understand what query optimizer hints is and then we will move on to the concept of

About these ads

4 thoughts on “SQL SERVER – Query Optimizer Hint ROBUST PLAN – Question to You

  1. When in doubt, consult the source:

    ROBUST PLAN
    Forces the query optimizer to try 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 have to store and process rows that are wider than any one of the input rows. The rows may be so wide that, sometimes, the particular operator cannot process the row. If this occurs, the Database Engine 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.

    If such a plan is not possible, the query optimizer returns an error instead of deferring error detection to query execution. Rows may contain variable-length columns; the Database Engine allows for rows to be defined that have a maximum potential size beyond the ability of the Database Engine to process them. Generally, despite the maximum potential

    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/12f66b30-6a46-41f0-9a7c-3690f5ddb538/

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c07f1ad3-4080-4f84-8672-88312c8b24f6/

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c07f1ad3-4080-4f84-8672-88312c8b24f6/

  2. Here is a query that we use:

    Select
    HeaderText = v.CombinedText,
    Text1 = t1.CombinedText,
    Text2 = Convert(char(6000), t2.CombinedText),
    Text3 = Convert(char(6000), t3.CombinedText),
    Text4 = Convert(char(7401), t4.CombinedText)
    from vw_OnlineExport_Form v
    Inner Join tmpResponseConvert1 t1 on t1.SurveyNumber = v.SurveyNumber
    Inner Join tmpResponseConvert2 t2 on t2.SurveyNumber = v.SurveyNumber
    Inner Join tmpResponseConvert3 t3 on t3.SurveyNumber = v.SurveyNumber
    Inner Join tmpResponseConvert4 t4 on t4.SurveyNumber = v.SurveyNumber
    OPTION (ROBUST PLAN)

    Our sql system needs to convert data that is full of verbatim comments that are stored in a vertical format to a horizontal fixed length format. We do this by building records of 6000-7500 characters each and join them all together as you see above. The end result is an output fixed file of 22500.

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s