THE SQL Server Blog Spot on the Web
This blog has moved! You can find this content at the following new location:
http://greglow.com/index.php/2011/09/12/denali-improved-t-sql-query-optimization/
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using RSS
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 ?
I agree, only back-to-back profiler figures are reliable enough in my experience
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
Posting "elapsed times" would be much better(trustworthy) way to compare.