re: TechEd 2011 - Performance Tuning and Optimization in SQL Server 2008 R2 and SQL Server Code Named "Denali"

Gustavo Ayala — Mon, 15 Aug 2011 13:56:19 GMT

I liked your top(@i) trick to lower the memory grant.Overall it's a clean solution.

I'm looking for the opposite thing.

Fooling the optimizer into taking a greater memory grant (so as to no spill to tempdb).

Unfortunately (well maybe not) the query optimizer is smart enough to know that top with a greater value will not "invent rows" out of thin air if the stream below sort (or top actually) doesn't produce them.

So top won't raise the memory grant. I've tried with union all with something with the same schema and adding varchar columns.

All ugly solutions (like if there was such thing as a clean solution for this stuff fooling the query optimizer...)

Do you know a not-so-ugly one ?

Thanks.

re: TechEd 2011 - Performance Tuning and Optimization in SQL Server 2008 R2 and SQL Server Code Named "Denali"

Adam Machanic — Mon, 15 Aug 2011 14:17:26 GMT

Hi Gustavo,

You're on the right track already, and you're correct that there are not many "not-so-ugly" solutions. Another option is to try OPTIMIZE FOR on something other than the TOP to mess with the seek predicates.

I'll be covering all of this in gruesome detail in my session at the PASS conference this year, if you'll be attending.

Best,

Adam