Denali: Improved T-SQL Query Optimization

re: Denali: Improved T-SQL Query Optimization

Dave Ballantyne — Sun, 11 Sep 2011 07:48:48 GMT

Hi Greg,

I inherently distrust the Query Cost relative to batch to be anywhere near accurate, especially where scalar functions (user defined or not) are involved.

What is the estimated cost of the LAG function within the plan ? , I would bet that it is a lot less than its real cost.

Do the io counters in profiler show the same ratio of costs ?

re: Denali: Improved T-SQL Query Optimization

John Alan — Mon, 12 Sep 2011 06:03:46 GMT

I agree, only back-to-back profiler figures are reliable enough in my experience

re: Denali: Improved T-SQL Query Optimization

Greg Low — Mon, 12 Sep 2011 07:43:33 GMT

Hi Dave/John,

I totally agree but in the case of these window functions, we've seen a real difference in the plans generated and they are much, much cleaner in how they work.

The real sweet spot seems to be any sort of running totals, which are otherwise an exponentially bad problem as the number of rows increases. And with the window functions, they just perform consistently.

Regards,

Greg

re: Denali: Improved T-SQL Query Optimization

noeld — Mon, 12 Sep 2011 18:29:44 GMT

Posting "elapsed times" would be much better(trustworthy) way to compare.