Query Execution fails

Question

Query Execution fails

sdevarajan

SSC Veteran

Points: 255
More actions
November 23, 2010 at 11:39 pm

#147731

Hi,
I am facing an issue while executing a bigger query. The following is the error that am getting.
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.
I am generating the query dynamic and there are possibilities for a bigger query with group by and order by. I am getting the above error when i execute the same. Can anyone help me in resolving this please.
Attached the query which fails.
Thanks,
Sangeetha

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Ummm.... there's not much we can do for ya there. I'm not entirely sure the query definition fits in 8k bytes.

This is a hard limit of the worktables in SQL 2k5, because of the memory page restrictions. If this was a hard query we could help you work around the problems, but the dynamic portion of this makes it nearly impossible to give you a multi-step workaround. Sorry.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Adiga One Orange Chip Points: 27284 More actions · Answer 2

@sangeetha,

You may find these posts useful.

http://blogs.msdn.com/b/craigfr/archive/2009/06/24/maximum-row-size-and-query-hints.aspx

http://www.sqlservercentral.com/Forums/Topic498820-149-1.aspx

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

sdevarajan SSC Veteran Points: 255 More actions · Answer 3

Hi Adiga/Craig,

Thanks for the response.

@Adiga - The blogs gives the solution to reduce the number of columns but i need to have all the columns since it is being generated dynamic.

@Adiga / Craig - Is there a way to split the query by estimating the size of the query before execution so that i can execute it one after another.

Thanks for your help!

Regards,

Sangeetha

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 4

It's not the size of the query or the plan that's the problem, it's the work table. Have you looked at the execution plan to determine why you're hitting a work table and see if you can eliminate the work table, not the columns.

BTW, if you're trying to group by so many columns that you're exceeding the page size, you're still looking at other issues.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning