What should I look at to begin understanding the sudden performance degradation of this query?

swasheck 2013-09-11 19:24:05

A few weeks ago, this query's performance suddenly dropped. Where it was executing in 20 minutes, it's now averaging ~60 minutes. There has been no appreciable increase in data volume. Additionally, it completes within the 20-minute timeframe on the weekends. Because of this, I suspect that we are coming into conflict with another poor query that is not executing on the weeked. However, the developer is reluctant to fully rewrite. Are there any improvements that I could suggest in the context of the query here?

Config:
SQL Server 2008 R2
MAXDOP value_in_use = 6, is_dynamic = 1

EDIT:

I've added the weekend plan.link text

Aaron Bertrand 2013-09-12 15:06:17
Estimated rows change drastically between the weekday (e.g. 304K for the SELECT under the IF) and weekend (1,276 in the same place on the weekend). Unfortunately I fear that the bad cardinality may be at least in part caused by the use of the UDF (fn_GetAllAsOfDates). Is that a multi-statement or inline table UDF? Is it created with schemabinding? Can it be optimized or removed from the logic? What happens if you update the stats on PERFORMANCE_TYPE? How about fn_CalculateReturn, is this something that can be inlined or, perhaps, calculated after the #temp table is populated?
Hans 2013-09-11 19:58:03
Probably parameter sniffing. Try updateing statistics on every table involved in the query. If you have planned downtime, try the query after a reboot of the sql server. You could also use DBCC freeprocache command.
http://www.bing.com/search?q=dbcc+freeproccache&FORM=IE8SRC. Drops every query plan.
So your next execution will be stored in the plan cache.