Post reply

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, consid

  • May 12, 2008 at 8:27 am

    #187461

    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.

  • May 12, 2008 at 8:46 am

    #813858

    Where is the query and table definitions?

    N 56°04'39.16"
    E 12°55'05.25"

  • May 12, 2008 at 1:45 pm

    #814061

    Without the table and proc, I'm kind of guessing here. I've seen this kind of problem before with views (or just plain selects) that are very, very wide, and which have large Group By clauses, or complex Order By clauses.

    If your query has a ton of columns in it, and/or lots of wide data types (varchar(8000) for example), this can make it too wide for a worktable. In some cases, this is caused by "select *", and can be fixed by limiting the query to the columns that are actually needed.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • May 21, 2008 at 1:15 pm

    #818819

    I have the same error, any Ideas around the column limitation

    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.

  • May 21, 2008 at 1:28 pm

    #818833

    If you can post the query and the definitions of the tables involved, we may be able to help.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 23, 2014 at 5:20 am

    #1724014

    I have a report (SP) on global views that union 10 companies.

    Now I took the monthly backups of these companies and try the same on the last 6 month.

    So each view has about 60 'UNION ALL' from the different databases.

    And get the above error.

    I rebuilt the views for two month, the error is gone.

    Now I set the views back to 6 month, and split the major query in the SP, running seperatly each select. No error.

    It seems it doesn't matter where you split the query, just let it produce two execution plans.

    For me it was simple to do two Insert into #TmpTable.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply