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 (https://blog.sqlauthority.com)
3 Comments. Leave new
Sheesh, searching for this error brings up your article on many different websites. :)
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
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.