SQL query plan and minimum row size exceeds the maximum allowable of 8060 bytes

user_3437567 0
Viewed 1393

I'm getting the following error when running a query against my local database :

The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, consider reducing the number and/or size of the fields in the clause. Consider using prefix (LEFT()) or hash (CHECKSUM()) of fields for grouping or prefix for ordering. Note however that this will change the behavior of the query.

This occurs when running a query that looks similar to this (apolgies for the lack of detail) :

SELECT <about 183 columns>
FROM tableA
INNER JOIN tvf1(<params>) tvf1
  ON tvf1.id = tableA.X1
INNER JOIN tvf2(<params>) tvf2
  ON tvf2.id = tableA.X2
INNER JOIN tvf3(<params>) tvf3
  ON tvf3.id = tableA.X3
INNER JOIN tvf4(<params>) tvf4
  ON tvf4.id = tableA.X4
INNER JOIN tvf5(<params>) tvf5
  ON tvf5.id = tableA.X5

The table-valued-functions above all use a combination of GROUP BY, ROW_NUMBER() and other aggregation functions. While binary debugging, commenting out any 2 of the above joins results in the error not occurring, doesnt matter which though.

My database is running on Compatibility Level 2019. If i try setting Legacy Cardinality Estimation to On then the error no longer happens but I dont understand what this setting does.

edit : If the database compatibility level is 2016 then everything works as expected as well

A concern i have is that the production database might be upgraded in future and this error could occur.

Edit : I've managed to get the column count down to a handful now however my results are inconsistent.

SELECT 
         
        Other =  TvfGroupData.Other             
        ,GroupA = TvfGroupData.GroupA
        ,GroupB = TvfGroupData.GroupB
        ,GroupC = TvfGroupData.GroupC

        
    , [Max Created Date] = 
        (SELECT MAX(Value)
            FROM (VALUES 
                (Tvf1.CreatedDate)
                ,(Tvf2.CreatedDate)
                ,(Tvf3.CreatedDate)
                --,(TvfGroupData.CreatedDate)
                ,(Tvf3.CreatedDate)
                ,(Tvf4.CreatedDate)
            ) AS AllValues(Value)
        )

        
FROM TableA
LEFT JOIN Tvf1() ...
LEFT JOIN Tvf2() ...
LEFT JOIN TvfGroupData() ...
LEFT JOIN Tvf3() ...
LEFT JOIN Tvf4() ...

In the above query the following scenarios work :

  1. excluding only GroupA column.
  2. excluding only GroupB, GroupC column

Other combinations all fail with the error :

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

sql-server
2 Answers

The cardinality estimator is how SQL Server generates the execution plan, meaning how the engine will execute and assemble the disparate sets of data.

The recent changes in the engine usually (but not always) result in better execution plans, leading to faster queries response while consuming less resources.

If you look into how SQL Server processes a query statement, the full data set is gathered before the unwanted columns are excluded. When multiple data sets are combined, the engine may exclude columns before joining to another set, or it may join the sets before excluding columns. It is based on what the engine "sees" in your data patterns (statistics).

UDFs are a frequent stumbling block for the query plan optimizer as a poorly formed function masks the data statistics and prevents the engine from efficiently piecing the data together.

All that to say, the updated engine is looking at your data and determining it is more efficient to combine multiple sets before eliminating unwanted columns.

I believe you may be able to fix this by subselecting the columns you need from your functions before joining to the outer set.

SELECT * 
FROM (select SpecificColumns from tableA) as tableA
INNER JOIN (select SpecificColumns from tvf1(<params>)) as tvf1
  ON tvf1.id = tableA.X1
INNER JOIN (select SpecificColumns from tvf2(<params>)) as tvf2
  ON tvf2.id = tableA.X2

Alternatively, you may want to reconsider the Do-Everything query approach to reporting. At ~8kb per row, you're likely passing a tremendous amount of data to your reporting system.

You may also give Cross Apply a try.

SELECT <about 183 columns>
FROM tableA
CROSS APPLY tvf1(<params>) tvf1
CROSS APPLY tvf2(<params>) tvf2
WHERE tvf1.id = tableA.X1
  AND tvf2.id = tableA.X2

CROSS APPLY can instruct the optimizer to process sets in a different order.

user_71271820

Ive stil been unable to track down the exact problem.

My workaround for now has been to evaluate the table-valued-functions into table variables/temp tables and then join onto them instead

So it has been changed to something like this

DECLARE @tvf1 AS TABLE ....
INSERT INTO @tvf1 SELECT * FROM tvf1()...
DECLARE @tvf2 AS TABLE ....
INSERT INTO @tvf2 SELECT * FROM tvf2()...
DECLARE @tvf3 AS TABLE ....
INSERT INTO @tvf3 SELECT * FROM tvf3()...


SELECT <about 183 columns>
FROM tableA
INNER JOIN @tvf1 tvf1
  ON tvf1.id = tableA.X1
INNER JOIN @tvf2 tvf2
  ON tvf2.id = tableA.X2
INNER JOIN @tvf3 tvf3
  ON tvf3.id = tableA.X3
user_34375670
Related