Query takes 43 seconds. Help speed up?
I've gone as far as I can with my sql tuning knowledge. Can anyone point out any obvious problems?
You should probably make a temp table result out of the top two queries of the CTE then the delta cte can join those two temp tables. That will help the estimates significantly and produce a better plan – at least that is the idea.
- Add OPTION (RECOMPILE) to the statement. This will enable the parameter embedding optimization (see the link below for details) which may help performance by eliminating residual predicates, simplifying the query, and perhaps making better use of indexes.
- Run the query again, with the hint above, from Plan Explorer. This will collect useful extra performance information, including duration and table I/O. If you are using Pro, it will collect extended events wait information too.
- Consider using an indexed view to pre-compute the aggregate.
- Try reducing DOP. Your original query ran at DOP 48, but the split version used DOP 28, which is quite an unusual number. It may be that SQL Server is dynamically reducing DOP at runtime due to a shortage of worker threads (too much parallelism!)